sql - 连续计数记录结果

Question

我正在研究一种数据结构，其中包含每个人的积极或消极结果列表。

示例数据(id是身份):

id      person  result
1       1       0
2       1       1
3       1       1
4       2       1
5       2       0
6       1       1
7       1       0
8       2       0
9       2       0
10      2       0

通过这个，我想计算每个人的连续 结果 = 1 的最大数量。此示例中的结果将是

person  max_count
1       3
2       1

我尝试过像这样使用ROW_NUMBER() OVER (PARTITION BY)

SELECT person, 
ROW_NUMBER() OVER (PARTITION BY person, result ORDER BY id) AS max_count      
FROM TABLE 

但它给了我一个累计计数，而不是连续计数。

我应该怎样做才能进行连续计数？任何提示将不胜感激。提前致谢

Answer 1

这看起来像经典的间隙与岛屿问题。检查下面查询中每个 CTE 的中间结果以了解发生了什么。

示例数据

我添加了具有两个阳性结果序列的人3，以便我们可以找到最长的序列。

DECLARE @T TABLE (id int, person int, result int);
INSERT INTO @T (id, person, result) VALUES
(1 , 1, 0),
(2 , 1, 1),
(3 , 1, 1),
(4 , 2, 1),
(5 , 2, 0),
(6 , 1, 1),
(7 , 1, 0),
(8 , 2, 0),
(9 , 2, 0),
(10, 2, 0),
(11, 3, 0),
(12, 3, 1),
(13, 3, 1),
(14, 3, 1),
(15, 3, 1),
(16, 3, 0),
(17, 3, 1),
(18, 3, 1),
(19, 3, 0),
(20, 3, 0);

查询

WITH
CTE_RowNumbers
AS
(
    SELECT
        id, person, result
        ,ROW_NUMBER() OVER (PARTITION BY person ORDER BY ID) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY person, result ORDER BY ID) AS rn2
    FROM @T
)
,CTE_Groups
AS
(
    SELECT
        id, person, result
        ,rn1-rn2 AS GroupNumber
    FROM CTE_RowNumbers
)
,CTE_GroupSizes
AS
(
    SELECT
        person
        ,COUNT(*) AS GroupSize
    FROM CTE_Groups
    WHERE
        result = 1
    GROUP BY
        person
        ,GroupNumber
)
SELECT
    person
    ,MAX(GroupSize) AS max_count
FROM CTE_GroupSizes
GROUP BY person
ORDER BY person;

结果

+--------+-----------+
| person | max_count |
+--------+-----------+
|      1 |         3 |
|      2 |         1 |
|      3 |         4 |
+--------+-----------+