PostgreSQL中ROW_NUMBER窗口函数按连续状态重置计数问题咨询

安全阿岳

2026-9-27

PostgreSQL 实现状态变化时重置行号的方案

你当前的写法将status直接放入窗口函数的分区键中，会将同一dept_id、name下所有相同status的行划入同一个分区，因此行号会全局累计，而非按连续段计数。该需求属于典型的连续状态孤岛分组场景，可通过状态变化标记+分组累加的方式实现，具体步骤如下：

注意：判断连续状态必须存在一个能明确行先后顺序的全局排序字段（如自增主键、记录创建时间、业务流水号等），以下示例假设该字段为sort_id，请替换为你的实际排序字段。

实现代码

WITH status_mark AS (
  SELECT
    *,
    -- 对比当前行与上一行的status，不一致则标记为1，否则为0
    CASE WHEN status = LAG(status, 1, '') OVER (PARTITION BY dept_id, name ORDER BY sort_id)
         THEN 0 ELSE 1 END AS is_changed
  FROM your_table_name
),
status_grp AS (
  SELECT
    *,
    -- 累加标记得到连续相同status的组ID
    SUM(is_changed) OVER (PARTITION BY dept_id, name ORDER BY sort_id) AS status_group
  FROM status_mark
)
SELECT
  *,
  -- 每个组内单独生成行号
  ROW_NUMBER() OVER (PARTITION BY dept_id, name, status_group ORDER BY sort_id) AS row_num
FROM status_grp;

你也可以将多层CTE合并简化为单步查询：

SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY dept_id, name, 
    SUM(CASE WHEN status = LAG(status, 1, '') OVER (PARTITION BY dept_id, name ORDER BY sort_id) THEN 0 ELSE 1 END) 
      OVER (PARTITION BY dept_id, name ORDER BY sort_id)
    ORDER BY sort_id
  ) AS row_num
FROM your_table_name;