news 2026/7/20 21:09:10

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/7/20 2:50:26

5分钟搞定F5-TTS语音合成：从零配置到实战应用完整指南

5分钟搞定F5-TTS语音合成：从零配置到实战应用完整指南 【免费下载链接】F5-TTS Official code for "F5-TTS: A Fairytaler that Fakes Fluent and Faithful Speech with Flow Matching" 项目地址: https://gitcode.com/gh_mirrors/f5/F5-TTS 还在为…

作者头像 李华
网站建设 2026/7/21 2:56:20

Qwen3-8B与14B的TTFT性能对比及优化原理

Qwen3-8B与14B的TTFT性能对比及优化原理 在当前大模型部署日益普及的背景下，用户对响应速度的要求越来越高——尤其是在智能客服、实时对话系统等高交互场景中，“快”往往比“大”更重要。通义实验室发布的Qwen3系列模型中，Qwen3-8B 和 Qwen…

作者头像 李华
网站建设 2026/7/20 20:26:24

Miniconda + conda-forge：AI开发环境管理指南

Miniconda conda-forge：AI开发环境管理指南 🧭 你有没有过这样的经历？深夜两点，终于跑通了某个论文的代码，兴奋地发给同事演示，结果对方回了一句：“我这边报错了。” 或者更糟——你在自己的机…

作者头像 李华
网站建设 2026/7/21 10:44:20

8款必选终端主题：提升开发效率的终极指南

8款必选终端主题：提升开发效率的终极指南 【免费下载链接】ohmyzsh 项目地址: https://gitcode.com/gh_mirrors/ohmy/ohmyzsh 终端作为开发者日常工作的核心工具，一个精心设计的主题能显著提升工作效率和视觉体验。通过合理的主题选择&#xff0…

作者头像 李华
网站建设 2026/7/19 6:07:03

Python深度学习：从入门到实战

目录 第一部分：基础篇 —— 奠定智慧的基石 第1章：开启深度学习之旅 1.1 人工智能、机器学习与深度学习：正本清源，理解三者关系。1.2 深度学习的“前世今生”：从赫布理论到神经网络的复兴。1.3 为何选择Python&…

作者头像 李华
网站建设 2026/7/18 5:28:22

CopyQ剪贴板管理器终极配置指南：打造高效工作流

CopyQ剪贴板管理器终极配置指南：打造高效工作流 【免费下载链接】CopyQ hluk/CopyQ: CopyQ 是一个高级剪贴板管理器，具有强大的编辑和脚本功能，可以保存系统剪贴板的内容并在以后使用。 项目地址: https://gitcode.com/gh_mirrors/co/CopyQ…

作者头像 李华