求交时间区间并计算区间内最大值的SQL实现问题

问题说明 #

现有表结构及数据如下：

需要拆分所有重叠/相邻的时间区间，得到不重叠的子区间，并计算每个子区间内value的最大值，期望结果：

尝试执行以下SQL未得到预期结果：

SELECT range_agg(tsrange(start_at, finish_at)), max(value) FROM my_table;

返回结果：

解决方案 #

range_agg会直接合并所有重叠区间为一个整体，max(value)也只会返回全局最大值，无法满足按拆分后子区间计算最大值的需求。可以通过以下步骤实现目标：

1. 提取所有关键时间点（所有start_at和finish_at），排序后生成相邻时间对作为基础子区间。
2. 匹配每个基础子区间对应的原始记录，判断子区间是否被原始区间覆盖。
3. 按基础子区间聚合，计算覆盖该区间的所有记录的value最大值。

对应的PostgreSQL SQL语句：

WITH all_times AS (
    SELECT start_at AS time_point FROM my_table
    UNION
    SELECT finish_at AS time_point FROM my_table
),
time_ranges AS (
    SELECT
        time_point AS start_at,
        LEAD(time_point) OVER (ORDER BY time_point) AS finish_at
    FROM all_times
    ORDER BY time_point
)
SELECT
    tr.start_at,
    tr.finish_at,
    MAX(t.value) AS max
FROM time_ranges tr
JOIN my_table t ON tr.start_at >= t.start_at AND tr.finish_at <= t.finish_at
WHERE tr.finish_at IS NOT NULL
GROUP BY tr.start_at, tr.finish_at
ORDER BY tr.start_at;

语句解释 #

• all_times：收集所有起始、结束时间点并去重，得到所有分割区间的关键节点。
• time_ranges：用LEAD窗口函数将相邻时间点配对，生成拆分后的不重叠子区间。
• 最终查询：关联子区间与原始表，筛选出被原始区间完全覆盖的子区间，按子区间聚合计算最大值。

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