Redshift中按10分钟切片统计会话时段数量的优化方案

针对Redshift中避免笛卡尔积的性能问题，推荐使用时间序列生成+非等值关联的方案，核心思路是先生成所需的10分钟时间切片，再关联会话表统计每个切片内的活跃会话数，完全规避不必要的笛卡尔积计算。

实现步骤与SQL示例 #

1. 生成对齐到10分钟的时间切片

通过generate_series生成覆盖会话时间范围的所有10分钟切片，确保切片起始时间严格对齐到00/10/20/30/40/50分：

WITH time_slices AS (
    -- 先计算会话的时间范围边界
    SELECT
        -- 将起始时间对齐到最近的10分钟整点
        DATE_TRUNC('hour', min_play) + (FLOOR(DATE_PART('minute', min_play)/10) * 10) * INTERVAL '1 minute' AS base_start,
        max_stop
    FROM (
        SELECT MIN(play) AS min_play, MAX(stop) AS max_stop FROM #times
    ) t_range
    -- 生成所有10分钟切片
    CROSS JOIN generate_series(
        0,
        -- 计算总共有多少个10分钟切片
        CEIL(DATE_PART('minute', max_stop - base_start)/10) + DATE_PART('hour', max_stop - base_start)*6
    ) AS s(num)
    -- 生成每个切片的起始和结束时间
    SELECT
        base_start + (num * INTERVAL '10 minutes') AS slice_start,
        base_start + ((num + 1) * INTERVAL '10 minutes') AS slice_end
    WHERE base_start + (num * INTERVAL '10 minutes') <= max_stop
)

2. 统计各切片的会话数量

通过非等值关联判断会话是否覆盖当前切片，再按切片分组统计：

SELECT
    ts.slice_start,
    -- 按会话ID去重统计，若#times每行对应一个会话则用COUNT(*)也可
    COUNT(DISTINCT t.session_id) AS active_session_count
FROM time_slices ts
LEFT JOIN #times t
    -- 会话开始时间早于切片结束，且会话结束时间晚于切片开始
    ON t.play < ts.slice_end
    AND t.stop >= ts.slice_start
GROUP BY ts.slice_start
ORDER BY ts.slice_start;

性能优势说明 #

• 避免了原方案中小时/分钟临时表的笛卡尔积，仅生成会话时间范围内必要的10分钟切片，数据量大幅减少。
• Redshift对非等值关联的优化（尤其是当play/stop字段设置为排序键时）能高效过滤匹配的会话，远优于笛卡尔积后的全量匹配。
• 时间切片的生成逻辑仅扫描一次#times的极值，计算开销极低。

可选优化 #

如果会话表#times的play字段设置了排序键，可在生成时间切片时利用排序键进一步缩小扫描范围，或者将时间切片CTE替换为临时表并设置分布键，提升关联效率。

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