PostgreSQL表行分组实现优化：简化逻辑并提升查询性能

嘿，我仔细看了你的需求和现有查询，核心是要把无time_2的记录和后续对应val的有time_2记录配对分组，同时解决现有查询逻辑复杂、性能不佳以及val取值错误的问题对吧？

先分析现有方案的问题 #

你当前的查询用了多组窗口函数，还混合了time_1和time_2两种排序键，导致执行计划里出现了三次外部排序（磁盘IO开销很大，这就是执行时间长的主要原因）；而且CASE分支太多，不仅容易出错（比如val取错），后期维护也麻烦。

优化方案一：简化窗口函数+分组（兼容PostgreSQL 9.4+） #

这个方案先按val分组，用分割点标记组ID，再聚合所需字段，逻辑更清晰，排序开销更少：

WITH grouped AS (
  SELECT
    *,
    -- 按val分组，以有time_2的行为分割点生成组ID
    SUM(CASE WHEN time_2 IS NOT NULL THEN 1 ELSE 0 END) OVER (
      PARTITION BY val ORDER BY time_1 DESC
    ) AS group_id,
    -- 标记组内最靠后的记录（time_1最晚）
    ROW_NUMBER() OVER (PARTITION BY val, group_id ORDER BY time_1 DESC) AS rn
  FROM foo_table
  WHERE bar_id = 'bar'
)
SELECT
  -- 取组内time_1最晚的记录ID
  LAST_VALUE(id) OVER (PARTITION BY val, group_id ORDER BY time_1) AS id,
  -- 取组内最早的time_1
  MIN(time_1) OVER (PARTITION BY val, group_id) AS time_1,
  -- 取组内的time_2（最多一条有值的记录）
  MAX(time_2) OVER (PARTITION BY val, group_id) AS time_2,
  val
FROM grouped
WHERE rn = 1
ORDER BY time_1 DESC;

如果你的PostgreSQL版本是13+，可以用QUALIFY替代子查询里的rn过滤，代码更简洁：

WITH grouped AS (
  SELECT
    *,
    SUM(CASE WHEN time_2 IS NOT NULL THEN 1 ELSE 0 END) OVER (
      PARTITION BY val ORDER BY time_1 DESC
    ) AS group_id
  FROM foo_table
  WHERE bar_id = 'bar'
)
SELECT
  LAST_VALUE(id) OVER (PARTITION BY val, group_id ORDER BY time_1) AS id,
  MIN(time_1) OVER (PARTITION BY val, group_id) AS time_1,
  MAX(time_2) OVER (PARTITION BY val, group_id) AS time_2,
  val
FROM grouped
QUALIFY ROW_NUMBER() OVER (PARTITION BY val, group_id ORDER BY time_1 DESC) = 1
ORDER BY time_1 DESC;

优化方案二：用MATCH_RECOGNIZE实现模式匹配（PostgreSQL 11+） #

如果你的PostgreSQL版本支持，这个方案是最直观的，专门用于这种“匹配连续记录模式”的场景，几乎不需要额外的逻辑：

SELECT *
FROM foo_table
WHERE bar_id = 'bar'
MATCH_RECOGNIZE (
  PARTITION BY val
  ORDER BY time_1 DESC
  MEASURES
    LAST(A.id) AS id,
    MIN(A.time_1) AS time_1,
    COALESCE(B.time_2, NULL) AS time_2,
    A.val AS val
  PATTERN (A* B?)
  DEFINE
    A AS time_2 IS NULL,
    B AS time_2 IS NOT NULL
)
ORDER BY time_1 DESC;

这里的逻辑很直白：

• 按val分组，按time_1倒序处理记录
• 匹配**0或多个无time_2的行（A）+ 可选的1个有time_2的行（B）**的模式
• 提取每个匹配组的最后一个A的ID、所有A的最早time_1、B的time_2（如果存在），以及对应的val

性能提升的关键优化 #

1. 调整索引：现有索引foo_table_bar_id_idx是hash索引，对排序和分区查询支持差，建议创建复合BTREE索引：

CREATE INDEX idx_foo_bar_val_time1 ON foo_table (bar_id, val, time_1 DESC);

这个索引可以直接满足WHERE bar_id = 'bar'的过滤，以及后续PARTITION BY val和ORDER BY time_1 DESC的需求，彻底避免执行计划里的外部排序。

2. 去掉冗余排序：优化后的方案只在一个窗口里按val和time_1排序，相比原查询的多次排序，磁盘IO和CPU开销会大幅降低。

解决val取值错误的问题 #

原查询中val取错，是因为你用了LEAD(val) OVER (ORDER BY time_2)，这会让val的取值和time_2的排序绑定，而不是按val分组。优化后的方案都基于PARTITION BY val，确保每个组的val是正确的。

备注：内容来源于stack exchange，提问作者mikemoikku