1. SQL每日一题系列的价值与定位SQL作为关系型数据库的标准查询语言是每个开发者必须掌握的硬技能。但很多人在学习过程中容易陷入看懂了但写不出来的困境——这就是SQL每日一题系列存在的意义。我维护这个系列已有三年时间核心设计原则是每道题都必须是真实业务场景的抽象拒绝脱离实际的学术题。今天的第7题也不例外它模拟了电商平台中经典的用户连续登录分析场景。这类问题在用户留存分析、活动参与统计等业务中极为常见。2. 题目场景解析2.1 业务背景还原假设我们有一个电商平台的用户登录记录表CREATE TABLE user_logins ( user_id INT, login_date DATE );业务部门需要统计连续登录超过3天的活跃用户用于精准营销。这个需求看似简单但隐藏着几个技术难点如何定义连续自然日连续还是登录行为连续如何处理用户一天内多次登录的情况如何高效计算大规模用户数据2.2 数据样本示例INSERT INTO user_logins VALUES (1, 2023-01-01), (1, 2023-01-02), (1, 2023-01-03), -- 连续3天 (2, 2023-01-01), (2, 2023-01-03), -- 不连续 (3, 2023-01-01), (3, 2023-01-02), (3, 2023-01-03), (3, 2023-01-04); -- 连续4天3. 解决方案深度剖析3.1 核心解决思路这类问题的通用解法是日期差值法对每个用户的登录日期排序计算日期与排序号的差值相同差值的日期即为连续日期WITH ranked_logins AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM user_logins ), date_groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS date_group FROM ranked_logins ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM date_groups GROUP BY user_id, date_group HAVING COUNT(*) 3;3.2 关键技术点解析窗口函数ROW_NUMBER()是解题的核心它为每个用户的登录记录建立序号基准日期计算DATE_SUB(login_date, INTERVAL rn DAY)这个魔法操作是关键对于连续日期这个差值会相同因为日期和序号同步增长分组统计最后按用户和date_group分组过滤出满足条件的记录3.3 性能优化方案当数据量较大时比如百万级用户可以考虑以下优化-- 添加日期范围限制减少计算量 WHERE login_date BETWEEN 2023-01-01 AND 2023-01-31 -- 对user_id和login_date建立复合索引 CREATE INDEX idx_user_login ON user_logins(user_id, login_date); -- 使用物化视图预计算MySQL 8.0 WITH RECURSIVE ... -- 递归CTE方案4. 变体问题与解决方案4.1 变体1最长连续登录天数SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM ( -- 沿用前面的date_groups查询 ) t GROUP BY user_id;4.2 变体2特定时间段的连续登录-- 春节期间的连续登录 WHERE login_date BETWEEN 2023-01-21 AND 2023-01-274.3 变体3跨月连续登录处理-- 使用DATE_FORMAT确保跨月计算准确 DATE_FORMAT(login_date, %Y-%m-%d)5. 实战经验与避坑指南时区陷阱服务器时区与业务时区不一致会导致日期判断错误-- 明确指定时区 CONVERT_TZ(login_date, UTC, Asia/Shanghai)重复数据处理使用DISTINCT避免重复登录记录干扰SELECT DISTINCT user_id, DATE(login_date) FROM user_logins空值处理添加NULL检查避免计算异常WHERE login_date IS NOT NULL执行计划分析用EXPLAIN检查是否使用了索引EXPLAIN SELECT ... -- 分析查询性能分区表策略对于超大规模数据按用户ID哈希分区PARTITION BY HASH(user_id) PARTITIONS 166. 不同数据库的语法适配6.1 PostgreSQL方案-- 使用PostgreSQL的日期计算语法 (login_date - rn * INTERVAL 1 day) AS date_group6.2 SQL Server方案-- 使用DATEADD函数 DATEADD(day, -rn, login_date) AS date_group6.3 Oracle方案-- 使用Oracle的日期计算 login_date - rn AS date_group7. 可视化分析扩展将结果导入BI工具时可以生成热力图直观展示-- 生成日历格式数据 SELECT user_id, login_date, DAYOFWEEK(login_date) AS weekday, WEEK(login_date) AS week_num FROM user_logins8. 真实业务场景延伸在实际业务中这类分析通常需要结合其他数据-- 结合订单数据计算转化率 SELECT u.user_id, COUNT(DISTINCT o.order_id) AS order_count FROM user_logins u LEFT JOIN orders o ON u.user_id o.user_id AND o.order_date BETWEEN u.login_date AND DATE_ADD(u.login_date, INTERVAL 7 DAY) GROUP BY u.user_id这个SQL每日一题展示了如何用基础的SQL语法解决看似复杂的业务问题。关键在于理解日期差值法的核心思路这种模式可以扩展到各类连续行为分析中如连续付费、连续打卡等场景。