Snowflake查询如何拉取包含零销量的全量Store_ID、week_id、upc_id数据

核心问题 #

你当前查询的核心问题是没有预先构造「符合条件的所有门店+目标UPC+时间范围内所有周」的全量维度组合，仅对门店表做右连接无法补全缺失的UPC和周维度记录，自然无法展示零销售的组合。

修复思路 #

• 先通过笛卡尔积生成全量维度基准表，覆盖你需要展示的所有门店、UPC、周的组合
• 以全量维度基准表作为主表，左连接销售统计子查询和上周销售表，无销售的指标默认返回空，可通过IFNULL转为0

修改后的完整代码 #

WITH all_dimensions AS (
    -- 筛选符合条件的全量门店
    SELECT DISTINCT store_id, district_cd 
    FROM dw_dss.lu_store_finance_om 
    WHERE division_id = 19 AND district_cd IN (72,73)
),
all_upcs AS (
    -- 筛选需要查询的所有目标UPC
    SELECT upc_id, upc_dsc 
    FROM dw_dss.lu_upc 
    WHERE upc_id IN (27610100000,27610200000,27610300000,27610400000)
),
all_weeks AS (
    -- 提取时间范围内的所有周及对应周排序序号
    SELECT DISTINCT week_id, 
           DENSE_RANK() OVER (ORDER BY week_id) AS rank
    FROM lu_day_merge 
    WHERE d_date BETWEEN '2021-07-25' AND CURRENT_DATE -1
),
-- 生成全量维度组合：门店 * UPC * 周
full_base AS (
    SELECT * FROM all_dimensions
    CROSS JOIN all_upcs
    CROSS JOIN all_weeks
),
-- 原有销售统计逻辑
sales_data AS (
    SELECT 
        week_id,
        str.store_id,
        txn.upc_id,
        DENSE_RANK() OVER (ORDER BY week_id) as rank,
        SUM(txn.net_amt) as net_amt,
        SUM(txn.item_qty) as item_qty
    FROM dw_dss.txn_facts txn
    LEFT JOIN dw_dss.lu_store_finance_om STR ON str.store_id = txn.store_id
    JOIN dw_dss.lu_upc upc ON upc.upc_id = txn.upc_id
    JOIN lu_day_merge day ON day.d_date = txn.txn_dte
    WHERE str.district_cd in (72,73)
        AND txn.upc_id in (27610100000,27610200000,27610300000,27610400000)
        AND str.division_id = 19
        AND txn_dte between '2021-07-25' and current_date - 1
    GROUP BY 1,2,3
)
-- 最终关联查询
SELECT 
    fb.week_id,
    fb.district_cd,
    fb.store_id,
    fb.upc_id,
    fb.upc_dsc,
    IFNULL(SUM(sd.net_amt), 0) as current_week_sales,
    IFNULL(SUM(t.net_amt), 0) as last_week_sales,
    IFNULL(SUM(sd.item_qty), 0) as current_week_units,
    IFNULL(SUM(t.item_qty), 0) as last_week_units
FROM full_base fb
LEFT JOIN sales_data sd 
    ON fb.store_id = sd.store_id 
    AND fb.upc_id = sd.upc_id 
    AND fb.week_id = sd.week_id
LEFT JOIN temp_tables.ab_week_ago t
    ON t.rank = fb.rank 
    AND fb.store_id = t.store_id 
    AND fb.upc_id = t.upc_id
GROUP BY 1,2,3,4,5

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