問題引入
下面是某游戲公司記錄的用戶每日登錄數據, 計算每個用戶最大的連續登錄天數，定義連續登錄時可以間隔一天。舉例：如果一個用戶在 1,3,5,6,9 登錄了游戲，則視為連續 6 天登錄。
id dt1001 2021-12-121002 2021-12-121001 2021-12-131001 2021-12-141001 2021-12-161002 2021-12-161001 2021-12-191002 2021-12-171001 2021-12-20
這是個連續問題的升級版，當滿足某種要求時我們也是算作連續的，所以不能使用傳統的連續編號，然后做差值的解法了。核心思路解析如下：
這種解法是比較常見的，很多場景都可以這樣使用。還有比如計算用戶的會話數，當兩次會話時間超過1分鐘時就算做不同的會話，也可以這樣做。
#這里假設樣例數據存在tmp表里面
select
user_id
,max(diff) as max_login_days
from
(selectuser_id ,user_group,datediff(date(max(dt)),date(min(dt)),'dd')+1 as diff --拿到每個用戶下，連續時間里面最大日期與最小日期的差值加1就得到來連續天數from(selectuser_id,dt-- 如果當前日期與上一個日期的差值在2之內，那么就給0，否則給1,sum(if(datediff(date(dt),date(last_dt),'dd')<=2,0,1)) over(partition by user_id order by dt) as user_groupfrom(selectuser_id,dt,lag(dt,1,dt) over(partition by user_id order by dt) as last_dt --根據user_id分組，拿到當前行的上一個日期，沒有上一個就給自己本身的值from tmp)t1)t1group by user_id ,user_group
)t1
group by user_id
;
?思考： 為什么user_group這個字段可以作為分組條件？
條件求和：
? ? ?if(datediff(date(dt),date(last_dt),'dd')<=2,0,1)這個條件判斷當前行日期與上一行日期的差異。如果差異小于或等于2天，則返回0，否則返回1。累加生成
user_group：
窗口函數
SUM(...) OVER(...)的工作方式是在指定的窗口內累加值。在這種情況下，窗口是由PARTITION BY user_id ORDER BY dt定義的，這意味著：累加是在每個user_id分區內獨立進行的，所以不同用戶的累加是隔離的。在每個分區內，累加是按照日期dt的順序進行的。- 對于連續登錄的日期（差異小于等于2天），由于返回的是0，
sum函數累加值不變，表示這些日期屬于同一個登錄周期。
相似問題
問題：如下為某電商公司用戶訪問網站的數據，包括用戶id和訪問時間兩個字段。現有如下規則：如果某個用戶的連續的訪問記錄時間間隔小于60秒，則屬于同一個會話，現在需要計算每個用戶有多少個會話。比如A用戶在第1秒，60秒，200秒，230秒有三次訪問記錄，則該用戶有2個會話，其中第一個會話是第1秒和第60秒的記錄，第二個會話是第200秒和230秒的記錄。
user_id ts1001 169200000001001 169200000501002 169200000651002 169200000801001 169200001501002 16920000160
#sql可以簡化，這里只是為了拆分每一步是如何做的而沒有做合并。
with tmp as (select 1001 as user_id,16920000000 as tsunion allselect 1001 as user_id,16920000050 as tsunion allselect 1002 as user_id,16920000065 as tsunion allselect 1002 as user_id,16920000080 as tsunion allselect 1001 as user_id,16920000150 as tsunion allselect 1002 as user_id,16920000160 as ts
)
select
user_id
,count(distinct user_group) as user_group_cnt
from
(selectuser_id,ts-- 開窗做累加,sum(flag) over(partition by user_id order by ts) as user_groupfrom(selectuser_id,ts-- 判斷當前行的時間與上一行的差值,if(ts-last_ts<60,0,1) as flagfrom(selectuser_id,ts-- 取當前行的上一個時間，沒有上一行就給自身的時間,lag(ts,1,ts) over(partition by user_id order by ts) as last_tsfrom tmp)t1)t1
)t1
group by user_id
;
