SQL Server 2019中统计连续测试失败次数的查询需求

需求说明 #

• 使用SQL Server 2019标准版，统计每个mooniq在9个series区间内连续失败的最大次数（testresult=1表示失败）
• 连续定义：失败必须对应连续的series值，非连续分散的失败不计入统计（例如mooniq=24904虽有3次失败，但均不连续，最终统计为0）

测试数据表结构及数据 #

DROP TABLE IF EXISTS #tmpTable

CREATE TABLE #tmpTable 
(
    mooniq int,
    testresult int,
    series bigint
)

INSERT INTO #tmpTable (mooniq, testresult, series) 
VALUES
(74904, NULL, 1),
(74904, NULL, 2),
(74904, NULL, 3),
(74904, NULL, 4),
(74904, 1, 5),
(74904, 1, 6),
(74904, 1, 7),
(74904, NULL, 8),
(74904, NULL, 9),
(94904, NULL, 1),
(94904, NULL, 2),
(94904, NULL, 3),
(94904, NULL, 4),
(94904, 1, 5),
(94904, 1, 6),
(94904, 1, 7),
(94904, 1, 8),
(94904, 1, 9),
(24904, NULL, 1),
(24904, NULL, 2),
(24904, NULL, 3),
(24904, NULL, 4),
(24904, 1, 5),
(24904, NULL, 6),
(24904, 1, 7),
(24904, NULL, 8),
(24904, 1, 9);

预期输出结果 #

mooniq  contiqs
------  -------
24904   0  
74904   3
94904   5

已尝试的代码 #

--简化后的业务问题尝试代码
with grouped_moons as (
 select mooniq, 
          testresult,
          dense_rank() over (order BY mooniq,series) drom ,
          dense_rank() over (partition by testresult order by mooniq, series)  DRPQ 
                  from #tmpTable t)
select * from grouped_moons order by mooniq

解决方案 #

采用**分组岛屿（Island and Gap）**的思路，识别每个mooniq下连续的失败段，再统计最长连续长度：

WITH FailedRecords AS (
    -- 筛选出所有失败记录（testresult=1）
    SELECT mooniq, series
    FROM #tmpTable
    WHERE testresult = 1
),
FailedGroups AS (
    -- 为每个连续失败段标记分组ID：连续的series会得到相同的group_id
    SELECT 
        mooniq,
        series,
        series - ROW_NUMBER() OVER (PARTITION BY mooniq ORDER BY series) AS group_id
    FROM FailedRecords
),
GroupLengths AS (
    -- 统计每个连续失败段的长度
    SELECT 
        mooniq,
        COUNT(*) AS continuous_count
    FROM FailedGroups
    GROUP BY mooniq, group_id
)
-- 对每个mooniq取最长连续失败次数，无连续则返回0
SELECT 
    m.mooniq,
    ISNULL(MAX(gl.continuous_count), 0) AS contiqs
FROM (SELECT DISTINCT mooniq FROM #tmpTable) m
LEFT JOIN GroupLengths gl ON m.mooniq = gl.mooniq
GROUP BY m.mooniq
ORDER BY m.mooniq;

代码说明 #

1. FailedRecords：只保留失败的记录，排除testresult=NULL的情况
2. FailedGroups：通过series - 行号的方式生成分组ID，连续的series计算后结果相同，以此区分不同的连续失败段
3. GroupLengths：按mooniq和分组ID统计每个连续段的失败次数
4. 最后通过左连接关联所有mooniq，用ISNULL处理无连续失败的情况，返回0

内容的提问来源于stack exchange，提问作者Mike G