每天分享一個sql，幫助大家找到sql的快樂
需求
每個用戶連續登錄最大天數建表陳述句
create table login(
id string,
rq string
)
row format delimited fields terminated by '\t'
;資料
#這里將資料分開，便于直觀看到連續登錄天數
insert into table login values
(1,"2019-07-26"),
(1,"2019-07-27"),
(1,"2019-07-30"),
(1,"2019-07-31"),
(1,"2019-08-01"),
(2,"2019-07-26"),
(2,"2019-07-27"),
(2,"2019-07-28"),
(2,"2019-07-30"),
(2,"2019-07-31");實作
select
t3.id,
max(t3.num)
from
(select
t2.id,
count(*) as num
from
(select
t1.id,
date_sub(t1.rq,t1.n) as rq1
from
(select
id,
rq,
row_number() over(partition by id order by rq) as n
from
login
group by id,rq -- 第一次分組
)t1
)t2
group by t2.id,t2.rq1 -- 第二次分組
)t3
group by t3.id -- 第三次分組
;結果
Total MapReduce CPU Time Spent: 7 seconds 320 msec
OK
t3.id _c1
1 3
2 3
Time taken: 38.097 seconds, Fetched: 2 row(s)分析
1、通過對需求理解發現，首先需要對用戶id開窗
2、連續登錄，所以時間資訊，并按照升序，需要在視窗里面添加order by
3、核心邏輯——連續登錄的判斷是，通過排序添加序號，再用當前日期和當前序號做差，
如果得到日期相同，則表示是連續日期，所以使用row_number，
4、整體的邏輯順序是先排序添加序號欄位、計算差值日期、統計差值日期相同數量、最后得出每個用戶差值日期數最多即需求擴展
1、這里t1,t2可以合并為一步，減少一次子查詢
2、第一次分組是每個用戶每天只有一條資料，第二次分組是統計差值日期相同數量，第三次分組是統計每個用戶最大連續登錄天數知識點
1、row_number添加序號，無論欄位值是否相同
2、date_sub(日期，數值)，用日期-數值，即當前日期的前n天，回傳值是日期字串型別分析中第3點在hive sql系列（三）中計算連續榷訓中也用到了日期差值，參考鏈接：
https://blog.csdn.net/luo981695830/article/details/115312360
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/271546.html
標籤：其他
下一篇：關于我以及我為什么要學習編程