Geek Answers Handbook

Sequential recording result count

I am working on a data structure with a list of positive or negative results for each person.

Sample data (id is an identifier):

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 calculate the maximum number consecutive result = 1for each person. The result of this sample will be

person  max_count
1       3
2       1

I 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 he gives me accumulativecount instead of consecutiveone.

What to do to make a sequential count? Any hint would be appreciated. thanks in advance

+4
source share
2 answers

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

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;


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

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
0

More articles:


All Articles