CODEWITHSUNDEEP
postgresqlgaps-and-islands
Vanessa Zorgia
Vanessa Zorgia

Reputation: 13

count consecutive occurrences of discrete value

I have below table and I want to count the consecutive days for each name and each flag.

Name        Date            Flag 
-------------------------------------
Alberta    01-01-2018       B

Alberta    02-01-2018       B

Alberta    03-01-2018       B

Alberta    04-01-2018       L

Alberta    05-01-2018       L

Ambelos    01-01-2018       B

Ambelos    02-01-2018       B

Ambelos    03-01-2018       L

Ambelos    04-01-2018       B

I want the below 

Name          Date            Flag     CountDays 
----------------------------------------------------
Alberta      03-01-2018        B          3

Alberta      05-01-2018        L          2

Ambelos      02-01-2018        B          2

Ambelos      03-01-2018        L          1

Ambelos      04-01-2018        B          1

How can I do this?

Upvotes: 1

Views: 1662

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You could use TABIBITOSAN technique

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE t
    (Name varchar(7), dt timestamp, Flag varchar(1))
;

INSERT INTO t
    (Name, dt, Flag)
VALUES
    ('Alberta', '2018-01-01 00:00:00', 'B'),
    ('Alberta', '2018-02-01 00:00:00', 'B'),
    ('Alberta', '2018-03-01 00:00:00', 'B'),
    ('Alberta', '2018-04-01 00:00:00', 'L'),
    ('Alberta', '2018-05-01 00:00:00', 'L'),
    ('Ambelos', '2018-01-01 00:00:00', 'B'),
    ('Ambelos', '2018-02-01 00:00:00', 'B'),
    ('Ambelos', '2018-03-01 00:00:00', 'L'),
    ('Ambelos', '2018-04-01 00:00:00', 'B')
;

Query 1:

SELECT name, 
       Max(dt) AS "Date", 
       flag, 
       Count(*) As CountDays
FROM   (SELECT t.*, 
               row_number() 
                 over ( 
                   PARTITION BY name 
                   ORDER BY dt ) - row_number() 
                                     over ( 
                                       PARTITION BY name, flag 
                                       ORDER BY dt ) AS seq 
        FROM   t) s 
GROUP  BY name, 
          flag, 
          seq 
ORDER  BY name,"Date"

Results:

|    name |                 Date | flag | countdays |
|---------|----------------------|------|-----------|
| Alberta | 2018-03-01T00:00:00Z |    B |         3 |
| Alberta | 2018-05-01T00:00:00Z |    L |         2 |
| Ambelos | 2018-02-01T00:00:00Z |    B |         2 |
| Ambelos | 2018-03-01T00:00:00Z |    L |         1 |
| Ambelos | 2018-04-01T00:00:00Z |    B |         1 |

Upvotes: 4

Related Questions