PostgreSQL 13：如何在SELECT语句中基于时间戳生成递增event_id

可以通过嵌套窗口函数实现需求，核心思路是先识别每个"新出现"的event_id分组，再对这些分组进行累计计数：

最终SQL语句 #

SELECT
  event_id,
  timestamp,
  SUM(new_group_flag) OVER (ORDER BY timestamp) AS sequence_num
FROM (
  SELECT
    event_id,
    timestamp,
    -- 标记当前行是否为新分组：与前一行event_id不同则标记为1
    CASE
      WHEN LAG(event_id) OVER (ORDER BY timestamp) != event_id THEN 1
      ELSE 0
    END AS new_group_flag
  FROM your_table_name -- 替换为你的实际表名
) AS grouped_events
ORDER BY timestamp;

逻辑说明 #

1. 子查询中使用LAG()窗口函数，按timestamp排序后获取前一行的event_id，与当前行对比：
   • 若两者不同，说明当前行是一个新的分组起点，标记为1
   • 若相同，标记为0
2. 外层查询使用SUM() OVER (ORDER BY timestamp)对标记值进行累加：
   • 每次遇到新分组标记（值为1）时，序号自动递增
   • 连续相同的event_id会共享同一个序号，后续再次出现的旧event_id会触发新的序号

示例验证 #

假设你的表数据如下：

执行SQL后得到结果：

完全符合要求的规则：连续相同event_id序号一致，再次出现的旧event_id生成新的递增序号。

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