如何按时间序列分组统计连续相同ITEM_TYPE的记录数量？

现有原始数据：

ARRIVAL,ITEM_TYPE,ITEM
1,0,Cat
2,0,Dog
3,1,Horse
4,1,Cow
5,0,Fish
6,0,Barn
7,0,Potato

需要按ARRIVAL的时序，统计连续相同ITEM_TYPE的分组记录数，期望结果：

0,2
1,2
0,3

普通的COUNT()+GROUP BY ITEM_TYPE会将所有同类型记录合并统计（比如得到0,5），无法区分时序上的不同连续分组，可通过窗口函数实现需求，以下是适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）的解决方案：

实现步骤 #

1. 生成连续分组标识
   通过两次ROW_NUMBER()窗口函数的差值，标记出每个连续同类型分组的唯一ID：

SELECT 
    ARRIVAL,
    ITEM_TYPE,
    -- 全局时序编号 - 同类型内时序编号 = 连续分组ID
    ROW_NUMBER() OVER (ORDER BY ARRIVAL) - 
    ROW_NUMBER() OVER (PARTITION BY ITEM_TYPE ORDER BY ARRIVAL) AS group_id
FROM your_table_name;

连续相同ITEM_TYPE的记录会得到相同的group_id，类型切换时group_id会变化。

2. 统计每个分组的记录数
   基于上述结果，按ITEM_TYPE和group_id分组统计，并按分组的最早ARRIVAL排序保证时序正确：

SELECT 
    ITEM_TYPE,
    COUNT(*) AS group_count
FROM (
    SELECT 
        ITEM_TYPE,
        ROW_NUMBER() OVER (ORDER BY ARRIVAL) - 
        ROW_NUMBER() OVER (PARTITION BY ITEM_TYPE ORDER BY ARRIVAL) AS group_id
    FROM your_table_name
) AS grouped_data
GROUP BY ITEM_TYPE, group_id
ORDER BY MIN(ARRIVAL);

执行后即可得到目标结果：

ITEM_TYPE | group_count
----------|------------
0         | 2
1         | 2
0         | 3

原理说明 #

• 全局ROW_NUMBER()：给所有记录按到达顺序分配唯一递增编号；
• 同类型内ROW_NUMBER()：给每个ITEM_TYPE下的记录单独按到达顺序分配递增编号；
• 两者差值：连续同类型记录的两个编号同步递增，差值保持不变；当ITEM_TYPE切换时，差值会跳变，以此区分不同的连续分组。

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