SQL查询连续登录用户方法详解-程序员充电站

1. 基本数据表结构

假设有一个登录记录表：

CREATETABLElogin_log(user_idINT,login_dateDATE);

示例数据：

INSERTINTOlogin_logVALUES(1,'2024-01-01'),(1,'2024-01-02'),(1,'2024-01-03'),(1,'2024-01-05'),-- 这里断了一天(1,'2024-01-06'),(1,'2024-01-07'),(2,'2024-01-01'),(2,'2024-01-02'),(2,'2024-01-04');-- 这里断了一天

2. 方法一：使用窗口函数（推荐）

2.1 查询连续登录3天及以上的用户

WITHranked_logsAS(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)asrnFROMlogin_logGROUPBYuser_id,login_date-- 去重，防止一天多次登录),date_diffAS(SELECTuser_id,login_date,DATE_SUB(login_date,INTERVALrnDAY)asgroup_dateFROMranked_logs)SELECTuser_id,MIN(login_date)asstart_date,MAX(login_date)asend_date,COUNT(*)asconsecutive_daysFROMdate_diffGROUPBYuser_id,group_dateHAVINGCOUNT(*)>=3ORDERBYuser_id,start_date;

2.2 使用LEAD/LAG函数的简化版

WITHconsecutive_groupsAS(SELECTuser_id,login_date,LAG(login_date)OVER(PARTITIONBYuser_idORDERBYlogin_date)asprev_date,LEAD(login_date)OVER(PARTITIONBYuser_idORDERBYlogin_date)asnext_dateFROM(SELECTDISTINCTuser_id,login_dateFROMlogin_log)t)SELECTuser_id,login_dateasconsecutive_dateFROMconsecutive_groupsWHERE-- 连续3天的情况：当前日期、前1天、前2天都存在(login_date=prev_date+INTERVAL1DAYANDlogin_date=prev_date+INTERVAL2DAY)OR-- 或者检查连续区间(login_date=prev_date+INTERVAL1DAYANDlogin_date=next_date-INTERVAL1DAY)OR(login_date=next_date-INTERVAL1DAYANDlogin_date=next_date-INTERVAL2DAY);

3. 方法二：使用自连接

SELECTDISTINCTl1.user_idFROMlogin_log l1JOINlogin_log l2ONl1.user_id=l2.user_idANDl2.login_date=l1.login_date+INTERVAL1DAYJOINlogin_log l3ONl1.user_id=l3.user_idANDl3.login_date=l1.login_date+INTERVAL2DAYWHEREEXISTS(SELECT1FROMlogin_logWHEREuser_id=l1.user_idANDlogin_date=l1.login_date+INTERVAL1DAY)ANDEXISTS(SELECT1FROMlogin_logWHEREuser_id=l1.user_idANDlogin_date=l1.login_date+INTERVAL2DAY);

4. 方法三：使用递归CTE（复杂但功能强大）

WITHRECURSIVE consecutive_loginAS(-- 基础查询：每个用户的首次登录SELECTuser_id,login_date,login_dateasstart_date,1asconsecutive_daysFROM(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)asrnFROMlogin_log)tWHERErn=1UNIONALL-- 递归部分：查找连续的下一天SELECTcl.user_id,ll.login_date,CASEWHENll.login_date=cl.login_date+INTERVAL1DAYTHENcl.start_dateELSEll.login_dateENDasstart_date,CASEWHENll.login_date=cl.login_date+INTERVAL1DAYTHENcl.consecutive_days+1ELSE1ENDasconsecutive_daysFROMconsecutive_login clJOINlogin_log llONcl.user_id=ll.user_idANDll.login_date>cl.login_dateWHEREll.login_date=(SELECTMIN(login_date)FROMlogin_logWHEREuser_id=cl.user_idANDlogin_date>cl.login_date))SELECTuser_id,MAX(consecutive_days)asmax_consecutive_daysFROMconsecutive_loginGROUPBYuser_idHAVINGMAX(consecutive_days)>=3;

5. 实用查询示例

5.1 查询每个用户的最大连续登录天数

WITHranked_logsAS(SELECTuser_id,login_date,DATE_SUB(login_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)DAY)asgroup_dateFROM(SELECTDISTINCTuser_id,login_dateFROMlogin_log)t)SELECTuser_id,MAX(consecutive_days)asmax_consecutive_daysFROM(SELECTuser_id,group_date,COUNT(*)asconsecutive_daysFROMranked_logsGROUPBYuser_id,group_date)groupsGROUPBYuser_idORDERBYmax_consecutive_daysDESC;

5.2 查询指定时间段内的连续登录

WITHranked_logsAS(SELECTuser_id,login_date,DATE_SUB(login_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)DAY)asgroup_dateFROM(SELECTDISTINCTuser_id,login_dateFROMlogin_logWHERElogin_dateBETWEEN'2024-01-01'AND'2024-01-31')t)SELECTuser_id,COUNT(*)asconsecutive_days,MIN(login_date)asstart_date,MAX(login_date)asend_dateFROMranked_logsGROUPBYuser_id,group_dateHAVINGCOUNT(*)>=7-- 连续7天登录ORDERBYconsecutive_daysDESC;

5.3 查询连续登录中断的情况

WITHlogin_gapsAS(SELECTuser_id,login_date,LEAD(login_date)OVER(PARTITIONBYuser_idORDERBYlogin_date)asnext_date,DATEDIFF(LEAD(login_date)OVER(PARTITIONBYuser_idORDERBYlogin_date),login_date)asgap_daysFROM(SELECTDISTINCTuser_id,login_dateFROMlogin_log)t)SELECTuser_id,login_dateaslast_login_before_gap,next_dateasnext_login_after_gap,gap_days-1asmissed_daysFROMlogin_gapsWHEREgap_days>1ORDERBYuser_id,login_date;

6. 性能优化建议

创建索引：

CREATEINDEXidx_user_loginONlogin_log(user_id,login_date);

分区表：如果数据量很大，按月份或用户ID范围分区
物化视图：对于频繁查询的结果可以创建物化视图
定期清理：删除历史数据，只保留最近N天的数据

7. 不同数据库的语法差异

函数/特性	MySQL	PostgreSQL	SQL Server	Oracle
日期加减	DATE_ADD()	+ INTERVAL	DATEADD()	+ INTERVAL
日期差	DATEDIFF()	-	DATEDIFF()	-
行号	ROW_NUMBER()	ROW_NUMBER()	ROW_NUMBER()	ROW_NUMBER()
递归CTE	支持(8.0+)	支持	支持	支持