SQL視窗分析函式使用詳解系列三之偏移量類視窗函式

1.綜述

本文以HiveSQL語法進行程式碼演示。

對於其他資料庫來說同樣也適用，比如SparkSQL，FlinkSQL以及Mysql8，Oracle，SqlServer等傳統的關係型資料庫。

已更新第一類聚合函式類，點選這裡閱讀 ①SQL視窗函式系列一之聚合函式類

②SQL視窗函式系列二之分組排序視窗函式

本節介紹Hive視窗分析函式中的第三類視窗函式：偏移量類視窗函式。

在實際的應用場景中，顧名思義，偏移量分析函式主要應用於求解和指定偏移資料的差值。例如和上一行資料差值，和下一行資料差值。

有什麼實際意義呢？例如，每行資料是天粒度的，那麼上下行的差值計算就是前後天的資料增長量或者減少量，比left join,right join的方式更為簡單，效率更高。

1.1 偏移量類視窗函式

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 lag

• 功能

Lag函式用於獲取指定列的前n（取決於偏移量的設定）個行的值，按照我們設定的分割槽以及排序規則。

• 語法

lag(column_name, offset, default_value) over (partition by partition_col order by order_col)

① column_name要查詢的列名

② offset 要查詢的偏移量，即要獲取的行數的偏移量，預設為1，例如往前1行或者n行。

③ default_value 一個可選的預設值（當沒有找到前一個行時返回的值）

• 示例

按客戶端分組，按id排序，取出上一行的年齡。

select id,client,age,lag(age,1,10) over(partition by client order by id) as lag_1_age from temp.user_info
where id <= 10
order by id;

資料結果

id	client	age	lag_1_age
1	ios	29	10
2	ios	26	29
3	android	35	10
4	ios	32	26
5	ios	33	32
6	ios	36	33
7	ios	29	36
8	ios	28	29
9	ios	32	28
10	ios	26	32

可以看到id為1的使用者沒有上一行，所以取到的值為我設定的預設值10.如果不設定預設值，返回null

Id 為2的使用者渠道的偏移值是id為1的使用者的年齡。

• 擴充使用

偏移量最常見的使用是當資料最細粒度為天粒度時，查詢該使用者的前一天行為和今天行為的差值或者相比上一日上漲或者下降百分比等。偽SQL

-- 這裡省略了偏移量和預設值
select id,pv,dt,pv-lag_pv as gap_pv  -- 當日和上一日的pv差值 
from (
		select id,pv,dt,lag(pv) over(partition by id order by dt) as lag_pv from temp.user_pv_info
) a

2.2 lead

• 功能

和lag類似，卻剛好相反。是取向下的偏移量的值。進而進行差值計算等。

用於獲取指定列的後n（取決於偏移量的設定）個行的值，按照我們設定的分割槽以及排序規則。

• 語法

lead(column_name, offset, default_value) over (partition by partition_col order by order_col)

① column_name要查詢的列名

② offset 要查詢的偏移量，即要獲取的行數的偏移量，預設為1，例如往前1行或者n行。

③ default_value 一個可選的預設值（當沒有找到前一個行時返回的值）

• 示例

按客戶端分組，按id排序，取出下二行的年齡。

select id,client,age,lead(age,2,10) over(partition by client order by id) as lead_2_age from temp.user_info
where id <= 10
order by id;

資料結果

id	client	age	lead_2_age
1	ios	29	32
2	ios	26	33
3	android	35	10
4	ios	32	36
5	ios	33	29
6	ios	36	28
7	ios	29	32
8	ios	28	26
9	ios	32	10
10	ios	26	10

如上，我把偏移量設定為2，可以看到id為9和10的向下兩行沒有資料。

• 擴充使用

和lag使用場景一致，很多場景lag和lead都可以互換，需要設定排序是正序或者倒序的區別。

2.3 first_value

• 功能

first_value用於返回分組中的第一個值，按指定的排序列。我們在使用中可以根據特定的排序規則來確定和查詢獲取每個分組的第一個值。

• 語法

first_value(expression) over(
	[partition by 列名1，列名2]
  [order by 列名3，列名4]
)

① expression要獲取第一個值的列或者表示式

② partition by 用於指定分組的列

③ order by 用於指定排序的列

• 示例

查詢不同客戶端，年齡最小的使用者。

select id,client,age,first_value(age) over(partition by client order by age) as min_age from temp.user_info
where id <= 10
order by id;

資料結果

id	client	age	min_age
1	ios	29	26
2	ios	26	26
3	android	35	35
4	ios	32	26
5	ios	33	26
6	ios	36	26
7	ios	29	26
8	ios	28	26
9	ios	32	26
10	ios	26	26

可以看到當前ios客戶端的最小年齡為26，android客戶端最小年齡為35.

• 擴充使用

這樣查有什麼用呢？

例如可以進一步求解當前使用者年齡和最小年齡或者最大年齡的差值。

如果是其他例如銷售資料，或者活躍資料等，就更加有實用意義了。

總之，SQL視窗分析函式能夠支援我們在更多的場景直接進行資料處理，進而更加深入和高效的進行資料分析。

以上，關於SQL視窗函式的三類就更完了。後續更多以SQL每日一題的方式體現。

感謝閱讀。

下一期：還沒想好。

按例，歡迎點選此處關注我的個人公眾號，交流更多知識。