postgresql - 计算离散值的连续出现次数

Question

我有下表，我想计算每个名称和每个标志的连续天数。

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

我想要下面的

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

我怎样才能做到这一点？

标签： postgresqlgaps-and-islands

Answer 1

你可以使用TABIBITOSAN技术

SQL小提琴

PostgreSQL 9.6 架构设置：

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

查询 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" 

结果：

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