PostgreSQL如何计算最新连续成功/失败序列长度？

我来帮你搞定这个PostgreSQL的连续序列计算需求！你遇到的IGNORE NULLS不支持的问题确实很常见，不过我们可以用PostgreSQL原生支持的窗口函数技巧完美绕开它，下面分两种场景给出方案：

一、生成所有行的连续序列长度（匹配你给出的示例输出） #

这个方案会给每一行标记出它所在连续序列的长度，完全符合你提供的示例输出格式：

WITH grouped AS (
  SELECT 
    id, 
    success,
    -- 通过两个row_number的差值生成连续序列的组ID
    row_number() OVER (ORDER BY id DESC) 
    - row_number() OVER (PARTITION BY success ORDER BY id DESC) AS group_id
  FROM results
),
group_lengths AS (
  -- 计算每个连续组的长度
  SELECT 
    group_id, 
    success, 
    COUNT(*) AS length
  FROM grouped
  GROUP BY group_id, success
)
-- 关联回原数据，给每一行加上对应组的长度
SELECT 
  g.id, 
  g.success, 
  gl.length
FROM grouped g
JOIN group_lengths gl 
  ON g.group_id = gl.group_id AND g.success = gl.success
ORDER BY g.id DESC;

原理说明： #

• grouped CTE里的两个row_number()：第一个是按id倒序全局编号，第二个是按success分组后倒序编号。当success值连续相同时，两个编号的差值保持不变；一旦success值变化，差值会跳变，这样就把所有连续相同success的行分到了同一个组。
• group_lengths CTE统计每个组的总行数，也就是该连续序列的长度，最后关联回原数据即可得到每一行对应的序列长度。

二、仅获取最新的连续成功/失败序列长度（核心需求） #

如果只需要最新的连续成功序列和连续失败序列的长度，不需要所有行的标记，可以用更简洁的查询：

WITH latest_groups AS (
  SELECT 
    success,
    -- 标记每个行所在的连续组ID
    row_number() OVER (ORDER BY id DESC) 
    - row_number() OVER (PARTITION BY success ORDER BY id DESC) AS group_id,
    -- 计算当前组的长度
    COUNT(*) OVER (PARTITION BY success, 
      row_number() OVER (ORDER BY id DESC) 
      - row_number() OVER (PARTITION BY success ORDER BY id DESC)) AS length,
    -- 标记每个success类型下的组的新旧顺序（1是最新的组）
    ROW_NUMBER() OVER (PARTITION BY success ORDER BY id DESC) AS group_rank
  FROM results
)
-- 只取每个success类型下的最新组的长度
SELECT 
  success, 
  MAX(length) AS sequence_length
FROM latest_groups
WHERE group_rank = 1
GROUP BY success
ORDER BY success;

这个查询会直接返回类似这样的结果：

success | sequence_length
---------+-----------------
 false   | 2
 true    | 3

替代简化方案： #

如果你觉得嵌套窗口函数有点复杂，也可以用以下方式直接定位最新的连续组并统计：

WITH latest_change_points AS (
  -- 找到最新的success值变化的位置
  SELECT 
    id, 
    success,
    CASE WHEN lag(success) OVER (ORDER BY id DESC) != success THEN id END AS change_point
  FROM results
),
latest_groups_boundaries AS (
  -- 获取最新的两个连续组的起始ID
  SELECT 
    success,
    COALESCE(MAX(change_point), (SELECT MIN(id) FROM results)) AS start_id
  FROM latest_change_points
  GROUP BY success
)
-- 统计每个最新组的行数
SELECT 
  lgb.success, 
  COUNT(*) AS sequence_length
FROM results r
JOIN latest_groups_boundaries lgb 
  ON r.success = lgb.success AND r.id >= lgb.start_id
GROUP BY lgb.success;

为什么你的原查询有问题？ #

PostgreSQL在旧版本中确实不支持IGNORE NULLS作为last_value的修饰符（PostgreSQL 11+开始支持，但并不是所有环境都能升级），而我们上面的方案完全基于PostgreSQL原生支持的窗口函数特性，不需要依赖这个可选语法，兼容性更好。

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