SQL查询无匹配记录时如何返回全部月份且SKU列无空值

• 过滤单个SKU查询后，无行为数据的月份SKU列返回Null：
  
• 预期效果：全年12个月份行均显示对应SKU编号，无行为数据的月份统计值补0：
  
• 调整后仍不符合预期，存在SKU为Null的行：
  

现有查询逻辑存在缺陷：month_generate公用表表达式仅生成了1-12月的连续月份序列，未关联SKU维度。左连接时，仅存在SKU行为数据的月份能匹配到month_agg中的行，无对应行为的月份无法匹配到b表记录，b.sku_number就会返回Null值。

方案1：通用方案（支持单/多SKU查询，推荐） #

先通过交叉连接构造「SKU+月份」的全量基础维度表，再左连接聚合后的行为数据，无匹配行为的行SKU字段不会为空，统计值用coalesce补0即可。
修正后完整SQL：

WITH month_agg AS (
    SELECT 
        sku_number, 
        EXTRACT(month FROM date_) AS month, 
        SUM(v) AS viewed, 
        SUM(a) AS add_to_cart, 
        SUM(p) AS purchased
    FROM merge_and_pivot
    WHERE sku_number = '10671924'
    GROUP BY sku_number, EXTRACT(month FROM date_)
),
sku_dim AS (
    SELECT DISTINCT sku_number 
    FROM merge_and_pivot 
    WHERE sku_number = '10671924'
),
month_generate AS (
    SELECT EXTRACT(month FROM date_) AS month
    FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', '2018-12-01', INTERVAL 1 MONTH)) AS date_
),
full_base AS (
    SELECT s.sku_number, m.month
    FROM sku_dim s
    CROSS JOIN month_generate m
)
SELECT 
    f.month, 
    f.sku_number, 
    COALESCE(b.viewed, 0) AS viewed, 
    COALESCE(b.add_to_cart, 0) AS add_to_cart, 
    COALESCE(b.purchased, 0) AS purchased
FROM full_base f
LEFT JOIN month_agg b 
    ON f.sku_number = b.sku_number 
    AND f.month = b.month
ORDER BY f.month

方案2：单SKU简化方案 #

如果仅需查询固定单个SKU，可直接在最终查询中写死SKU值，无需调整连接逻辑，改动量最小：

-- month_agg、month_generate 公用表部分保持原有写法不变
SELECT 
    a.month, 
    '10671924' AS sku_number,
    COALESCE(b.viewed, 0), 
    COALESCE(b.add_to_cart, 0), 
    COALESCE(b.purchased, 0)
FROM month_generate a
LEFT JOIN month_agg b ON a.month = b.month

提示：如果后续需要同时查询多个SKU的全月度数据，必须使用方案1，方案2仅适用于固定单SKU的临时查询场景。

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