Presto SQL中基于Stage C分隔的row_number窗口函数实现问题

现有数据表包含object_id、event_time、event_type、event_subtype、stage字段，需按object_id、event_time排序，新增row_number列：同一object_id下，初始行号为1，每当经过一次stage=C的行后，后续所有行的行号递增1。此前尝试row_number() over (partition by object_id, stage order by event_time)无法满足需求，寻求正确的Presto SQL窗口函数写法。

样例输入表 #

预期输出表 #

解决方案 #

使用累计求和窗口函数统计stage=C的前置出现次数，以此计算行号：

SELECT
  object_id,
  event_time,
  event_type,
  event_subtype,
  stage,
  COALESCE(
    SUM(CASE WHEN stage = 'C' THEN 1 ELSE 0 END) OVER (
      PARTITION BY object_id 
      ORDER BY event_time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ),
    0
  ) + 1 AS row_number
FROM your_table
ORDER BY object_id, event_time;

逻辑说明 #

1. 分区与排序：按object_id分组，确保仅在同一对象内计算；按event_time排序，保证时间顺序正确。
2. 前置C的累计统计：SUM(CASE WHEN stage = 'C' THEN 1 ELSE 0 END)统计当前行之前所有行中stage=C的次数，ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定窗口范围为当前行的前置所有行，避免将当前行的C计入统计。
3. 处理边界情况：用COALESCE将分组第一行的null结果转为0（第一行无前置行，sum返回null）。
4. 计算行号：前置C的累计次数加1，得到初始为1，每经过一次C后行号递增的效果。

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