news 2026/6/10 12:49:26

SQL查询连续登录用户方法详解

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
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. 性能优化建议

  1. 创建索引：
CREATEINDEXidx_user_loginONlogin_log(user_id,login_date);

  1. 分区表：如果数据量很大，按月份或用户ID范围分区

  2. 物化视图：对于频繁查询的结果可以创建物化视图

  3. 定期清理：删除历史数据，只保留最近N天的数据

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

函数/特性MySQLPostgreSQLSQL ServerOracle
日期加减DATE_ADD()+ INTERVALDATEADD()+ INTERVAL
日期差DATEDIFF()-DATEDIFF()-
行号ROW_NUMBER()ROW_NUMBER()ROW_NUMBER()ROW_NUMBER()
递归CTE支持(8.0+)支持支持支持

选择哪种方法取决于：

  • 数据量：大数据量建议使用窗口函数
  • 查询频率：频繁查询建议建立物化视图
  • 数据库版本：确保支持相关函数
  • 业务需求：是否需要实时结果还是可接受延迟
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/6/10 13:33:43

vLLM中FlashAttention与KVCache优化解析

vLLM中FlashAttention与KVCache优化解析 在当前大模型推理部署的生产实践中，高吞吐、低延迟、内存高效已成为核心诉求。传统Transformer推理方案受限于注意力机制的计算与访存瓶颈，在长序列和高并发场景下表现乏力。而 vLLM（Virtual Memory f…

作者头像 李华
网站建设 2026/6/9 21:40:21

Qwen3-14B-Base：148亿参数如何重塑大模型效率

Qwen3-14B-Base：148亿参数如何重塑大模型效率 在AI竞赛从“比谁更大”转向“看谁更聪明”的今天，一个看似矛盾的现象正在发生：参数规模不到前代一半的模型，反而在真实任务中全面超越了百亿级巨无霸。阿里巴巴通义千问团队发布的 Q…

作者头像 李华
网站建设 2026/6/10 9:23:44

0.5B参数多模态检索模型KaLM-Embedding-V2.5发布

KaLM-Embedding-V2.5：0.5B参数如何重塑多模态检索的边界 在AI模型纷纷向千亿参数冲刺的今天，一个仅含0.5B参数的小型多模态模型却悄然打破了“大即强”的迷思。HIT-TMG团队发布的 KaLM-Embedding-V2.5，不仅实现了文本与图像在统一语义空间中…

作者头像 李华
网站建设 2026/6/8 13:26:45

python基于协同过滤算法的音乐推荐播放器_3e3bx4x2_论文

目录 已开发项目效果实现截图关于我系统介绍开发技术路线核心代码参考示例本项目开发思路结论源码lw获取/同行可拿货,招校园代理 ：文章底部获取博主联系方式！ 已开发项目效果实现截图 同行可拿货,招校园代理 python基于协同过滤算法的音乐推荐播放器_…

作者头像 李华
网站建设 2026/6/6 7:58:45

python高校后勤报修系统设计与实现_79tvn57g_zl016--论文

目录已开发项目效果实现截图关于我系统介绍开发技术路线核心代码参考示例本项目开发思路结论源码lw获取/同行可拿货,招校园代理 ：文章底部获取博主联系方式！已开发项目效果实现截图 同行可拿货,招校园代理 python高校后勤报修系统设计与实现_79tvn57g…

作者头像 李华
网站建设 2026/6/8 7:15:18

ADVANCE Day25

浙大疏锦行 📘 Day 25 实战作业：Python 异常处理 —— 提升代码健壮性 1. 作业综述 核心目标： 本作业旨在掌握 Python 异常处理的“四大金刚”机制（try-except-else-finally）。我们将通过模拟真实的数据处理和模型训…

作者头像 李华