1.綜述
本文以HiveSQL語法進行代碼演示。
對于其他數(shù)據(jù)庫來說同樣也適用，比如SparkSQL，F(xiàn)linkSQL以及Mysql8，Oracle，SqlServer等傳統(tǒng)的關(guān)系型數(shù)據(jù)庫。
已更新第一類聚合函數(shù)類，點擊這里閱讀 ①SQL窗口函數(shù)系列一之聚合函數(shù)類
②SQL窗口函數(shù)系列二之分組排序窗口函數(shù)
本節(jié)介紹Hive窗口分析函數(shù)中的第三類窗口函數(shù)：偏移量類窗口函數(shù)。
在實際的應(yīng)用場景中，顧名思義，偏移量分析函數(shù)主要應(yīng)用于求解和指定偏移數(shù)據(jù)的差值。例如和上一行數(shù)據(jù)差值，和下一行數(shù)據(jù)差值。
有什么實際意義呢？例如，每行數(shù)據(jù)是天粒度的，那么上下行的差值計算就是前后天的數(shù)據(jù)增長量或者減少量，比left join,right join的方式更為簡單，效率更高。
1.1 偏移量類窗口函數(shù)
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ù)預(yù)覽
| 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.各偏移量函數(shù)的使用
2.1 lag
- 功能
Lag函數(shù)用于獲取指定列的前n（取決于偏移量的設(shè)置）個行的值，按照我們設(shè)定的分區(qū)以及排序規(guī)則。
- 語法
lag(column_name, offset, default_value) over (partition by partition_col order by order_col)
① column_name要查詢的列名
② offset 要查找的偏移量，即要獲取的行數(shù)的偏移量，默認為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;
數(shù)據(jù)結(jié)果
| 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的用戶沒有上一行，所以取到的值為我設(shè)置的默認值10.如果不設(shè)置默認值，返回null
Id 為2的用戶渠道的偏移值是id為1的用戶的年齡。
- 拓展使用
偏移量最常見的使用是當數(shù)據(jù)最細粒度為天粒度時，查詢該用戶的前一天行為和今天行為的差值或者相比上一日上漲或者下降百分比等。偽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（取決于偏移量的設(shè)置）個行的值，按照我們設(shè)定的分區(qū)以及排序規(guī)則。
- 語法
lead(column_name, offset, default_value) over (partition by partition_col order by order_col)
① column_name要查詢的列名
② offset 要查找的偏移量，即要獲取的行數(shù)的偏移量，默認為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;
數(shù)據(jù)結(jié)果
| 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 |
如上，我把偏移量設(shè)置為2，可以看到id為9和10的向下兩行沒有數(shù)據(jù)。
- 拓展使用
和lag使用場景一致，很多場景l(fā)ag和lead都可以互換，需要設(shè)置排序是正序或者倒序的區(qū)別。
2.3 first_value
- 功能
first_value用于返回分組中的第一個值，按指定的排序列。我們在使用中可以根據(jù)特定的排序規(guī)則來確定和查詢獲取每個分組的第一個值。
- 語法
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;
數(shù)據(jù)結(jié)果
| 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.
- 拓展使用
這樣查有什么用呢？
例如可以進一步求解當前用戶年齡和最小年齡或者最大年齡的差值。
如果是其他例如銷售數(shù)據(jù)，或者活躍數(shù)據(jù)等，就更加有實用意義了。
總之，SQL窗口分析函數(shù)能夠支持我們在更多的場景直接進行數(shù)據(jù)處理，進而更加深入和高效的進行數(shù)據(jù)分析。
以上，關(guān)于SQL窗口函數(shù)的三類就更完了。后續(xù)更多以SQL每日一題的方式體現(xiàn)。
感謝閱讀。
下一期：還沒想好。文章來源：http://www.zghlxwxcb.cn/news/detail-859139.html
按例，歡迎點擊此處關(guān)注我的個人公眾號，交流更多知識。文章來源地址http://www.zghlxwxcb.cn/news/detail-859139.html
到了這里，關(guān)于SQL窗口分析函數(shù)使用詳解系列三之偏移量類窗口函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
