连续记录结果

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 

有了这个，我想算consecutiveresult = 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 

尝试，但它给了我一个accumulative计数，而不是consecutive之一。

我应该怎么做连续计数？任何暗示将不胜感激。在此先感谢

Answer 1

这看起来像经典gaps-and-islands问题。 在下面的查询中检查每个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 | 
+--------+-----------+ 

Answer 2

使用案例和SUM就可以实现上述结果

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) 
    select 
    person, 
    SUM(CASE WHEN RESULT = 1 then 1 else 0 END) 
    from @T 
    GROUP BY person