直接统计“过去7天每天都活跃”的用户需用窗口函数验证日期连续性:先对user_id和event_date去重排序,再用date-INTERVAL'1 day'*ROW_NUMBER()构造连续段标识,确保MIN(event_date)=CURRENT_DATE-INTERVAL'6 days'且COUNT=7,并落在指定区间内。

用窗口函数识别连续登录天数
直接统计“过去7天每天都活跃”的用户,关键不是算总次数,而是验证日期是否连续且覆盖完整7天。最可靠的方式是先对每个用户按日期去重、排序,再用 LAG 或 ROW_NUMBER() 配合日期差计算连续段。比如 PostgreSQL 或 MySQL 8.0+ 中,可以这样构造连续标识:date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date) —— 同一连续段内该表达式结果相同,不同段则不同。
实操时注意三点:
- 必须先对 user_id 和 event_date 做 DISTINCT,否则重复行为会干扰连续性判断
- 日期字段需为 DATE 类型(非 TIMESTAMP),或显式 CAST(event_time AS DATE)
- ROW_NUMBER() 的 ORDER BY 必须严格按日期升序,不能加其他字段打乱顺序
筛选恰好覆盖最近7天的连续段
光有连续段还不够,得确认这个连续段是否“从今天往前正好7天”。假设今天是 '2024-06-15',那目标区间就是 '2024-06-09' 到 '2024-06-15'(含)。不能只查 MIN(event_date) = '2024-06-09' 且 COUNT(DISTINCT event_date) = 7,因为这无法排除中间缺某天、但首尾凑巧对上的情况。
稳妥做法是:在已分组的连续段中,加两个条件:
- MIN(event_date) = CURRENT_DATE - INTERVAL '6 days'(注意是减6天,不是7天)
- COUNT(DISTINCT event_date) = 7
- 同时确保所有 event_date 都落在 [CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE] 范围内(防止段更长但只取部分)
MySQL 5.7 等不支持窗口函数的场景
如果数据库版本老旧(如 MySQL 5.7、早期 SQL Server),ROW_NUMBER() 不可用,就得用变量模拟。典型写法是声明 @prev_user 和 @prev_date 变量,在子查询中逐行比对上一行的 user_id 和日期是否为前一天。
但必须注意:
- 变量赋值顺序依赖 ORDER BY 严格生效,必须显式 ORDER BY user_id, event_date
- 每次查询前要 SET @prev_user = NULL, @prev_date = NULL,否则变量状态残留会导致错误
- 这种方式在并发查询或复杂 JOIN 下极不稳定,仅建议用于离线统计脚本,勿用于高并发接口
性能与索引关键点
这类查询容易慢,尤其当行为表达千万级。核心优化不在 SQL 写法,而在索引设计:
- 必须有复合索引:INDEX (user_id, event_date),顺序不能颠倒
- 如果只查最近7天,可在 WHERE 中加 event_date >= CURRENT_DATE - INTERVAL '6 days' 提前过滤,避免全表扫描
- 避免在 event_date 上用函数(如 DATE(event_time)),否则索引失效;应提前物化为 DATE 字段或建函数索引(MySQL 8.0.13+ 支持)
连续性判断本身逻辑简单,但日期边界、版本兼容、索引失效这三处最容易出错——尤其是把“过去7天”误解为“最近7条记录”,或者漏掉去重导致单日多次行为被误判为多日。

















