窗口函数概述
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
本文转载自: https://blog.csdn.net/suly_liping/article/details/128535403
版权归原作者 罗生尘 所有， 如有侵权，请联系我们删除。
版权归原作者 罗生尘 所有， 如有侵权，请联系我们删除。