1.綜述

我們討論面試中各大廠的SQL算法面試題，往往核心考點就在于窗口函數(shù)，所以掌握好了窗口函數(shù)，面對SQL算法面試往往事半功倍。

已更新第一類聚合函數(shù)類，點擊這里閱讀 hive窗口函數(shù)聚合函數(shù)類

本節(jié)介紹Hive聚合函數(shù)中的第二類聚合函數(shù)：分組排序窗口函數(shù)。

這些函數(shù)的用法不僅僅適用于Hive，對于很多數(shù)數(shù)據(jù)庫來說同樣也適用，比如SparkSQL，F(xiàn)linkSQL以及Mysql8，Oracle，MSSQL等傳統(tǒng)的關(guān)系型數(shù)據(jù)庫。

如果論使用的廣泛性，hive窗口函數(shù)中我們使用最廣泛的就是排序類窗口函數(shù)，我們通常一提起窗口函數(shù)，想到的就是這類排序類窗口函數(shù)，它在我們進行數(shù)據(jù)去重中扮演了至關(guān)重要的角色。

1.1 Hive窗口函數(shù)分類

Hive提供的窗口函數(shù)可以分為一下幾類

• 聚合函數(shù)類

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 分析函數(shù)語法

分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

具體解析

over()括號內(nèi)為空時，是直接進行計算。

其中partition by 列名 是按指定列進行分組，進而進行計算。

最后的order by 列名 是按照指定列進行排序，進而進行計算。

1.3 基礎(chǔ)數(shù)據(jù)準備

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 '聊天次數(shù)'
) comment '用戶信息測試臨時表' 

數(shù)據(jù)預覽

2.各分組排序類函數(shù)的使用

2.1 row_number

• 功能

按照排序的順序輸出窗口中的數(shù)據(jù)的行號信息，不會出現(xiàn)排名相同的情況，且排名是連續(xù)的。即使是值相同，排名也會按照其排序順序遞增。

• 示例

按照星座分組，統(tǒng)計出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

數(shù)據(jù)結(jié)果：

可以很清晰的看到按星座分組，每個星座內(nèi)部的PV排名的id。例如，射手座用戶id9排名第一，用戶id5排名第二。

• 拓展使用：

1. 更深一步的需求是篩出每個星座最高pv的用戶，（或者說按星座去重，只取最高訪問pv的用戶）

2. 再更進一步還可以計算諸如最大在線天數(shù)等SQL問題。

3. 業(yè)務中常用用法為通過指定主鍵進行數(shù)據(jù)去重。

2.2 rank

• 功能

按照指定列進行排名，如果值相同，則排名并列，下一個排名會出現(xiàn)跳躍，即排名是不連續(xù)的。例如有前2個值一樣，那么前2行并列第一，第3行的排名則為3。

• 示例

按使用客戶端分組，統(tǒng)計年齡由高到低的排名。

select id,client,age,rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10

數(shù)據(jù)結(jié)果：

可以看到上述信息中，通過安卓和iOS客戶端的年齡大小排名。其中32歲兩個并列第三，沒有第四名，直接開始第五名。相同值進行并列。rank_id的排名是不連續(xù)的。

• 拓展使用：

常用于指定排名場景。

2.3 dense_rank

• 功能

該函數(shù)可以和rank()對照使用，按照指定列排序的順序輸出窗口中的數(shù)據(jù)的排名，如果值相同時，排名并列，下一個排名是連續(xù)遞增的，不會出現(xiàn)跳躍情況。即如果前2行的值相同，則前2行并列第1，第3行的排名則為第2。

可以理解為一個為疏松排名（rank）,一個為密集排名（dense_rank）

• 示例

按使用客戶端分組，統(tǒng)計年齡由高到低的排名。

select id,client,age,dense_rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10

數(shù)據(jù)結(jié)果：對比上文

可以看到，排名沒有出現(xiàn)跳躍，是連續(xù)的，相同排名并列其名次。例如ios的32歲兩個并列第三老。

• 拓展使用

和rank相反，我希望出現(xiàn)不中斷的排名，這樣的使用場景。

2.4 percent_rank

• 功能

見名知意，按百分比進行排名。

與 percent 函數(shù)類似，percent_rank （分布函數(shù)）函數(shù)的窗口 order by 子句所指定列中的值的返回值，是介于 0 和 1 之間的小數(shù)形式表示。

• 計算方法

(rank - 1) / (n - 1)

rank為上述rank()函數(shù)的排名，n 為當前窗口的總數(shù)。

• 示例

按使用客戶端分組，統(tǒng)計年齡由高到低的百分比排名（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

數(shù)據(jù)結(jié)果：

可以看出，用戶9和4的年齡32歲排名ios客戶端前百分之二十五，其并列排名。

• 拓展使用

該函數(shù)經(jīng)常用于較大數(shù)據(jù)量的百分比占比分析，也常用于探究數(shù)據(jù)分布分析場景。

例如可以通過分布函數(shù)踢除極值，進而求解均值，降低數(shù)據(jù)誤差。

2.5 cume_dist

• 功能

如果按升序排列，則統(tǒng)計：小于等于當前值的行數(shù)所占當前分區(qū)窗口總行數(shù)的比例。(number of rows ≤ current row)/(total number of rows）。

如果是降序排列，則統(tǒng)計：大于等于當前值的行數(shù)所占當前分區(qū)窗口總行數(shù)的比例。

• 示例

1.統(tǒng)計小于等于當前年齡的人數(shù)占總?cè)藬?shù)的比例。

另一種問法：小于等于29歲的人占總?cè)藬?shù)的比例。

select id,client,age,cume_dist() over(order by age desc) as rank_id
from temp.user_info where id <= 10
order by age

數(shù)據(jù)結(jié)果：

可以看到小于等于29歲所占人群的總比例為50%。

2.統(tǒng)計當前客戶端分區(qū)小于等于當前年齡的人數(shù)占總?cè)藬?shù)的比例。

另一種問法：ios客戶端小于等于29歲的人占總?cè)藬?shù)的比例。

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

數(shù)據(jù)結(jié)果：

可以看到ios客戶端小于等于29歲的人群占比為55.6%左右。

• 拓展使用

該函數(shù)是一個累積求比例的函數(shù)，常用于求解排名前百分之多少或者排名后百分之多少的問題。

2.6 ntile

• 功能

分桶窗口函數(shù)，用于將按指定列分組的數(shù)據(jù)按照順序切分成N片，返回當前切片值。將一個有序的數(shù)據(jù)集劃分為多個桶(bucket)，并為每行分配一個適當?shù)耐皵?shù)（切片值，第幾個切片，第幾個分區(qū)等概念）。它可用于將數(shù)據(jù)劃分為相等的小切片，為每一行分配該小切片的數(shù)字序號。

• 注意

ntile不支持rows between，range between.

• 示例

統(tǒng)計按照客戶端分組，按年齡排序，將每個窗口分成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

數(shù)據(jù)結(jié)果：

可以看到ios客戶端9人被分為三部分，需要哪一部分，只要再限制rank_id 等于幾就行。

• 拓展使用

該函數(shù)是一個分桶函數(shù)，可以按照指定的列把數(shù)據(jù)均勻的分成想要的幾部分數(shù)據(jù)。

例如，求解用戶活躍時長前百分之二十的群體，如果包含0活躍時長用戶，用百分比排序就不好計算了，而用該函數(shù)可以很快計算出來。

以上，為本次分享內(nèi)容。

后續(xù)計劃會開啟一個新的系列內(nèi)容：SQL每日一題系列，多來自各大廠的高頻面試題，有好的算法面試題也可以積極分享，互相交流。

感謝閱讀。

下一期：hive窗口分析函數(shù)使用詳解之三-求偏移量類窗口函數(shù)

按例，歡迎點擊此處關(guān)注我的個人公眾號，交流更多知識。文章來源地址http://www.zghlxwxcb.cn/news/detail-850411.html

到了這里，關(guān)于hive窗口分析函數(shù)使用詳解系列二之分組排序窗口函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！