Redshift SQL多日期批量计算：高效自定义分组方案问询

需求说明 #

需要针对一系列日期（例如['2020-09-01', '2020-09-02', '2020-09-03']）批量计算结果，现有适用于单日期的SQL查询，需扩展至多日期场景。不希望用循环遍历的方式，希望通过自定义分组实现，单组对应单日期的WHERE筛选结果，且同一记录可属于多个不同分组，要求适配Redshift SQL，最多支持一千个日期。

输入数据 #

预期结果 #

现有单日期SQL查询 #

WITH temp_table AS (
    SELECT 
        column_1 - column_2 AS computed_column
    FROM table_name 
    WHERE (date_column_1 > '2020-09-01' AND date_column_2 < '2020-09-01') 
        OR (date_column_1 < '2020-09-01' AND date_column_2 > '2020-09-01')
) 
SELECT
    '2020-09-01' as result_date,
    avg(computed_column) as computed_result
FROM temp_table 

高效解决方案（Redshift适配） #

核心思路是将目标日期列表构造成临时数据集，与原表做笛卡尔积关联，针对每个日期应用筛选条件后，按日期分组计算结果。这种方式仅需一次扫描原表，效率远高于循环执行多次查询。

实现代码 #

-- 构造目标日期列表，支持最多1000个日期
WITH target_dates AS (
    SELECT '2020-09-01'::DATE AS result_date UNION ALL
    SELECT '2020-09-02'::DATE UNION ALL
    SELECT '2020-09-03'::DATE
    -- 如需更多日期，继续添加UNION ALL语句
),
filtered_data AS (
    SELECT
        td.result_date,
        t.column_1 - t.column_2 AS computed_column
    FROM table_name t
    CROSS JOIN target_dates td
    -- 应用原单日期的筛选逻辑，替换为当前关联的result_date
    WHERE (t.date_column_1 > td.result_date AND t.date_column_2 < td.result_date)
        OR (t.date_column_1 < td.result_date AND t.date_column_2 > td.result_date)
)
SELECT
    result_date,
    ROUND(AVG(computed_column), 1) AS computed_result -- 保留一位小数匹配预期结果
FROM filtered_data
GROUP BY result_date
ORDER BY result_date;

方案优势 #

1. 高效性：仅扫描一次原表，相比循环执行N次查询（N为日期数量），IO开销大幅降低，原表数据量越大优势越明显。
2. 扩展性：只需在target_dates中添加或修改日期即可支持任意数量（最多1000个）的日期计算。
3. Redshift适配：Redshift对笛卡尔积（CROSS JOIN）优化较好，DATE类型转换和聚合计算均符合Redshift语法规范。

注意事项 #

• 如果目标日期数量接近1000，可将日期存储在临时表中再与原表关联，避免过长的UNION ALL语句。
• 若原表数据量极大，可考虑对date_column_1和date_column_2建立索引，进一步提升筛选效率。

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