时间戳

首页 > 解决方案 > 计算每个客户和值的连续行数

sql - 计算每个客户和值的连续行数

问题描述

我有下表：

create table tickets (id int, customer text, status smallint);

insert into tickets values
(1, 'A', 0),
(2, 'A', 0),
(3, 'B', 0),
(4, 'B', 0),
(5, 'A', 0),
(6, 'B', 1),
(7, 'C', 0),
(8, 'B', 0),
(9, 'A', 0),
(10, 'B', 0),
(11, 'A', 1),
(12, 'A', 1),
(13, 'A', 1);

我试图根据这个线程找出我的案例：

统计表中值的连续出现次数

但我能做到的最好的就是这个，这还不是我的预期输出

select customer, status, count(*) as cnt
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by customer, status order by id)
             ) as grp
      from tickets t
     ) x
group by grp, customer, status
order by max(id)

DBFiddle

预期输出将是：（每个客户的每个状态的连续记录）

+-----------+-------+-----+
| customer  | status| cnt |
+-----------+-------+-----+
|  A        | 0     |   4 |
|  A        | 1     |   3 |
|  B        | 0     |   2 |
|  B        | 1     |   1 |
|  B        | 0     |   2 |
|  C        | 0     |   1 |

标签： sqlpostgresqlrow-number

解决方案

对于间隙和孤岛解决方案，对于第一个 row_number，您需要按客户分区。

SELECT customer,    status, COUNT(*) FROM (
select t.*,
             (row_number() over (partition by customer order by id) -
              row_number() over (partition by customer, status order by id)
             ) as grp
      from tickets t
) X
GROUP BY customer,  status, grp
ORDER BY customer,  max(id)

小提琴手

结果：

customer    status  count
--------    ------  -----
A           0       4
A           1       3
B           0       2
B           1       1
B           0       2
C           0       1

推荐阅读