Redshift环境下按分区列合并为单条记录的高效实现方法

核心需求 #

按session_id分区，合并同一session_id对应的多条时间记录，最终取该session的最小start_date和最大end_date。

现有写法的性能问题 #

你目前使用窗口函数+rank过滤的逻辑存在较多冗余计算开销：

• 额外触发了两次窗口排序计算
• 冗余的distinct去重步骤
• 还要额外过滤rank=1的记录，数据量级越大，性能损耗越明显

最优性能实现方案 #

该需求逻辑完全可以通过简单的分组聚合实现，是Redshift环境下性能最高的写法，避免了所有不必要的排序、窗口计算开销：

WITH abc AS(
    SELECT 123 AS session_id,'2020-09-20 00:00:00'::timestamp AS start_date,
           '2021-05-31 00:00:00'::timestamp AS end_date
    UNION ALL
    SELECT 123 AS session_id,'2021-06-01 00:00:00'::timestamp AS start_date,
           '2021-08-17 00:00:00'::timestamp AS end_date
     UNION ALL
    SELECT 123 AS session_id,'2021-08-18 00:00:00'::timestamp AS start_date,
           '2021-08-19 00:00:00'::timestamp AS end_date
)
SELECT 
    session_id,
    MIN(start_date)::date AS min_start_date,
    MAX(end_date)::date AS max_end_date
FROM abc
GROUP BY session_id

优化说明 #

• 将原来的UNION替换为UNION ALL：如果你的源数据没有重复记录，UNION ALL跳过了去重步骤，性能远高于UNION
• 仅需要一次全表扫描完成聚合计算，不需要排序、窗口计算等多余操作，大表场景下性能比原有写法提升数倍
• 直接输出最终结果，不需要额外的过滤步骤，资源消耗更低

拓展场景（可选） #

如果后续需求变为同一个session_id下仅合并连续/重叠的时间区间，时间断档的分开输出，可以用如下间隙识别方案：

WITH session_data AS (
    SELECT 
        session_id,
        start_date,
        end_date,
        LAG(end_date) OVER (PARTITION BY session_id ORDER BY start_date) AS prev_end
    FROM abc
),
session_groups AS (
    SELECT 
        *,
        SUM(CASE WHEN start_date <= prev_end + INTERVAL '1 day' THEN 0 ELSE 1 END) 
            OVER (PARTITION BY session_id ORDER BY start_date) AS time_group
    FROM session_data
)
SELECT 
    session_id,
    MIN(start_date)::date AS group_start,
    MAX(end_date)::date AS group_end
FROM session_groups
GROUP BY session_id, time_group

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