1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

create table abc_test(
    a string,
    b int,
    c int
) 
row format delimited fields terminated by '\t'
stored as textfile;

insert into abc_test values
('A',10,100),
('A',20,200),
('A',30,300),
('A',40,400),
('B',15,150),
('A',25,250),
('A',35,350)

select * from abc_test;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as min_b,c 
from
(
    select a,b,c,row_number() over(partition by a order by b asc) as rn 
    from abc_test
)as tmp
where rn = 1;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as maz_b,c 
from
(
    select a,b,c,row_number() over(partition by a order by b desc) as rn 
    from abc_test
)as tmp
where rn = 1;

1
2
3

insert into abc_test values
('A',10,999),
('A',20,999);

1
2
3
4
5
6
7
8

select 
    a as group_a,b as maz_b,c 
from
(
    select a,b,c,rank() over(partition by a order by b asc) as rn 
    from abc_test
)as tmp
where rn = 1;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as maz_b,c 
from
(
    select a,b,c,dense_rank() over(partition by a order by b asc) as rn 
    from abc_test
)as tmp
where rn = 2;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as maz_b,c 
from
(
    select a,b,c,dense_rank() over(partition by a order by b desc) as rn 
    from abc_test
)as tmp
where rn = 2;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as min_b,c 
from
(
    select a,b,c,row_number() over(partition by a order by b asc) as rn 
    from abc_test
)as tmp
where rn <= 2;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as min_b,c 
from
(
    select a,b,c,row_number() over(partition by a order by b desc) as rn 
    from abc_test
)as tmp
where rn <= 2;

1
2
3
4
5
6
7
8

select 
    a as group_a,b as min_b,c 
from
(
    select a,b,c,dense_rank() over(partition by a order by b asc) as rn 
    from abc_test
)as tmp
where rn <= 2;

1
2
3
4
5
6

insert overwrite table abc_test
select *
from interview.abc_test
where c != 999;

select * FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT 
    a,
    b,
    c,
    -- 窗口函数正确语法：聚合函数(字段) OVER(窗口定义)
    SUM(c) OVER(
        PARTITION BY a 
        ORDER BY b ASC   -- 必须指定排序字段，否则累计和无意义！
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_c
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT 
    a,
    b,
    c,
    -- 窗口函数正确语法：聚合函数(字段) OVER(窗口定义)
    avg(c) OVER(
        PARTITION BY a 
        ORDER BY b ASC   -- 必须指定排序字段，否则累计和无意义！
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_c
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT 
    a,
    b,
    c,
    -- 以a分组，按b升序，生成行号
    row_number() OVER(
        PARTITION BY a 
        ORDER BY b ASC
    ) AS rn,
    -- a分组的元素数
    count(*) OVER ( 
        PARTITION BY a 
    ) AS total_cnt, 
    -- 计算排名比例rank_ratio=排名/总元素数
    -- 在 Hive 中整数除法可能导致结果不符合预期，建议转成 double：
    cast(row_number() OVER ( 
        PARTITION BY a 
        ORDER BY b ASC 
    )AS double) 
    /
    count(*) OVER ( 
        PARTITION BY a 
    ) AS rank_ratio
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 正确写法：percent_rank() 不需要ROWS BETWEEN子句
SELECT 
    a,
    b,
    c,
    percent_rank() OVER(
        PARTITION BY a 
        ORDER BY b ASC
    ) AS percent_rank_value
FROM abc_test;

1
2
3
4
5
6
7
8
9

SELECT 
    a,
    b,
    c,
    cume_dist() OVER(
        PARTITION BY a 
        ORDER BY b ASC
    ) AS cume_dist_value
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT 
    a,
    b,
    c,
    -- 窗口函数正确语法：聚合函数(字段) OVER(窗口定义)
    sum(c) OVER(
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS current_sum_value,
    sum(c) OVER ( 
        PARTITION BY a 
    ) AS total_sum_value, 
    cast(sum(c) OVER(
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )AS double)
    /
    sum(c) OVER ( 
        PARTITION BY a 
    ) as sum_percent
FROM abc_test;ercent
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT 
    a,
    b,
    c,
    -- 前一行的值
    lag(c,1,0) OVER( 
        PARTITION BY a 
        ORDER BY b asc
    ) AS pre_value,
    -- 后一行的值
    lead(c,1,0) OVER(
        PARTITION BY a 
        ORDER BY b asc
    ) AS after_value,
    -- 前一行+后一行的值
    lag(c,1,0) OVER(
        PARTITION BY a 
        ORDER BY b asc
    )+
    lead(c,1,0) OVER(
        PARTITION BY a 
        ORDER BY b asc
    ) as  sum_pre_after
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT 
    a,
    b,
    c,
    -- 窗口函数正确语法：聚合函数(字段) OVER(窗口定义)
    sum(c) OVER(
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN 1 PRECEDING AND 1 following
    ) AS current_sum_value
FROM abc_test;

1
2
3
4
5
6
7
8

SELECT 
    a, 
    b, 
    c, 
    AVG(c) OVER ( 
        PARTITION BY a 
    ) AS avg_c_in
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    a, 
    b, 
    c, 
    AVG(c) OVER ( 
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS avg_pre_curr
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    a, 
    b, 
    c, 
    AVG(c) OVER ( 
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN 1 PRECEDING AND 1 following
    ) AS avg_pre_curr_follow
FROM abc_test;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    a, 
    b, 
    c, 
    AVG(c) OVER ( 
        PARTITION BY a 
        ORDER BY b asc
        ROWS BETWEEN 1 PRECEDING AND current row
    ) AS avg_pre_curr
FROM abc_test;

1

PARTITION BY a

1
2
3

ORDER BY b ASC
- 或：
ORDER BY b DESC

1
2
3

row_number()
rank() 
dense_rank()

1
2
3
4
5

SUM(c) OVER ( 
PARTITION BY a 
ORDER BY b 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
)

1
2

lag() 
lead()

1
2
3
4
5
6
7

SUM(c) OVER ( 
    PARTITION BY a 
    ORDER BY b 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
) 
/ 
SUM(c) OVER (PARTITION BY a)