1.綜述
我們討論面試中各大廠的SQL演算法面試題，往往核心考點就在於視窗函式，所以掌握好了視窗函式，面對SQL演算法面試往往事半功倍。
已更新第一類聚合函式類，點選這裡閱讀 hive視窗函式聚合函式類
本節介紹Hive聚合函式中的第二類聚合函式：分組排序視窗函式。
這些函式的用法不僅僅適用於Hive，對於很多數資料庫來說同樣也適用，比如SparkSQL，FlinkSQL以及Mysql8，Oracle，MSSQL等傳統的關係型資料庫。
如果論使用的廣泛性，hive視窗函式中我們使用最廣泛的就是排序類視窗函式，我們通常一提起視窗函式，想到的就是這類排序類視窗函式，它在我們進行資料去重中扮演了至關重要的角色。
1.1 Hive視窗函式分類
Hive提供的視窗函式可以分為一下幾類
- 聚合函式類
count() over();
sum() over();
max() over();
min() over();
avg() over();
- 分組排序類
row_number() over();
rank() over();
dense_rank() over();
percent_rank() over();
cume_dist() over();
ntile() over();
- 求偏移量類
lead() over();
lag() over();
first_value() over();
1.2 分析函式語法
分析函式 over(partition by 列名 order by 列名 rows between 開始位置 and 結束位置)
具體解析
over()括號內為空時，是直接進行計算。
其中partition by 列名 是按指定列進行分組，進而進行計算。
最後的order by 列名 是按照指定列進行排序，進而進行計算。
1.3 基礎資料準備
create table if not exists temp.user_info (
`id` bigint comment '使用者id',
`client` string comment '客戶端',
`gender` int comment '性別，0女1男',
`constellation` string comment '星座',
`age` int comment '年齡',
`pv` bigint comment '訪問量',
`chat_num` bigint comment '聊天次數'
) comment '使用者資訊測試臨時表'
資料預覽
| id | client | gender | constellation | age | pv | chat_num |
|---|---|---|---|---|---|---|
| 1 | ios | 0 | 處女座 | 29 | 174 | 3 |
| 2 | ios | 1 | 雙魚座 | 26 | 263 | 2 |
| 3 | android | 1 | 雙魚座 | 35 | 232 | 39 |
| 4 | ios | 1 | 水瓶座 | 32 | 57 | 3 |
| 5 | ios | 1 | 射手座 | 33 | 67 | 6 |
| 6 | ios | 1 | 雙子座 | 36 | 81 | 5 |
| 7 | ios | 1 | 獅子座 | 29 | 68 | 4 |
| 8 | ios | 1 | 獅子座 | 28 | 19 | 3 |
| 9 | ios | 0 | 射手座 | 32 | 479 | 2 |
| 10 | ios | 1 | 白羊座 | 26 | 255 | 36 |
2.各分組排序類函式的使用
2.1 row_number
- 功能
按照排序的順序輸出視窗中的資料的行號資訊，不會出現排名相同的情況，且排名是連續的。即使是值相同，排名也會按照其排序順序遞增。
- 示例
按照星座分組，統計出pv由高到低的排名。
select id,client,gender,row_number() over(partition by constellation order by pv desc) as rank_id
from temp.user_info where id <= 10
資料結果：
| id | client | gender | constellation | pv | rank_id |
|---|---|---|---|---|---|
| 6 | ios | 1 | 雙子座 | 81 | 1 |
| 2 | ios | 1 | 雙魚座 | 263 | 1 |
| 3 | android | 1 | 雙魚座 | 232 | 2 |
| 1 | ios | 0 | 處女座 | 174 | 1 |
| 9 | ios | 0 | 射手座 | 479 | 1 |
| 5 | ios | 1 | 射手座 | 67 | 2 |
| 4 | ios | 1 | 水瓶座 | 57 | 1 |
| 7 | ios | 1 | 獅子座 | 68 | 1 |
| 8 | ios | 1 | 獅子座 | 19 | 2 |
| 10 | ios | 1 | 白羊座 | 255 | 1 |
可以很清晰的看到按星座分組，每個星座內部的PV排名的id。例如，射手座使用者id9排名第一，使用者id5排名第二。
- 擴充使用：
-
更深一步的需求是篩出每個星座最高pv的使用者，（或者說按星座去重，只取最高訪問pv的使用者）
-
再更進一步還可以計算諸如最大線上天數等SQL問題。
-
業務中常用用法為透過指定主鍵進行資料去重。
2.2 rank
- 功能
按照指定列進行排名，如果值相同，則排名並列，下一個排名會出現跳躍，即排名是不連續的。例如有前2個值一樣，那麼前2行並列第一，第3行的排名則為3。
- 示例
按使用客戶端分組，統計年齡由高到低的排名。
select id,client,age,rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10
資料結果：
| id | client | age | rank_id |
|---|---|---|---|
| 3 | android | 35 | 1 |
| 6 | ios | 36 | 1 |
| 5 | ios | 33 | 2 |
| 9 | ios | 32 | 3 |
| 4 | ios | 32 | 3 |
| 1 | ios | 29 | 5 |
| 7 | ios | 29 | 5 |
| 8 | ios | 28 | 7 |
| 2 | ios | 26 | 8 |
| 10 | ios | 26 | 8 |
可以看到上述資訊中，透過安卓和iOS客戶端的年齡大小排名。其中32歲兩個並列第三，沒有第四名，直接開始第五名。相同值進行並列。rank_id的排名是不連續的。
- 擴充使用：
常用於指定排名場景。
2.3 dense_rank
- 功能
該函式可以和rank()對照使用，按照指定列排序的順序輸出視窗中的資料的排名，如果值相同時，排名並列，下一個排名是連續遞增的，不會出現跳躍情況。即如果前2行的值相同，則前2行並列第1，第3行的排名則為第2。
可以理解為一個為疏鬆排名（rank）,一個為密集排名（dense_rank）
- 示例
按使用客戶端分組，統計年齡由高到低的排名。
select id,client,age,dense_rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10
資料結果：對比上文
| id | client | age | rank_id |
|---|---|---|---|
| 3 | android | 35 | 1 |
| 6 | ios | 36 | 1 |
| 5 | ios | 33 | 2 |
| 9 | ios | 32 | 3 |
| 4 | ios | 32 | 3 |
| 1 | ios | 29 | 4 |
| 7 | ios | 29 | 4 |
| 8 | ios | 28 | 5 |
| 2 | ios | 26 | 6 |
| 10 | ios | 26 | 6 |
可以看到，排名沒有出現跳躍，是連續的，相同排名並列其名次。例如ios的32歲兩個並列第三老。
- 擴充使用
和rank相反，我希望出現不中斷的排名，這樣的使用場景。
2.4 percent_rank
- 功能
見名知意，按百分比進行排名。
與 percent 函式類似，percent_rank （分佈函式）函式的視窗 order by 子句所指定列中的值的返回值，是介於 0 和 1 之間的小數形式表示。
- 計算方法
(rank - 1) / (n - 1)
rank為上述rank()函式的排名，n 為當前視窗的總數。
- 示例
按使用客戶端分組，統計年齡由高到低的百分比排名（percent_rank）。
另一種問法:某某使用者的年齡（或者其他指標）在ios客戶端排名前百分之多少?
select id,client,age,percent_rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10
資料結果：
| id | client | age | rank_id |
|---|---|---|---|
| 3 | android | 35 | 0.0 |
| 6 | ios | 36 | 0.0 |
| 5 | ios | 33 | 0.125 |
| 9 | ios | 32 | 0.25 |
| 4 | ios | 32 | 0.25 |
| 1 | ios | 29 | 0.5 |
| 7 | ios | 29 | 0.5 |
| 8 | ios | 28 | 0.75 |
| 2 | ios | 26 | 0.875 |
| 10 | ios | 26 | 0.875 |
可以看出，使用者9和4的年齡32歲排名ios客戶端前百分之二十五，其並列排名。
- 擴充使用
該函式經常用於較大資料量的百分比佔比分析，也常用於探究資料分佈分析場景。
例如可以透過分佈函式踢除極值，進而求解均值，降低資料誤差。
2.5 cume_dist
- 功能
如果按升序排列，則統計：小於等於當前值的行數所佔當前分割槽視窗總行數的比例。(number of rows ≤ current row)/(total number of rows）。
如果是降序排列，則統計：大於等於當前值的行數所佔當前分割槽視窗總行數的比例。
- 示例
1.統計小於等於當前年齡的人數佔總人數的比例。
另一種問法：小於等於29歲的人佔總人數的比例。
select id,client,age,cume_dist() over(order by age desc) as rank_id
from temp.user_info where id <= 10
order by age
資料結果：
| id | client | age | rank_id |
|---|---|---|---|
| 2 | ios | 26 | 0.2 |
| 10 | ios | 26 | 0.2 |
| 8 | ios | 28 | 0.3 |
| 1 | ios | 29 | 0.5 |
| 7 | ios | 29 | 0.5 |
| 4 | ios | 32 | 0.7 |
| 9 | ios | 32 | 0.7 |
| 5 | ios | 33 | 0.8 |
| 3 | android | 35 | 0.9 |
| 6 | ios | 36 | 1.0 |
可以看到小於等於29歲所佔人群的總比例為50%。
2.統計當前客戶端分割槽小於等於當前年齡的人數佔總人數的比例。
另一種問法：ios客戶端小於等於29歲的人佔總人數的比例。
select id,client,age,cume_dist() over(partition by client order by age) as rank_id
from temp.user_info where id <= 10
order by age
資料結果：
| id | client | age | rank_id |
|---|---|---|---|
| 2 | ios | 26 | 0.2222222222222222 |
| 10 | ios | 26 | 0.2222222222222222 |
| 8 | ios | 28 | 0.3333333333333333 |
| 1 | ios | 29 | 0.5555555555555556 |
| 7 | ios | 29 | 0.5555555555555556 |
| 4 | ios | 32 | 0.7777777777777778 |
| 9 | ios | 32 | 0.7777777777777778 |
| 5 | ios | 33 | 0.8888888888888888 |
| 3 | android | 35 | 1.0 |
| 6 | ios | 36 | 1.0 |
可以看到ios客戶端小於等於29歲的人群佔比為55.6%左右。
- 擴充使用
該函式是一個累積求比例的函式，常用於求解排名前百分之多少或者排名後百分之多少的問題。
2.6 ntile
- 功能
分桶視窗函式，用於將按指定列分組的資料按照順序切分成N片，返回當前切片值。將一個有序的資料集劃分為多個桶(bucket)，併為每行分配一個適當的桶數（切片值，第幾個切片，第幾個分割槽等概念）。它可用於將資料劃分為相等的小切片，為每一行分配該小切片的數字序號。
- 注意
ntile不支援rows between，range between.
- 示例
統計按照客戶端分組，按年齡排序，將每個視窗分成3片（桶），返回每片（桶）的的分片（桶）資訊。
另一種問法：把ios客戶端的人群按年齡正序分成三部分，返回任意一部分的值。
select id,client,age,cume_dist() over(partition by client order by age) as rank_id
from temp.user_info where id <= 10
order by age
資料結果：
| id | client | age | rank_id |
|---|---|---|---|
| 3 | android | 35 | 1 |
| 10 | ios | 26 | 1 |
| 2 | ios | 26 | 1 |
| 8 | ios | 28 | 1 |
| 7 | ios | 29 | 2 |
| 1 | ios | 29 | 2 |
| 4 | ios | 32 | 2 |
| 9 | ios | 32 | 3 |
| 5 | ios | 33 | 3 |
| 6 | ios | 36 | 3 |
可以看到ios客戶端9人被分為三部分，需要哪一部分，只要再限制rank_id 等於幾就行。
- 擴充使用
該函式是一個分桶函式，可以按照指定的列把資料均勻的分成想要的幾部分資料。
例如，求解使用者活躍時長前百分之二十的群體，如果包含0活躍時長使用者，用百分比排序就不好計算了，而用該函式可以很快計算出來。
以上，為本次分享內容。
後續計劃會開啟一個新的系列內容：SQL每日一題系列，多來自各大廠的高頻面試題，有好的演算法面試題也可以積極分享，互相交流。
感謝閱讀。
下一期：hive視窗分析函式使用詳解之三-求偏移量類視窗函式
按例，歡迎點選此處關注我的個人公眾號，交流更多知識。