目錄
窗口函數(shù)概述
窗口序列函數(shù)
row_number
dense_rank
窗口邊界
滑動窗口
lag 獲取上一行數(shù)據(jù)
lead 獲取下一行數(shù)據(jù)
窗口專用計算函數(shù)
sum累加函數(shù)
max最大值
min最小值
avg平均值
count累計次數(shù)
first_value首行值
last_value末行值
cume_dist分布統(tǒng)計
percent_rank 秩分析函數(shù)
nitle數(shù)據(jù)切片函數(shù)
窗口函數(shù)概述
over窗口函數(shù)說明：
function(arg) over (partition by {partition columns} order by {order columns} desc/asc)partition columns：當前行中根據(jù)指定的列對partition columns列相同值歸到一個分區(qū)中；
order columns：在相同值的partition columns列分區(qū)中，按照order columns列值進行排序，可以指定升序或是降序，默認是升序
function(arg)：對應的窗口數(shù)據(jù)計算函數(shù)
窗口序列函數(shù)
row_number
在窗口內會對所有數(shù)值，輸出不同的序號，序號唯一且連續(xù)，如：1、2、3、4、5。
row_number() OVER (PARTITION BY COL1 ORDER BY COL2)表示根據(jù)COL1分組，在分組內部根據(jù) COL2排序，而此函數(shù)計算的值就表示每組內部排序后的順序編號（組內連續(xù)的唯一的)。
示例：
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 廣東省會對相同數(shù)值，輸出相同的序號，而且下一個序號間斷，如：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 廣東省 03dense_rank
會對相同數(shù)值，輸出相同的序號，但下一個序號不間斷，如：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排序
參數(shù)解釋：
n行數(shù)
unbounded 不限行數(shù)（修飾preceding和following） preceding 在前N行 following 在后N行 current row 當前行舉例說明 :
-- 窗口中整個的范圍（over 窗口函數(shù)默認是整個窗口范圍）
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 獲取上一行數(shù)據(jù)
LAG(col,n)：配合over使用，取窗口范圍往前第 n 行數(shù)據(jù)的值
lead 獲取下一行數(shù)據(jù)
LEAD(col,n)：配合over使用，取窗口范圍往后第 n 行數(shù)據(jù)的值
窗口專用計算函數(shù)
sum累加函數(shù)
實現(xiàn)效果：按照yyyymm統(tǒng)計截至到當前行的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實現(xiàn)效果：每一個都是的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.0max最大值
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.0min最小值
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.0avg平均值
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.57142857143count累計次數(shù)
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 7first_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 202206last_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 202212cume_dist分布統(tǒng)計
如果按升序排列，則統(tǒng)計：小于等于當前值的行數(shù)/總行數(shù)(number of rows ≤ current row)/(total number ofrows）。
如果是降序排列，則統(tǒng)計：大于等于當前值的行數(shù)/總行數(shù)
示例：
統(tǒng)計小于等于當前工資的人數(shù)占總人數(shù)的比例
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 |
+-------+-------+------+---------+根據(jù)部門統(tǒng)計小于等于當前工資的人數(shù)占部門總人數(shù)的比例:
SELECT
name
, dept_no
, salary
, cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_dist
FROM data;percent_rank 秩分析函數(shù)
返回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.0000nitle數(shù)據(jù)切片函數(shù)
nitle(n),n指將分組內的數(shù)據(jù)按照order列進行排序切分成n個區(qū)，排名序號依次排名為1，2，3，4，5.....，并返回數(shù)據(jù)切片排名序號；
如，各地區(qū)銷售額排名：
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后續(xù)可以根據(jù)ntile(5)的結果挑選第幾切片的數(shù)據(jù)出來；通常結合n值，用于篩選前20%、10%等數(shù)據(jù)；
根據(jù)上面示例，求各地銷售額前20%的數(shù)據(jù)：文章來源：http://www.zghlxwxcb.cn/news/detail-613412.html
（求前20%，即將數(shù)據(jù)切分城5份，取第一份數(shù)據(jù)即可）文章來源地址http://www.zghlxwxcb.cn/news/detail-613412.html
select * from
( select province,yyyymm,gvm,ntile(5) as ntile over(partition by province order by gvm desc) from total )
where ntile=1到了這里，關于Hive 窗口函數(shù)大全的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！
