0


Hive 窗口函数大全

窗口函数概述

over窗口函数说明:

function(arg) over (partition by {partition columns} order by {order columns} desc/asc)

partition columns:当前行中根据指定的列对partition columns列相同值归到一个分区中;

order columns:在相同值的partition columns列分区中,按照order columns列值进行排序,可以指定升序或是降序,默认是升序

function(arg):对应的窗口数据计算函数

窗口序列函数

row_number

在窗口内会对所有数值,输出不同的序号,序号唯一且连续,如:1、2、3、4、5。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。

示例:

SELECT 
ROW_NUMBER() OVER (PARTITION BY province ) AS row_number 
,user_id 
,province 
FROM tmp_cube

结果

row_number    user_id    province
1    137    云南省
2    139    云南省
3    138    云南省
4    136    云南省
5    135    云南省
6    140    云南省
1    133    北京
2    132    北京
3    134    北京
1    124    广东省
2    127    广东省
3    151    广东省
4    123    广东省
5    225    广东省
6    126    广东省

会对相同数值,输出相同的序号,而且下一个序号间断,如:1、1、3、3、5。

示例

SELECT rank() OVER (PARTITION BY province order by part ) AS rank
        ,province
        ,part
FROM    tmp_cube

结果

rank    province    part
1    云南省    01
1    云南省    01
3    云南省    02
3    云南省    02
5    云南省    03
5    云南省    03
1    北京    01
2    北京    02
3    北京    03
1    广东省    01
1    广东省    01
3    广东省    02
3    广东省    02
5    广东省    03
5    广东省    03

dense_rank

会对相同数值,输出相同的序号,但下一个序号不间断,如:1、1、2、2、3。

示例:

SELECT 
dense_rank() OVER (PARTITION BY province order by part ) AS dense_rank 
,province 
,part 
FROM tmp_cube ;

结果

dense_rank    province    part
1    云南省    01
1    云南省    01
2    云南省    02
2    云南省    02
3    云南省    03
3    云南省    03
1    北京    01
2    北京    02
3    北京    03
1    广东省    01
1    广东省    01
2    广东省    02
2    广东省    02
3    广东省    03
3    广东省    03

窗口边界

控制窗口范围,必须配合over窗口的order by排序

参数解释:

n行数

unbounded 不限行数(修饰preceding和following) preceding 在前N行 following 在后N行 current row 当前行

举例说明 :

-- 窗口中整个的范围(over 窗口函数默认是整个窗口范围)

rows between unbounded preceding and unbounded following

-- 从 前无限行 到 当前行

rows between unbounded preceding and current row

-- 从 当前行的前2行 到 当前行

rows between 2 preceding and current row

-- 从 当前行 到 当前行后2行

rows between current row and 2 following

-- 当前行 到 后不限行 rows between current row and unbounded following

滑动窗口

lag 获取上一行数据

LAG(col,n):配合over使用,取窗口范围往前第 n 行数据的值

lead 获取下一行数据

LEAD(col,n):配合over使用,取窗口范围往后第 n 行数据的值

窗口专用计算函数

sum累加函数

实现效果:按照yyyymm统计截至到当前行的sum(num)值;

sum(num) over(partition by user_id,yyyy order by yyyymm asc )
SELECT user_id ,yyyymm ,integral ,sum(integral) over (partition by user_id order by yyyymm) as sum FROM user_totaluser_total ;

结果

user_id    yyyymm    integral    sum
195    202206    20060.0    20060.0
195    202207    23028.0    43088.0
195    202208    20150.0    63238.0
195    202209    20170.0    83408.0
195    202210    20284.0    103692.0
195    202211    20150.0    123842.0
195    202212    20944.0    144786.0
195    202301    \N    144786.0
400    202206    0.0    0.0
400    202207    20384.0    20384.0
400    202208    20150.0    40534.0
400    202209    0.0    40534.0
400    202210    20150.0    60684.0
400    202211    0.0    60684.0
400    202212    0.0    60684.0
400    202301    \N    60684.0
405    202206    0.0    0.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    52502.0
405    202210    25916.0    78418.0
405    202211    0.0    78418.0
405    202212    0.0    78418.0

实现效果:每一个都是的sum(num)值;

sum(num) over(partition by user_id,yyyy )
SELECT 
user_id 
,yyyymm 
,integral 
,sum(integral) over (partition by user_id) as sum 
FROM user_totaluser_total ;

结果

user_id    yyyymm    integral    sum
195    202301    \N    144786.0
195    202206    20060.0    144786.0
195    202207    23028.0    144786.0
195    202208    20150.0    144786.0
195    202209    20170.0    144786.0
195    202210    20284.0    144786.0
195    202211    20150.0    144786.0
195    202212    20944.0    144786.0
400    202301    \N    60684.0
400    202206    0.0    60684.0
400    202207    20384.0    60684.0
400    202208    20150.0    60684.0
400    202209    0.0    60684.0
400    202210    20150.0    60684.0
400    202211    0.0    60684.0
400    202212    0.0    60684.0
405    202207    38852.0    78418.0
405    202206    0.0    78418.0
405    202209    13650.0    78418.0
405    202208    0.0    78418.0
405    202210    25916.0    78418.0
405    202211    0.0    78418.0
405    202212    0.0    78418.0

max最大值

min(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,max(integral) over (partition by user_id)
 FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    23028.0
195    202208    20150.0    23028.0
195    202209    20170.0    23028.0
195    202206    20060.0    23028.0
195    202210    20284.0    23028.0
195    202211    20150.0    23028.0
195    202212    20944.0    23028.0
195    202301    \N    23028.0
400    202209    0.0    20384.0
400    202206    0.0    20384.0
400    202207    20384.0    20384.0
400    202208    20150.0    20384.0
400    202210    20150.0    20384.0
400    202211    0.0    20384.0
400    202212    0.0    20384.0
400    202301    \N    20384.0
405    202206    0.0    38852.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    38852.0
405    202210    25916.0    38852.0
405    202211    0.0    38852.0
405    202212    0.0    38852.0

min最小值

min(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,min(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    23028.0
195    202208    20150.0    23028.0
195    202209    20170.0    23028.0
195    202206    20060.0    23028.0
195    202210    20284.0    23028.0
195    202211    20150.0    23028.0
195    202212    20944.0    23028.0
195    202301    \N    23028.0
400    202209    0.0    20384.0
400    202206    0.0    20384.0
400    202207    20384.0    20384.0
400    202208    20150.0    20384.0
400    202210    20150.0    20384.0
400    202211    0.0    20384.0
400    202212    0.0    20384.0
400    202301    \N    20384.0
405    202206    0.0    38852.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    38852.0
405    202210    25916.0    38852.0
405    202211    0.0    38852.0
405    202212    0.0    38852.0

avg平均值

avg(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,avg(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    20683.714285714286
195    202208    20150.0    20683.714285714286
195    202209    20170.0    20683.714285714286
195    202206    20060.0    20683.714285714286
195    202210    20284.0    20683.714285714286
195    202211    20150.0    20683.714285714286
195    202212    20944.0    20683.714285714286
195    202301    \N    20683.714285714286
400    202209    0.0    8669.142857142857
400    202206    0.0    8669.142857142857
400    202207    20384.0    8669.142857142857
400    202208    20150.0    8669.142857142857
400    202210    20150.0    8669.142857142857
400    202211    0.0    8669.142857142857
400    202212    0.0    8669.142857142857
400    202301    \N    8669.142857142857
405    202206    0.0    11202.57142857143
405    202207    38852.0    11202.57142857143
405    202208    0.0    11202.57142857143
405    202209    13650.0    11202.57142857143
405    202210    25916.0    11202.57142857143
405    202211    0.0    11202.57142857143
405    202212    0.0    11202.57142857143

count累计次数

count(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,count(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    7
195    202208    20150.0    7
195    202209    20170.0    7
195    202206    20060.0    7
195    202210    20284.0    7
195    202211    20150.0    7
195    202212    20944.0    7
195    202301    \N    7
400    202209    0.0    7
400    202206    0.0    7
400    202207    20384.0    7
400    202208    20150.0    7
400    202210    20150.0    7
400    202211    0.0    7
400    202212    0.0    7
400    202301    \N    7
405    202206    0.0    7
405    202207    38852.0    7
405    202208    0.0    7
405    202209    13650.0    7
405    202210    25916.0    7
405    202211    0.0    7
405    202212    0.0    7

first_value首行值

first_value(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,first_value(yyyymm) over (partition by user_id order by yyyymm) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202206    20060.0    202206
195    202207    23028.0    202206
195    202208    20150.0    202206
195    202209    20170.0    202206
195    202210    20284.0    202206
195    202211    20150.0    202206
195    202212    20944.0    202206
195    202301    \N    202206
400    202206    0.0    202206
400    202207    20384.0    202206
400    202208    20150.0    202206
400    202209    0.0    202206
400    202210    20150.0    202206
400    202211    0.0    202206
400    202212    0.0    202206
400    202301    \N    202206
405    202206    0.0    202206
405    202207    38852.0    202206
405    202208    0.0    202206
405    202209    13650.0    202206
405    202210    25916.0    202206
405    202211    0.0    202206
405    202212    0.0    202206

last_value末行值

last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause])

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,last_value(yyyymm) over (partition by user_id order by yyyymm) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202206    20060.0    202206
195    202207    23028.0    202207
195    202208    20150.0    202208
195    202209    20170.0    202209
195    202210    20284.0    202210
195    202211    20150.0    202211
195    202212    20944.0    202212
195    202301    \N    202301
400    202206    0.0    202206
400    202207    20384.0    202207
400    202208    20150.0    202208
400    202209    0.0    202209
400    202210    20150.0    202210
400    202211    0.0    202211
400    202212    0.0    202212
400    202301    \N    202301
405    202206    0.0    202206
405    202207    38852.0    202207
405    202208    0.0    202208
405    202209    13650.0    202209
405    202210    25916.0    202210
405    202211    0.0    202211
405    202212    0.0    202212

cume_dist分布统计

如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number ofrows)。

如果是降序排列,则统计:大于等于当前值的行数/总行数

示例:

统计小于等于当前工资的人数占总人数的比例

SELECT 
name
, dept_no
, salary
, cume_dist() OVER (ORDER BY salary) as cume_dist 
FROM data;

结果:

+-------+-------+------+---------+
|name   |dept_no|salary|cume_dist|
+-------+-------+------+---------+
|rose   |2      |4000  |0.125    |
|jack   |2      |5000  |0.375    |
|steven |3      |5000  |0.375    |
|john   |1      |6000  |0.5      |
|jerry  |2      |6600  |0.625    |
|tom    |1      |8000  |0.75     |
|richard|3      |9000  |0.875    |
|mike   |1      |10000 |1.0      |
+-------+-------+------+---------+

根据部门统计小于等于当前工资的人数占部门总人数的比例:

SELECT 
name
, dept_no
, salary
, cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_dist 
FROM data;

percent_rank 秩分析函数

返回order by列的百分比排名;

计算逻辑:(RANK-1)/(N-1)

即:(rank - 1) / (the number of rows in the window or partition - 1)

select row,value,rank() over() ,PERCENT_RANK() over(partition by 1 order by value) from tablename;

结果:

Row#    Value    Rank    Calculation    PERCENT_RANK
1    15    1    (1-1)/(7-1)    0.0000
2    20    2    (2-1)/(7-1)    0.1666
3    20    2    (2-1)/(7-1)    0.1666
4    20    2    (2-1)/(7-1)    0.1666
5    30    5    (5-1)/(7-1)    0.6666
6    30    5    (5-1)/(7-1)    0.6666
7    40    7    (7-1)/(7-1)    1.0000

nitle数据切片函数

nitle(n),n指将分组内的数据按照order列进行排序切分成n个区,排名序号依次排名为1,2,3,4,5.....,并返回数据切片排名序号;

如,各地区销售额排名:

select 
province
,yyyymm
,gvm
,ntile(5) over(partition by province order by gvm desc) 
from total

结果:

province    yyyymm    gvm    _c3
上海    202210    3416560    1
上海    202206    3050450    1
上海    202207    2974400    2
上海    202209    2611310    2
上海    202208    2353780    3
上海    202205    2002650    3
上海    202204    1556750    4
上海    202211    1510340    5
云南省    202207    3819660    1
云南省    202204    3605550    1
云南省    202210    3493000    2
云南省    202206    3432000    2
云南省    202205    3272100    3
云南省    202209    3123720    3
云南省    202208    3089060    4
云南省    202211    1853150    5

后续可以根据ntile(5)的结果挑选第几切片的数据出来;通常结合n值,用于筛选前20%、10%等数据;

根据上面示例,求各地销售额前20%的数据:

(求前20%,即将数据切分城5份,取第一份数据即可)

select * from 
( select province,yyyymm,gvm,ntile(5) as ntile over(partition by province order by gvm desc) from total ) 
where ntile=1
标签: hive 大数据 hadoop

本文转载自: https://blog.csdn.net/suly_liping/article/details/128535403
版权归原作者 罗生尘 所有, 如有侵权,请联系我们删除。

“Hive 窗口函数大全”的评论:

还没有评论