SQL如何统计分组内连续出现的次数_使用窗口函数判断连续性-SQL-PHP中文网

用 ROW_NUMBER() 生成逻辑序号并与真实字段作差构造连续组标识（grp），相同差值即属同一连续段，再按 grp 分组统计；必须显式 ORDER BY 确保业务顺序，且对首行 NULL 妥善处理。

sql如何统计分组内连续出现的次数_使用窗口函数判断连续性

用 `LAG()` 或 `ROW_NUMBER()` 识别连续段

连续性判断的本质是：同一分组内，某字段（如日期、序号）的值是否构成等差序列。直接比前后行值差是否为1最直观，但需先按业务顺序排序，再用 LAG() 取上一行值做减法。更稳健的做法是用 ROW_NUMBER() 生成逻辑序号，再和真实字段做差——若真实字段本身连续，差值恒定；一旦中断，差值突变，从而划分出连续块。

常见错误是忽略分组内排序：没加 ORDER BY 的窗口函数结果不可控，尤其在多列分组或存在重复时间戳时，LAG() 可能拉到错误的“上一行”。

必须显式写 ORDER BY，且排序字段要能唯一确定业务顺序（例如 event_time + id）
对日期字段做连续判断时，优先转成整数天（如 TO_DAYS(event_date)），避免 DATE_SUB() 在跨月时出错
若原始数据有重复值（如同一秒多个事件），先去重或加辅助排序字段，否则 LAG() 可能跳过实际相邻记录

用差值分组（`grp`）聚合连续段

核心技巧是构造一个“连续组标识”字段：对每个分组，计算 real_value - ROW_NUMBER() OVER(...)，相同差值即属同一连续段。这个 grp 可作为二次分组键，再用 COUNT(*) 统计每段长度。

示例场景：统计每个用户连续登录天数

SELECT user_id, MIN(login_date) AS start_date, COUNT(*) AS streak_days
FROM (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ) DAY) AS grp
  FROM user_logins
) t
GROUP BY user_id, grp;

注意：DATE_SUB(... INTERVAL N DAY) 是 MySQL 写法；PostgreSQL 需用 login_date - (ROW_NUMBER() OVER (...) || ' days')::interval；SQLite 用 login_date - (ROW_NUMBER() OVER (...) - 1)（假设日期存为 julian day）。

差值计算必须在同一窗口定义中完成，不能拆成两个子查询再 JOIN，否则窗口函数作用域失效
若连续依据是 ID 而非时间，且 ID 为 BIGINT，确保减法不溢出（极少发生，但某些旧版 Hive 有隐式类型转换问题）
该方法无法区分“间隔1天”和“间隔2天”，只识别严格连续（差=1）。如需检测间隔≤2的松散连续，得改用 LAG() 多次比较并标记状态

处理边界情况：空值、跳跃起点、单元素段

LAG() 遇到分组首行返回 NULL，直接参与减法会导致整行 grp 为 NULL，进而让单日登录者被漏掉或归入错误组。必须用 COALESCE() 或条件表达式兜底。

常见错误现象：统计结果少了几条，尤其是每个分组的第一条记录消失；或者所有单次行为都被合并成一个大组。

用 LAG() 判断时，写成 login_date = LAG(login_date) OVER(...) + INTERVAL 1 DAY，首行因 LAG() 为 NULL 导致布尔值为 UNKNOWN，被 WHERE 过滤掉——应改用 OR LAG(...) IS NULL 显式包含起点
构造 grp 时，若用 real_value - ROW_NUMBER()，首行差值就是 real_value - 1，天然可区分，无需额外处理，这是它比 LAG() 更鲁棒的地方
单元素段（如某用户只登录1天）会形成长度为1的连续段，这是合理结果，不是 bug；若业务要求忽略，后续加 HAVING COUNT(*) > 1

性能与兼容性提醒

窗口函数在大数据量下性能取决于排序开销。连续性分析必然需要全量排序，无法走索引跳过；但可以提前用 WHERE 过滤无关分组（如限定最近30天），减少窗口输入行数。

MySQL 8.0+、PostgreSQL 8.4+、SQL Server 2005+、Oracle 8i+ 均支持标准窗口语法，但 SQLite 直到 3.25.0 才支持，旧版本只能用自连接模拟，性能极差。

别在 PARTITION BY 中放高基数字段（如用户ID超千万），可能导致内存溢出；考虑先按时间分区再聚合
某些 OLAP 引擎（如 Doris、StarRocks）对 ROW_NUMBER() 优化较好，但 LAG() 跨多行时可能触发 spill-to-disk，留意执行计划里的 Warning
如果只是查“当前最长连续天数”，不必物化所有段，可用变量或递归 CTE 逐行扫描，但可读性和维护性下降明显

真正麻烦的是“连续N天中任意M天”的模糊连续，那已经超出窗口函数单次扫描能力，得转成事件序列建模或借助图算法。