Hive視窗函式保姆級教程

五分鐘學大資料發表於2021-06-15

在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:00
             			 cookie1第三行，往上1行值為第二行值，2021-06-10 10:00:02
             			 cookie1第六行，往上1行值為第五行值，2021-06-10 10:50:01
last_2_time: 指定了往上第2行的值，為指定預設值
						 cookie1第一行，往上2行為NULL
						 cookie1第二行，往上2行為NULL
						 cookie1第四行，往上2行為第二行值，2021-06-10 10:00:02
						 cookie1第七行，往上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.6666666666666666

5. 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_date

UNION 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 day

UNION 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 1

OFFSET：偏移量，表示從第幾條資料開始取，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 = n3

4. 連續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視窗函式
2020-10-07
Hive函式
hive視窗函式使用
2020-09-24
Hive函式
hive05_視窗函式
2024-08-08
Hive函式
Hive 視窗函式（Windowing Functions）
2017-08-28
Hive函式Function
hive視窗分析函式使用詳解系列二之分組排序視窗函式
2024-04-13
Hive函式排序
hive視窗分析函式使用詳解系列一
2024-04-07
Hive函式
大資料技術-hive視窗函式詳解
2021-08-25
大資料Hive函式
分散式事務保姆級教程
2023-09-27
分散式
視窗函式
2007-04-23
函式
開源函式計算平臺 OpenFunction 保姆級入門教程
2022-04-02
函式Function
保姆級教程，透視分析真的不難
2022-03-24
PostgreSQL：視窗函式
2020-12-01
SQL函式
SQL 視窗函式
2022-03-28
SQL函式
Mysql視窗函式
2020-10-14
MySql函式
保姆級mysql安裝教程+下載教程-附視訊教程
2020-05-18
MySql
Oracle分析函式與視窗函式
2018-05-29
Oracle函式
【函式】oracle視窗函式over()的理解
2012-04-25
函式Oracle
與SQL視窗函式相同
2018-12-03
SQL函式
PostgreSQL>視窗函式的用法
2018-07-22
SQL函式
MySQL視窗函式彙總
2024-09-27
MySql函式
mysql視窗函式中的滑動視窗
2020-11-21
MySql函式
SpringCloud搭建保姆級教程
2023-09-20
SpringGCCloud
SeaweedFS + TiKV 部署保姆級教程
2024-07-12
hive函式
2020-12-14
Hive函式
從開發到部署微服務保姆級視訊教程
2022-04-06
微服務
SQL視窗分析函式使用詳解系列三之偏移量類視窗函式
2024-04-26
SQL函式
MySQL 8.0 視窗函式-筆記
2020-04-29
MySql函式筆記
用函式控制彈出視窗
2006-07-21
函式
MySQL視窗函式用法總結
2024-05-14
MySql函式
RabbitMQ保姆級教程最佳實踐
2023-09-23
MQ
ROS通訊方式（保姆級教程）
2024-10-16
ROS
graspnet復現保姆級教程
2024-04-10
Hive函式大全
2018-08-20
Hive函式
Hive FUNCTIONS函式
2015-07-25
HiveFunction函式
MySQL資料庫基礎篇視窗函式示例解析教程RATZ
2022-03-01
MySql資料庫函式
PostgreSQL 視窗函式 ( Window Functions ) 如何使用？
2019-07-17
SQL函式Function
通俗易懂：視窗函式 | 全是案例
2022-04-20
函式
詳解SQL操作的視窗函式
2022-05-27
SQL函式

Hive視窗函式保姆級教程