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 '用戶信息測試臨時表' 

數據預覽

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為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;

數據結果

如上，我把偏移量設置為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;

數據結果

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

• 拓展使用

這樣查有什麼用呢？

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

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

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

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

感謝閱讀。

下一期：還沒想好。

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