PostgreSQL查询优化：去除子查询实现单SELECT取首尾时间对应level1值

核心需求 #

按id分组查询两组关联值：

• 分组内最小start_time对应的level1字段值，别名为level1_at_start
• 分组内最大end_time对应的level1字段值，别名为level1_at_end

优化后SQL（无关联子查询，单条SELECT实现） #

基于PostgreSQL语法特性，使用窗口函数实现全表单次扫描，性能远优于原关联子查询方案：

SELECT DISTINCT
    id,
    FIRST_VALUE(level1) OVER (
        PARTITION BY id 
        ORDER BY start_time ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS level1_at_start,
    LAST_VALUE(level1) OVER (
        PARTITION BY id 
        ORDER BY end_time ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS level1_at_end
FROM "table"
;

方案说明 #

1. 通过PARTITION BY id实现按ID分组逻辑，无需额外GROUP BY声明
2. FIRST_VALUE按start_time升序排序后取分组内第一条记录的level1值，对应最小开始时间的匹配值
3. LAST_VALUE按end_time升序排序后取分组内最后一条记录的level1值，对应最大结束时间的匹配值
4. 最终通过DISTINCT去重，得到每个ID唯一的结果行，完全匹配样例输入输出要求

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