PostgreSQL：如何筛选分组中当前值组合的首次更新行

针对你的需求——获取每个group中当前foo/bar组合首次开始持续未变化的行（而非历史首次出现），可以通过PostgreSQL的窗口函数高效实现，无需程序遍历。

核心思路 #

通过窗口函数标记连续相同foo/bar组合的分组，找到每个group最新的连续分组，再取该分组的第一行（即持续起始点）。

具体SQL实现 #

WITH ranked_rows AS (
  SELECT
    *,
    -- 标记连续相同foo/bar的分组：当当前行与上一行值不同时，新建分组
    SUM(CASE WHEN (foo, bar) = LAG((foo, bar)) OVER (PARTITION BY "group" ORDER BY timestamp) THEN 0 ELSE 1 END)
      OVER (PARTITION BY "group" ORDER BY timestamp) AS continuous_group
  FROM foobar
),
latest_continuous_groups AS (
  -- 获取每个group最新的连续分组ID
  SELECT "group", MAX(continuous_group) AS current_group
  FROM ranked_rows
  GROUP BY "group"
)
-- 从最新连续分组中取最早的一行（即持续起始点）
SELECT DISTINCT ON (rr."group") rr.*
FROM ranked_rows rr
JOIN latest_continuous_groups lcg ON rr."group" = lcg."group" AND rr.continuous_group = lcg.current_group
ORDER BY rr."group", rr.timestamp ASC;

针对示例数据的执行结果 #

对于你提供的示例数据，该查询会返回：

+------+---------+-------+-------+-------------+------------------+
| "id" | "group" | "foo" | "bar" | "timestamp" | "continuous_group" |
+------+---------+-------+-------+-------------+------------------+
| 3    | 1       | 10    | 20    | 3           | 3                |
+------+---------+-------+-------+-------------+------------------+

这正是(10,20)组合开始持续未变化的起始行。

优化建议 #

为提升查询效率，建议创建复合索引：

CREATE INDEX idx_foobar_group_timestamp ON foobar ("group", timestamp);

该索引会加速窗口函数中的分组排序操作，尤其在数据量较大时效果明显。

特殊情况处理 #

如果某个group所有行的foo/bar组合完全相同，该查询会自动返回该分组的第一行（即首次出现的行，因为全程未变化），符合需求逻辑。

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