引言

場景介紹：

許多互聯網平臺為了提高用戶的參與度和忠誠度，會推出各種連續登錄獎勵機制。例如，游戲平臺會給連續登錄的玩家發放游戲道具、金幣等獎勵；學習類 APP 會為連續登錄學習的用戶提供積分，積分可兌換課程或其他福利。通過這些激勵措施，平臺希望用戶能夠養成持續使用產品的習慣，從而提升產品的活躍度和留存率。同時，對于平臺運營者來說，分析用戶的連續登錄數據可以了解用戶的使用習慣和忠誠度，進而優化產品功能和運營策略。

題目描述：

假設我們有一個記錄用戶登錄信息的表，表名為 login_table，其中包含兩個字段：uid（用戶 ID）和 dt（登錄日期）。現在需要完成以下三個任務：

1. 查詢連續登錄超過三天的用戶：找出在一段時間內，連續登錄天數大于三天的用戶列表。這有助于平臺識別出那些高度活躍且對產品有較高忠誠度的用戶，以便進一步進行精細化運營和獎勵。
2. 查詢每個用戶連續登錄的最大天數：對于每個用戶，統計其在所有登錄記錄中連續登錄的最長時間段，這能幫助我們了解不同用戶的活躍程度差異，為個性化運營提供數據基礎。
3. 查詢一個用戶連續登錄的最大天數（可隔一天）：在計算用戶連續登錄天數時，允許中間間隔一天，只要整體登錄天數最多，就是我們要找的結果。比如用戶在 1、3、5、6 日登錄，那么其連續登錄的最大天數為 6 天。這種統計方式可以更靈活地評估用戶的活躍程度，考慮到了用戶可能因為某些特殊情況中斷一天登錄，但整體仍保持較高的使用頻率。

數據準備與代碼實現

數據準備

1	2025-01-01
1	2025-01-02
1	2025-01-03
2	2025-01-07
2	2025-01-08
3	2025-01-09
3	2025-01-10
3	2025-01-12
3	2025-01-13

1. 查詢連續登錄超過三天的用戶

思路：

1. 用戶登錄記錄編號：利用row_number()函數按uid分區并依dt升序排序生成序號rn，實現對各用戶登錄時間進行排序編號
2. 計算連續登錄首日：利用date_add函數將dt減去rn，計算每行對應的連續登錄起始日期first_day。
3. 選出連續登錄超過三天大用戶：利用group by按uid和first_day分組，結合having篩選出分組行數大于等于3的記錄，實現找出連續登錄超三天的用戶uid。

with data as (select 1 as uid,'2025-01-01' as dt union allselect 1 as uid,'2025-01-02' as dt union allselect 1 as uid,'2025-01-03' as dt union allselect 2 as uid,'2025-01-07' as dt union allselect 2 as uid,'2025-01-08' as dt union allselect 3 as uid,'2025-01-09' as dt union allselect 3 as uid,'2025-01-10' as dt union allselect 3 as uid,'2025-01-12' as dt union allselect 3 as uid,'2025-01-13' as dt
),
data2 as (select uid,dt,row_number() over (partition by uid order by dt) rn from data
),
data3 as (select uid,dt,rn,date_add(dt,-rn) as first_day from data2
)
select uid from data3 group by uid,first_day having count(1) >= 3;

2. 查詢每個用戶連續登錄的最大天數

思路：

1. 用戶登錄記錄編號：利用窗口函數row_number()，按uid分區并依dt升序排序生成序號rn，實現對各用戶登錄時間進行排序編號。
2. 計算連續登錄首日：利用date_add函數將dt減去rn，計算每行對應的連續登錄起始日期first_day。
3. 統計分組登錄天數：利用group by按uid和first_day分組，通過count(*)統計同一組合的天數login_day，以此統計出每個用戶每段連續登錄的天數。
4. 獲取用戶最大連續登錄天數：再次使用group by對uid進行分組，通過max(login_day)從每個用戶的多段連續登錄天數中選出最大值，最終得到每個用戶連續登錄的最大天數。

with data as (select 1 as uid,'2025-01-01' as dt union allselect 1 as uid,'2025-01-02' as dt union allselect 1 as uid,'2025-01-03' as dt union allselect 2 as uid,'2025-01-07' as dt union allselect 2 as uid,'2025-01-08' as dt union allselect 3 as uid,'2025-01-09' as dt union allselect 3 as uid,'2025-01-10' as dt union allselect 3 as uid,'2025-01-12' as dt union allselect 3 as uid,'2025-01-13' as dt
),
data2 as (select uid,dt,row_number() over (partition by uid order by dt) rn from data
),
data3 as (select uid,dt,rn,date_add(dt,-rn) as first_day from data2
),
data4 as (select uid,first_day,count(*) as login_day from data3 group by uid,first_day)
select uid,max(login_day) from data4 group by uid;

3. 查詢一個用戶連續登錄的最大天數，可以隔一天。解釋：1、3、5、6登錄則最大登錄天數為6天。

思路：

1. 查找上次登錄時間：利用lag函數按uid分區并依dt升序排序，實現獲取每行記錄的上一次登錄時間prev_dt。
2. 打標判斷連續登錄：利用datediff函數計算dt與prev_dt的時間差，根據差值情況打標flag，實現區分是否連續登錄，如果差值小于2天或者null（表示第一天）標記為0，都則標記為1。
3. 計算連續登錄標識和：利用sum函數按uid分組并依dt升序對flag求和，生成sum_flag，實現標識連續登錄段。
4. 計算每組時間差值：利用datediff函數對uid和sum_flag聚類分組后計算max(dt)與min(dt)的差值，實現獲取每個分組的時間跨度。
5. 獲取最大連續登錄天數：利用分組和max函數選出每個用戶的最大時間差值max(diff)+1，實現得到每個用戶連續登錄的最大天數max_login。

核心點：將相差值小于等于2的分到同一組里，然后采用分段思想計算每個分組分段的天數即為連續登錄的天數。

with data as (select 1 as uid,'2025-01-01' as dt union allselect 1 as uid,'2025-01-02' as dt union allselect 1 as uid,'2025-01-04' as dt union allselect 2 as uid,'2025-01-07' as dt union allselect 2 as uid,'2025-01-08' as dt union allselect 2 as uid,'2025-01-11' as dt union allselect 2 as uid,'2025-01-13' as dt union allselect 2 as uid,'2025-01-15' as dt union allselect 3 as uid,'2025-01-09' as dt union allselect 3 as uid,'2025-01-10' as dt union allselect 3 as uid,'2025-01-12' as dt union allselect 3 as uid,'2025-01-15' as dt
),
data2 as (select uid,dt,lag(dt, 1) over (partition by uid order by dt) prev_dt from data
),
data3 as (select uid,dt,prev_dt,if(datediff(dt, prev_dt) <= 2 or datediff(dt, prev_dt) is null, 0 ,1) flag from data2
),
data4 as (select uid,dt,prev_dt,flag,sum(flag) over(partition by uid order by dt) as sum_flag from data3
),
data5 as (select uid,datediff(max(dt),min(dt)) diff from data4 group by uid,sum_flag
)
select uid,max(diff)+1 as max_login from data5 group by uid;

知識點總結

1.窗口函數：lag、row_number

https://blog.csdn.net/Ahuuua/article/details/127136611

基本語法：函數名(參數) OVER (PARTITION BY 子句 ORDER BY 子句 ROWS/RANGE子句)

• 函數名：如sum、max、min、count、avg等聚合函數以及lead、lag行比較函數等；
• over： 關鍵字，表示前面的函數是分析函數，不是普通的集合函數；
• 分組子句：over關鍵字后面掛號內的內容

lag()比較窗口函數

lag/lead(arg1,arg2,arg3)：其中arg1為列名；arg2為偏移值，不能為負，默認為1；arg3超出記錄窗口時的默認值，當不指定默認值時，則為null。lag:向前取n行； lead:向后取n行

row_number()排序窗口函數

排序窗口函數的主要作用是為查詢結果中的每一行數據生成一個唯一的行號。這個行號是基于特定的排序規則生成的，并且可以根據不同的分組條件進行獨立編號。

	rank	row_number	dense_rank
100	1	1	1
100	1	2	1
90	3	3	2

2. 日期計算函數

日期的三種形式：

• DATE：YYYY-MM-DD，CURRENT_DATE()
• DATETIME：YYYY-MM-DD HH:MM:SS、CURRENT_TIMESTAMP()
• TIMESTAMP：時間戳，1973-12-30 15:30:00為19731230153000，UNIX_TIMESTAMP()
  常見計算函數：
• DATEDIFF(end,start)：計算end-start，單位天數
• TIMESTAMPDIFF(unit,start,end)：計算end-start，單位unit
  • unit：second、minute、hour、day、week、month、quarter（季度）、year
• DATE_ADD(date, num)：計算date+num后的時間，num參數表示要增加的時間間隔數量，正數表示增加時間，負數表示減少時間。

select CURRENT_DATE(),CURRENT_TIMESTAMP(),UNIX_TIMESTAMP();