Hive窗口函数保姆级教程
在SQL中有一類函數叫做聚合函數，例如sum()、avg()、max()等等，這類函數可以將多行數據按照規則聚集為一行，一般來講聚集后的行數是要少于聚集前的行數的。但是有時我們想要既顯示聚集前的數據，又要顯示聚集后的數據，這時我們便引入了窗口函數。窗口函數又叫OLAP函數/分析函數，窗口函數兼具分組和排序功能。
本文分為兩部分：
第一部分是Hive窗口函數詳解，剖析各種窗口函數（幾乎涵蓋Hive所有的窗口函數）；
第二部分是窗口函數實際應用，這部分總共有五個例子，都是工作常用、面試必問的非常經典的例子。
Hive 窗口函數
窗口函數最重要的關鍵字是 partition by 和 order by
具體語法如下：XXX over (partition by xxx order by xxx)
特別注意：over()里面的 partition by 和 order by 都不是必選的，over()里面可以只有partition by，也可以只有order by，也可以兩個都沒有，大家需根據需求靈活運用。
窗口函數我劃分了幾個大類，我們一類一類的講解。
1. SUM、AVG、MIN、MAX
講解這幾個窗口函數前，先創建一個表，以實際例子講解大家更容易理解。
首先創建用戶訪問頁面表：user_pv
create?table?user_pv( cookieid?string,??--?用戶登錄的cookie，即用戶標識 createtime?string,?--?日期 pv?int?--?頁面訪問量 );?給上面這個表加上如下數據：
cookie1,2021-05-10,1 cookie1,2021-05-11,5 cookie1,2021-05-12,7 cookie1,2021-05-13,3 cookie1,2021-05-14,2 cookie1,2021-05-15,4 cookie1,2021-05-16,4-
SUM()使用
執行如下查詢語句：
select?cookieid,createtime,pv, sum(pv)?over(partition?by?cookieid?order?by?createtime)?as?pv1? from?user_pv;結果如下：（因命令行原因，下圖字段名和值是錯位的，請注意辨別！）
執行如下查詢語句：
select?cookieid,createtime,pv, sum(pv)?over(partition?by?cookieid?)?as?pv1? from?user_pv;結果如下：
第一條SQL的over()里面加 order by ，第二條SQL沒加order by ，結果差別很大
所以要注意了：
-
over()里面加 order by 表示：分組內從起點到當前行的pv累積，如，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號；
-
over()里面不加 order by 表示：將分組內所有值累加。
AVG，MIN，MAX，和SUM用法一樣，這里就不展開講了，但是要注意 AVG，MIN，MAX 的over()里面加不加 order by 也和SUM一樣，如 AVG 求平均值，如果加上 order by，表示分組內從起點到當前行的平局值，不是全部的平局值。MIN，MAX 同理。
2. ROW_NUMBER、RANK、DENSE_RANK、NTILE
還是用上述的用戶登錄日志表：user_pv，里面的數據換成如下所示：
cookie1,2021-05-10,1 cookie1,2021-05-11,5 cookie1,2021-05-12,7 cookie1,2021-05-13,3 cookie1,2021-05-14,2 cookie1,2021-05-15,4 cookie1,2021-05-16,4 cookie2,2021-05-10,2 cookie2,2021-05-11,3 cookie2,2021-05-12,5 cookie2,2021-05-13,6 cookie2,2021-05-14,3 cookie2,2021-05-15,9 cookie2,2021-05-16,7-
ROW_NUMBER()使用：
ROW_NUMBER()從1開始，按照順序，生成分組內記錄的序列。
SELECT? cookieid, createtime, pv, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn? FROM?user_pv;結果如下：
-
RANK 和 DENSE_RANK 使用：
RANK() 生成數據項在分組中的排名，排名相等會在名次中留下空位。
DENSE_RANK()生成數據項在分組中的排名，排名相等會在名次中不會留下空位。
SELECT? cookieid, createtime, pv, RANK()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn1, DENSE_RANK()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn2, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?DESC)?AS?rn3? FROM?user_pv? WHERE?cookieid?=?'cookie1';結果如下：
-
NTILE的使用：
有時會有這樣的需求:如果數據排序后分為三部分，業務人員只關心其中的一部分，如何將這中間的三分之一數據拿出來呢?NTILE函數即可以滿足。
ntile可以看成是：把有序的數據集合平均分配到指定的數量（num）個桶中, 將桶號分配給每一行。如果不能平均分配，則優先分配較小編號的桶，并且各個桶中能放的行數最多相差1。
然后可以根據桶號，選取前或后 n分之幾的數據。數據會完整展示出來，只是給相應的數據打標簽；具體要取幾分之幾的數據，需要再嵌套一層根據標簽取出。
SELECT? cookieid, createtime, pv, NTILE(2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn1, NTILE(3)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn2, NTILE(4)?OVER(ORDER?BY?createtime)?AS?rn3 FROM?user_pv? ORDER?BY?cookieid,createtime;結果如下：
3. LAG、LEAD、FIRST_VALUE、LAST_VALUE
講解這幾個窗口函數時還是以實例講解，首先創建用戶訪問頁面表：user_url
CREATE?TABLE?user_url?( cookieid?string, createtime?string,??--頁面訪問時間 url?string???????--被訪問頁面 );表中加入如下數據：
cookie1,2021-06-10?10:00:02,url2 cookie1,2021-06-10?10:00:00,url1 cookie1,2021-06-10?10:03:04,1url3 cookie1,2021-06-10?10:50:05,url6 cookie1,2021-06-10?11:00:00,url7 cookie1,2021-06-10?10:10:00,url4 cookie1,2021-06-10?10:50:01,url5 cookie2,2021-06-10?10:00:02,url22 cookie2,2021-06-10?10:00:00,url11 cookie2,2021-06-10?10:03:04,1url33 cookie2,2021-06-10?10:50:05,url66 cookie2,2021-06-10?11:00:00,url77 cookie2,2021-06-10?10:10:00,url44 cookie2,2021-06-10?10:50:01,url55-
LAG的使用：
LAG(col,n,DEFAULT) 用于統計窗口內往上第n行值。
第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL）
SELECT?cookieid, createtime, url, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn, LAG(createtime,1,'1970-01-01?00:00:00')?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last_1_time, LAG(createtime,2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last_2_time? FROM?user_url;結果如下：
解釋：
last_1_time:?指定了往上第1行的值，default為'1970-01-01?00:00:00'??cookie1第一行，往上1行為NULL,因此取默認值?1970-01-01?00:00:00cookie1第三行，往上1行值為第二行值，2021-06-10?10:00:02cookie1第六行，往上1行值為第五行值，2021-06-10?10:50:01 last_2_time:?指定了往上第2行的值，為指定默認值cookie1第一行，往上2行為NULLcookie1第二行，往上2行為NULLcookie1第四行，往上2行為第二行值，2021-06-10?10:00:02cookie1第七行，往上2行為第五行值，2021-06-10?10:50:01-
LEAD的使用：
與LAG相反
LEAD(col,n,DEFAULT) 用于統計窗口內往下第n行值。
第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL）
SELECT?cookieid, createtime, url, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn, LEAD(createtime,1,'1970-01-01?00:00:00')?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?next_1_time, LEAD(createtime,2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?next_2_time? FROM?user_url;結果如下：
-
FIRST_VALUE的使用：
取分組內排序后，截止到當前行，第一個值。
SELECT?cookieid, createtime, url, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn, FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?first1? FROM?user_url;結果如下：
-
LAST_VALUE的使用：
取分組內排序后，截止到當前行，最后一個值。
SELECT?cookieid, createtime, url, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn, LAST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last1? FROM?user_url;結果如下：
如果想要取分組內排序后最后一個值，則需要變通一下：
SELECT?cookieid, createtime, url, ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn, LAST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last1, FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime?DESC)?AS?last2? FROM?user_url? ORDER?BY?cookieid,createtime;注意上述SQL，使用的是 FIRST_VALUE 的倒序取出分組內排序最后一個值！
結果如下：
此處要特別注意order by
如果不指定ORDER BY，則進行排序混亂，會出現錯誤的結果
SELECT?cookieid, createtime, url, FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid)?AS?first2?? FROM?user_url;結果如下：
上述 url2 和 url55 的createtime即不屬于最靠前的時間也不屬于最靠后的時間，所以結果是混亂的。
4. CUME_DIST
先創建一張員工薪水表：staff_salary
CREATE?EXTERNAL?TABLE?staff_salary?( dept?string, userid?string, sal?int );表中加入如下數據：
d1,user1,1000 d1,user2,2000 d1,user3,3000 d2,user4,4000 d2,user5,5000-
CUME_DIST的使用：
此函數的結果和order by的排序順序有關系。
CUME_DIST：小于等于當前值的行數/分組內總行數。 order默認順序 ：正序
比如，統計小于等于當前薪水的人數，所占總人數的比例。
SELECT? dept, userid, sal, CUME_DIST()?OVER(ORDER?BY?sal)?AS?rn1, CUME_DIST()?OVER(PARTITION?BY?dept?ORDER?BY?sal)?AS?rn2? FROM?staff_salary;結果如下：
解釋：
rn1:?沒有partition,所有數據均為1組，總行數為5，第一行：小于等于1000的行數為1，因此，1/5=0.2第三行：小于等于3000的行數為3，因此，3/5=0.6 rn2:?按照部門分組，dpet=d1的行數為3,第二行：小于等于2000的行數為2，因此，2/3=0.66666666666666665. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP
這幾個分析函數通常用于OLAP中，不能累加，而且需要根據不同維度上鉆和下鉆的指標統計，比如，分小時、天、月的UV數。
還是先創建一個用戶訪問表：user_date
CREATE?TABLE?user_date?( month?STRING, day?STRING,? cookieid?STRING? );表中加入如下數據：
2021-03,2021-03-10,cookie1 2021-03,2021-03-10,cookie5 2021-03,2021-03-12,cookie7 2021-04,2021-04-12,cookie3 2021-04,2021-04-13,cookie2 2021-04,2021-04-13,cookie4 2021-04,2021-04-16,cookie4 2021-03,2021-03-10,cookie2 2021-03,2021-03-10,cookie3 2021-04,2021-04-12,cookie5 2021-04,2021-04-13,cookie6 2021-04,2021-04-15,cookie3 2021-04,2021-04-15,cookie2 2021-04,2021-04-16,cookie1-
GROUPING SETS的使用：
grouping sets是一種將多個group by 邏輯寫在一個sql語句中的便利寫法。
等價于將不同維度的GROUP BY結果集進行UNION ALL。
SELECT? month, day, COUNT(DISTINCT?cookieid)?AS?uv, GROUPING__ID? FROM?user_date? GROUP?BY?month,day? GROUPING?SETS?(month,day)? ORDER?BY?GROUPING__ID;注：上述SQL中的GROUPING__ID，是個關鍵字，表示結果屬于哪一個分組集合，根據grouping sets中的分組條件month，day，1是代表month，2是代表day。
結果如下：
上述SQL等價于：
SELECT?month, NULL?as?day, COUNT(DISTINCT?cookieid)?AS?uv, 1?AS?GROUPING__ID? FROM?user_date? GROUP?BY?month?UNION?ALL?SELECT?NULL?as?month, day, COUNT(DISTINCT?cookieid)?AS?uv, 2?AS?GROUPING__ID? FROM?user_date? GROUP?BY?day;-
CUBE的使用：
根據GROUP BY的維度的所有組合進行聚合。
SELECT? month, day, COUNT(DISTINCT?cookieid)?AS?uv, GROUPING__ID? FROM?user_date? GROUP?BY?month,day? WITH?CUBE? ORDER?BY?GROUPING__ID;結果如下：
上述SQL等價于：
SELECT?NULL,NULL,COUNT(DISTINCT?cookieid)?AS?uv,0?AS?GROUPING__ID?FROM?user_dateUNION?ALL?SELECT?month,NULL,COUNT(DISTINCT?cookieid)?AS?uv,1?AS?GROUPING__ID?FROM?user_date?GROUP?BY?month?UNION?ALL?SELECT?NULL,day,COUNT(DISTINCT?cookieid)?AS?uv,2?AS?GROUPING__ID?FROM?user_date?GROUP?BY?dayUNION?ALL?SELECT?month,day,COUNT(DISTINCT?cookieid)?AS?uv,3?AS?GROUPING__ID?FROM?user_date?GROUP?BY?month,day;-
ROLLUP的使用：
是CUBE的子集，以最左側的維度為主，從該維度進行層級聚合。
比如，以month維度進行層級聚合：
SELECT? month, day, COUNT(DISTINCT?cookieid)?AS?uv, GROUPING__ID?? FROM?user_date? GROUP?BY?month,day WITH?ROLLUP? ORDER?BY?GROUPING__ID;結果如下：
把month和day調換順序，則以day維度進行層級聚合：
SELECT? day, month, COUNT(DISTINCT?cookieid)?AS?uv, GROUPING__ID?? FROM?user_date? GROUP?BY?day,month? WITH?ROLLUP? ORDER?BY?GROUPING__ID;結果如下：
這里，根據日和月進行聚合，和根據日聚合結果一樣，因為有父子關系，如果是其他維度組合的話，就會不一樣。
窗口函數實際應用
1. 第二高的薪水
難度簡單。
編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary）。
+----+--------+ |?Id?|?Salary?| +----+--------+ |?1??|?100????| |?2??|?200????| |?3??|?300????| +----+--------+例如上述 Employee 表，SQL查詢應該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應返回 null。
+---------------------+ |?SecondHighestSalary?| +---------------------+ |?200?????????????????| +---------------------+這道題可以用 row_number 函數解決。
參考代碼：
SELECT*FROM(SELECT?Salary,?row_number()?over(order?by?Salary?desc)?rk?FROM?Employee)?t?WHERE?t.rk?=?2;更簡單的代碼：
SELECT?DISTINCT?Salary FROM?Employee ORDER?BY?Salary?DESC LIMIT?1?OFFSET?1OFFSET：偏移量，表示從第幾條數據開始取，0代表第1條數據。
2. 分數排名
難度簡單。
編寫一個 SQL 查詢來實現分數排名。
如果兩個分數相同，則兩個分數排名（Rank）相同。請注意，平分后的下一個名次應該是下一個連續的整數值。換句話說，名次之間不應該有“間隔”。
+----+-------+ |?Id?|?Score?| +----+-------+ |?1??|?3.50??| |?2??|?3.65??| |?3??|?4.00??| |?4??|?3.85??| |?5??|?4.00??| |?6??|?3.65??| +----+-------+例如，根據上述給定的 Scores 表，你的查詢應該返回（按分數從高到低排列）：
+-------+------+ |?Score?|?Rank?| +-------+------+ |?4.00??|?1????| |?4.00??|?1????| |?3.85??|?2????| |?3.65??|?3????| |?3.65??|?3????| |?3.50??|?4????| +-------+------+參考代碼：
SELECT?Score, dense_rank()?over(order?by?Score?desc)?as?`Rank` FROM?Scores;3. 連續出現的數字
難度中等。
編寫一個 SQL 查詢，查找所有至少連續出現三次的數字。
+----+-----+ |?Id?|?Num?| +----+-----+ |?1??|??1??| |?2??|??1??| |?3??|??1??| |?4??|??2??| |?5??|??1??| |?6??|??2??| |?7??|??2??| +----+-----+例如，給定上面的 Logs 表， 1 是唯一連續出現至少三次的數字。
+-----------------+ |?ConsecutiveNums?| +-----------------+ |?1???????????????| +-----------------+參考代碼：
SELECT?DISTINCT?`Num`?as?ConsecutiveNums FROM(SELECT?Num,lead(Num,?1,?null)?over(order?by?id)?n2,lead(Num,?2,?null)?over(order?by?id)?n3?FROM?Logs)?t1 WHERE?Num?=?n2?and?Num?=?n34. 連續N天登錄
難度困難。
寫一個 SQL 查詢, 找到活躍用戶的 id 和 name，活躍用戶是指那些至少連續 5 天登錄賬戶的用戶，返回的結果表按照 id 排序。
表 Accounts：
+----+-----------+ |?id?|?name??????| +----+-----------+ |?1??|?Winston???| |?7??|?Jonathan??| +----+-----------+表 Logins：
+----+-------------+ |?id?|?login_date??| +----+-------------+ |?7??|?2020-05-30??| |?1??|?2020-05-30??| |?7??|?2020-05-31??| |?7??|?2020-06-01??| |?7??|?2020-06-02??| |?7??|?2020-06-02??| |?7??|?2020-06-03??| |?1??|?2020-06-07??| |?7??|?2020-06-10??| +----+-------------+例如，給定上面的Accounts和Logins表，至少連續 5 天登錄賬戶的是id=7的用戶
+----+-----------+ |?id?|?name??????| +----+-----------+ |?7??|?Jonathan??| +----+-----------+思路：
去重：由于每個人可能一天可能不止登陸一次，需要去重
排序：對每個ID的登錄日期排序
差值：計算登錄日期與排序之間的差值，找到連續登陸的記錄
連續登錄天數計算：select id, count(*) group by id, 差值（偽代碼）
取出登錄5天以上的記錄
通過表合并，取出id對應用戶名
參考代碼：
SELECT?DISTINCT?b.id,?name FROM(SELECT?id,?login_date,DATE_SUB(login_date,?ROW_NUMBER()?OVER(PARTITION?BY?id?ORDER?BY?login_date))?AS?diff?FROM(SELECT?DISTINCT?id,?login_date?FROM?Logins)?a)?b INNER?JOIN?Accounts?ac ON?b.id?=?ac.id GROUP?BY?b.id,?diff HAVING?COUNT(b.id)?>=?5注意點：
DATE_SUB的應用：DATE_SUB (DATE, X)，注意，X為正數表示當前日期的前X天；
如何找連續日期：通過排序與登錄日期之間的差值，因為排序連續，因此若登錄日期連續，則差值一致；
GROUP BY和HAVING的應用：通過id和差值的GROUP BY，用COUNT找到連續天數大于5天的id，注意COUNT不是一定要出現在SELECT后，可以直接用在HAVING中
5. 給定數字的頻率查詢中位數
難度困難。
Numbers 表保存數字的值及其頻率。
+----------+-------------+ |??Number??|??Frequency??| +----------+-------------| |??0???????|??7??????????| |??1???????|??1??????????| |??2???????|??3??????????| |??3???????|??1??????????| +----------+-------------+在此表中，數字為 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位數是 (0 + 0) / 2 = 0。
+--------+ |?median?| +--------| |?0.0000?| +--------+請編寫一個查詢來查找所有數字的中位數并將結果命名為 median 。
參考代碼：
select avg(cast(number?as?float))?as?median from(select?Number,Frequency,sum(Frequency)?over(order?by?Number)?-?Frequency?as?prev_sum,sum(Frequency)?over(order?by?Number)?as?curr_sum?from?Numbers)?t1,?(select?sum(Frequency)?as?total_sum?from?Numbers)?t2 where t1.prev_sum?<=?(cast(t2.total_sum?as?float)?/?2)? and t1.curr_sum?>=?(cast(t2.total_sum?as?float)?/?2)總結
以上是生活随笔為你收集整理的Hive窗口函数保姆级教程的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ObjectC基础之函数调用
- 下一篇： 记录用HDFview和Panoply软件