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+) | 支持 | 支持 | 支持 |
选择哪种方法取决于：
- 数据量：大数据量建议使用窗口函数
- 查询频率：频繁查询建议建立物化视图
- 数据库版本：确保支持相关函数
- 业务需求：是否需要实时结果还是可接受延迟
