如何使用PostgreSQL窗口子句按终端值合并有序行时间区间？

我有一张foo表，数据如下：

some_field的取值为[A,B,C,X,Y,Z]，其中[A,B,C]代表开启或持续事件，[X,Y,Z]代表关闭事件。需要按some_fk分区，获取每个时间区间的首个开启事件时间和对应关闭事件时间（未终止的时间区间结束值为NULL），期望结果如下：

注：未终止的时间区间结束值为NULL

我当前的解决方案使用了3个公共表表达式（CTE）：

WITH ranked AS (
  SELECT
    RANK() OVER (PARTITION BY some_fk ORDER BY some_date_field) AS "rank",
    some_fk,
    some_field,
    some_date_field
  FROM foo    
), openers AS (
  SELECT * FROM ranked WHERE some_field IN ('A','B','C')
), closers AS (
  SELECT
    *,
    LAG("rank") OVER (PARTITION BY some_fk ORDER BY "rank") AS rank_lag
  FROM ranked WHERE some_field IN ('X','Y','Z')
)
SELECT DISTINCT
  openers.some_fk,
  FIRST_VALUE(openers.some_date_field) OVER (PARTITION BY some_fk ORDER BY "rank")
    AS some_date_field_start,
  closers.some_date_field AS some_date_field_end
FROM openers
JOIN closers
  ON openers.some_fk = closers.some_fk
WHERE openers."rank" BETWEEN COALESCE(closers.rank_lag, 0) AND closers."rank"

但我认为存在更优雅、规范且无需复杂嵌套的PostgreSQL实现方式，希望得到更优解法。

方案1：使用MATCH_RECOGNIZE（PostgreSQL 14+） #

PostgreSQL 14及以上版本支持模式匹配函数MATCH_RECOGNIZE，可以简洁地实现这类事件区间匹配需求：

SELECT some_fk, some_date_field_start, some_date_field_end
FROM foo
MATCH_RECOGNIZE (
  PARTITION BY some_fk
  ORDER BY some_date_field
  MEASURES
    FIRST(o.some_date_field) AS some_date_field_start,
    LAST(c.some_date_field) AS some_date_field_end
  PATTERN (o+ c?)
  DEFINE
    o AS some_field IN ('A', 'B', 'C'),
    c AS some_field IN ('X', 'Y', 'Z')
) mr;

逻辑说明：

• PARTITION BY some_fk：按分组键拆分数据
• ORDER BY some_date_field：确保数据按时间顺序处理
• PATTERN (o+ c?)：匹配一个或多个开启/持续事件（o+），后面可选跟一个关闭事件（c?）
• MEASURES：提取每组的首个开启事件时间，以及最后一个关闭事件时间（无关闭事件则返回NULL）

这个方案代码简洁，逻辑直观，完全符合需求。

方案2：窗口函数分组（兼容低版本PostgreSQL） #

如果你的PostgreSQL版本低于14，可以使用窗口函数累计分组的方式实现：

WITH events AS (
  SELECT
    some_fk,
    some_date_field,
    some_field,
    -- 从后往前累计关闭事件数量，将同一事件区间的记录归为一组
    SUM(CASE WHEN some_field IN ('X','Y','Z') THEN 1 ELSE 0 END) 
      OVER (PARTITION BY some_fk ORDER BY some_date_field DESC) AS group_id
  FROM foo
)
SELECT
  some_fk,
  MIN(some_date_field) AS some_date_field_start,
  MAX(CASE WHEN some_field IN ('X','Y','Z') THEN some_date_field END) AS some_date_field_end
FROM events
GROUP BY some_fk, group_id
ORDER BY some_fk, some_date_field_start;

逻辑说明：

1. 首先通过反向累计关闭事件的数量，为每个事件区间分配唯一的group_id——每个关闭事件会将后续（时间更早）的开启事件归为新的分组
2. 按some_fk和group_id分组，取每组的最小时间作为区间起始（首个开启事件时间），取组内的关闭事件时间作为区间结束（无则返回NULL）

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