如何统计连续行中事件的出现次数？修正row_number()计数问题

问题描述 #

原始数据：

ID           Date            Event
  ----------------------------------------
  123        2022-05-01         OCT
  123        2022-05-04         OCT
  123        2022-05-05         OCT
  123        2022-05-07         OCT
  123        2022-05-08         GRE
  123        2022-05-10         GRE
  123        2022-05-12         OCT
  123        2022-05-15         OCT

期望输出：

ID           Date            Event         Order_Event
  --------------------------------------------------------
  123        2022-05-01         OCT               1
  123        2022-05-04         OCT               2
  123        2022-05-05         OCT               3
  123        2022-05-07         OCT               4
  123        2022-05-08         GRE               1
  123        2022-05-10         GRE               2
  123        2022-05-12         OCT               1  
  123        2022-05-15         OCT               2

错误尝试：直接使用ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Date)会对全局相同事件累计计数，导致后续出现的OCT被延续之前的序号，不符合连续分组计数的需求。

解决方案 #

这是典型的岛屿和缺口问题，需先将连续相同的事件划分为独立分组，再在组内生成连续计数，具体步骤如下：

示例SQL代码（适配MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH event_groups AS (
    SELECT 
        ID,
        Date,
        Event,
        -- 当当前事件与上一行不同时标记为1，否则为0，累计求和生成分组ID
        SUM(CASE WHEN Event = LAG(Event) OVER (PARTITION BY ID ORDER BY Date) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY Date) AS group_id
    FROM your_table_name
)
SELECT 
    ID,
    Date,
    Event,
    ROW_NUMBER() OVER (PARTITION BY ID, group_id ORDER BY Date) AS Order_Event
FROM event_groups
ORDER BY Date;

代码说明 #

1. LAG(Event) OVER (PARTITION BY ID ORDER BY Date)：按ID分组、日期排序，获取当前行的上一行事件值，用于判断事件是否连续。
2. SUM(...) OVER (...)：对分组标记累计求和，每次事件变化时分组ID递增，将连续相同事件归为同一组。
3. 最后在ID和group_id的分组内，用ROW_NUMBER()按日期排序，生成每组内的连续计数。

验证结果 #

执行上述SQL后，会得到符合预期的输出：连续的OCT被分为两个独立组分别从1开始计数，GRE组也独立生成连续序号。

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