Consecutive Count on Record Result

Question

I'm working on a data structure with list of positive or negative result for each person.

Sample data (id is an identity):

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

With this I would like to count the maximum number of consecutive result = 1 for each person. The result in this sample would be

person  max_count
1       3
2       1

I have tried using ROW_NUMBER() OVER (PARTITION BY) like this

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

but it gives me an accumulative count instead of consecutive one.

What should I do to perform a consecutive count? Any hint would be appreciated. Thanks in advance

Hint: `gaps-and-islands`. What is the version of SQL Server? I think I can write the final query, but one thing is unclear. Why `max_count` for person `1` is `2`? Based on your sample it should be `3`, no? Rows with IDs: `2,3,6`. Rows `4,5` are for different person, I think they should be ignored, shouldn't they? Or, are you interested in a strict "consecutive" sequence, where IDs **must** be in a sequence without any gaps? If you add few more rows to your sample that explain different variants, it would help to understand what you need. — Vladimir Baranov, Mar 01 '16 at 03:17
@VladimirBaranov you're right. I've made a mistake on the question. Will correct that now. — ydoow, Mar 01 '16 at 03:26

score 3 · Accepted Answer · answered Mar 01 '16 at 03:43

This looks like classic gaps-and-islands problem. Examine intermediate results of each CTE in the query below to understand what is going on.

Sample data

I added person 3 with two sequences of positive results, so that we could find the longest sequence.

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);

Query

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;

Result

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

In your sample data, person 1 has only 2 consecutive positive result but in your query result, it says 3? — Kim, Mar 01 '16 at 03:50
@Kim, The query allows to have gaps in IDs when determining the sequence. For person `1` the rows with IDs `2,3,6` form a sequence of rows with `result=1`. — Vladimir Baranov, Mar 01 '16 at 03:57
I see... didn't notice the edit he made on the question. I was still looking at the result where P1 = 2. — Kim, Mar 01 '16 at 04:05
@VladimirBaranov Brilliant! Thanks man. The use of GroupNumber did the trick. — ydoow, Mar 01 '16 at 23:35

score 0 · Answer 2 · answered Mar 01 '16 at 05:27

0

by using Case and SUM we can achieve the above result

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

answered Mar 01 '16 at 05:27

mohan111

8,633
4
28
55

2

That doesn't fulfil the consecutive requirement. Eg, for person 1, if there is 0 between 1s. That would return sum of 1s without taking the zero into consideration. – ydoow Mar 01 '16 at 22:37

Consecutive Count on Record Result

2 Answers2