Redshift中替代Oracle分区外连接补全销售数据缺失值

Redshift不支持Oracle的PARTITION BY外连接语法，要补全无销售月份的产品数据，最简便的方法是先生成所有产品与目标日期的全量组合，再左连接销售数据表，具体实现如下：

核心思路 #

1. 提取所有唯一的产品信息，确保覆盖需要展示的全部产品
2. 生成需要统计的所有月份日期序列
3. 通过交叉连接得到每个产品对应每个月份的完整组合
4. 左连接实际销售数据，用COALESCE将无销售的金额置为0

Redshift兼容代码 #

WITH dates AS (
    SELECT to_date('2023-01-01', 'YYYY-MM-DD') AS d FROM sysdummy1
    UNION ALL
    SELECT to_date('2023-02-01', 'YYYY-MM-DD') AS d FROM sysdummy1
    UNION ALL
    SELECT to_date('2023-03-01', 'YYYY-MM-DD') AS d FROM sysdummy1
    UNION ALL
    SELECT to_date('2023-04-01', 'YYYY-MM-DD') AS d FROM sysdummy1
    UNION ALL
    SELECT to_date('2023-05-01', 'YYYY-MM-DD') AS d FROM sysdummy1
),
products AS (
    SELECT DISTINCT prod_id, prod_name
    FROM product_sales
),
product_date_combinations AS (
    SELECT p.prod_id, p.prod_name, d.d AS sale_date
    FROM products p
    CROSS JOIN dates d
)
SELECT 
    pdc.prod_id, 
    pdc.prod_name, 
    COALESCE(ps.sale_amount, 0) AS sale_amount, 
    pdc.sale_date
FROM product_date_combinations pdc
LEFT JOIN product_sales ps 
    ON pdc.prod_id = ps.prod_id 
    AND pdc.prod_name = ps.prod_name
    AND pdc.sale_date = ps.sale_date
ORDER BY pdc.sale_date, pdc.prod_id;

关键细节说明 #

• 用Redshift内置的sysdummy1表替代Oracle的dual表
• products CTE提取唯一产品，避免生成重复的产品-月份组合
• CROSS JOIN确保每个产品都能匹配到所有目标月份，不会遗漏无销售的记录
• COALESCE函数将无销售时的NULL金额转为0，满足展示需求

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