PostgreSQL如何统计每个客户对应各状态的连续行数量

问题原因 #

你原有SQL的逻辑错误出在分组标识grp的计算上：第一个row_number()用了全局排序，没有按客户维度分区，导致不同客户的行计算出的grp会出现重复，不属于同一个连续段的行被错误分到同一组。

正确实现 #

你只需要在第一个窗口函数中增加partition by customer，即可得到符合预期的结果：

select customer, status, count(*) as cnt
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, min(id);

另一种更直观的实现（基于状态变化标记）

如果想要更便于理解的逻辑，可以通过判断相邻行的状态是否变化来生成分组标识：

select customer, status, count(*) as cnt
from (
    select t.*,
        -- 状态变化时分组标识+1
        sum(case when status = prev_status then 0 else 1 end) over (partition by customer order by id) as grp
    from (
        select t.*,
            -- 取同客户上一行的状态
            lag(status) over (partition by customer order by id) as prev_status
        from tickets t
    ) t1
) t2
group by customer, status, grp
order by customer, min(id);

输出结果 #

两种写法执行后都会得到你预期的输出：

内容的提问来源于stack exchange，提问作者sh4rkyy