在业务数据统计场景里，统计连续达标的天数是一个很常见的需求，比如统计生产设备连续符合质量标准的天数、用户连续完成打卡的天数等。使用ROW_NUMBER差值法可以快速实现这类统计需求，不需要复杂的循环逻辑，仅通过SQL语句就能完成。
ROW_NUMBER差值法原理
该方法的核心思路是给每条达标记录生成两个排序序号，通过两个序号的差值来识别连续的分组：
- 第一个序号是按照达标日期升序生成的全局排序序号，使用
ROW_NUMBER()函数实现 - 第二个序号是按照用户或设备维度，对达标日期升序生成的组内排序序号
- 如果两个序号的差值相同，说明这些记录属于同一段连续达标的周期
举个例子，用户A在1号、2号、3号、5号达标，那么全局排序是1、2、3、4，组内排序是1、2、3、4，差值分别是0、0、0、0？不对，这里修正下，全局排序是按所有达标记录排，组内是按用户A的达标记录排，假设用户A的达标日期是1、2、3、5，组内排序是1、2、3、4，全局排序如果是所有用户混排的话可能不是连续的，不过更常用的场景是单独对单个用户的达标记录处理，或者对用户分组后处理，此时全局排序可以换成日期排序，组内排序是用户内的日期排序，两者的差值相同则连续。
具体实现步骤
1. 准备测试数据
首先创建一张达标记录表，包含用户ID、达标日期、是否达标三个字段，插入测试数据：
-- 创建达标记录表
CREATE TABLE达标记录表 (
用户ID INT,
达标日期 DATE,
是否达标 INT -- 1表示达标，0表示未达标
);
-- 插入测试数据
INSERT INTO 达标记录表 VALUES
(1, '2024-01-01', 1),
(1, '2024-01-02', 1),
(1, '2024-01-03', 1),
(1, '2024-01-04', 0),
(1, '2024-01-05', 1),
(1, '2024-01-06', 1),
(2, '2024-01-01', 1),
(2, '2024-01-02', 0),
(2, '2024-01-03', 1),
(2, '2024-01-04', 1),
(2, '2024-01-05', 1);
2. 筛选达标记录并生成排序序号
首先筛选出所有达标的记录，然后分别生成日期排序序号和用户内日期排序序号：
SELECT
用户ID,
达标日期,
-- 按达标日期升序生成全局排序序号
ROW_NUMBER() OVER (ORDER BY 达标日期) AS 全局排序号,
-- 按用户ID分组，组内按达标日期升序生成排序序号
ROW_NUMBER() OVER (PARTITION BY 用户ID ORDER BY 达标日期) AS 组内排序号
FROM 达标记录表
WHERE 是否达标 = 1;
3. 计算差值并分组统计连续天数
用全局排序号减去组内排序号得到差值，相同差值的记录属于同一段连续达标周期，再对差值和用户ID分组统计天数：
WITH 达标排序数据 AS (
SELECT
用户ID,
达标日期,
ROW_NUMBER() OVER (ORDER BY 达标日期) AS 全局排序号,
ROW_NUMBER() OVER (PARTITION BY 用户ID ORDER BY 达标日期) AS 组内排序号
FROM 达标记录表
WHERE 是否达标 = 1
)
SELECT
用户ID,
差值,
MIN(达标日期) AS 连续达标起始日,
MAX(达标日期) AS 连续达标结束日,
COUNT(*) AS 连续达标天数
FROM (
SELECT
用户ID,
达标日期,
-- 计算两个排序号的差值
全局排序号 - 组内排序号 AS 差值
FROM 达标排序数据
) t
GROUP BY 用户ID, 差值
ORDER BY 用户ID, 连续达标起始日;
结果说明
上述查询的执行结果如下：
| 用户ID | 差值 | 连续达标起始日 | 连续达标结束日 | 连续达标天数 |
|---|---|---|---|---|
| 1 | 0 | 2024-01-01 | 2024-01-03 | 3 |
| 1 | 1 | 2024-01-05 | 2024-01-06 | 2 |
| 2 | 2 | 2024-01-01 | 2024-01-01 | 1 |
| 2 | 3 | 2024-01-03 | 2024-01-05 | 3 |
可以看到用户1有两次连续达标，分别是3天和2天，用户2有两次连续达标，分别是1天和3天，和测试数据的实际情况完全吻合。
方法注意事项
- 该方法要求达标日期是连续的，中间不能有缺失的达标日期，否则会被识别为不同的连续周期
- 如果同一个用户在同一天有多次达标记录，需要先按用户和日期去重，否则会影响排序序号的计算
- 如果数据库不支持
ROW_NUMBER()函数，可以使用自连接或者变量模拟实现排序逻辑，但性能会有所下降
这种方法的时间复杂度是O(n log n)，其中n是达标记录的总数，适合处理百万级以内的数据量，性能表现比较稳定。
ROW_NUMBER连续达标天数SQL查询差值法修改时间：2026-06-29 09:12:38