如何在BigQuery中关联日期表与销售表补全日历维度？

核心思路 #

要实现每个商品的每日/渠道组合全覆盖，关键是先构建日历表 × 所有唯一商品的全量维度集合，再基于这个集合左连接销售表，而非直接用日历表左连销售表。

具体SQL实现 #

假设你的表结构如下（请根据实际表名/字段替换）：

• 日历表：calendar_table，包含date（日期）、channel（销售渠道）字段
• 销售表：sales_table，包含date、channel、product_id（商品ID）、ord_qty（订单量）、demand_usd（美元需求）及其他需填充字段

WITH unique_products AS (
    -- 提取销售表中所有唯一商品
    SELECT DISTINCT product_id
    FROM sales_table
),
full_dimension AS (
    -- 生成日历表与所有商品的全量组合（日期-渠道-商品）
    SELECT 
        c.date,
        c.channel,
        p.product_id
    FROM calendar_table c
    CROSS JOIN unique_products p
)
SELECT 
    fd.date,
    fd.channel,
    fd.product_id,
    -- 无销售时订单量和需求金额填0
    COALESCE(s.ord_qty, 0) AS ord_qty,
    COALESCE(s.demand_usd, 0) AS demand_usd,
    -- 用LAST_VALUE填充其他字段（以price为例），按商品+渠道分区、日期排序
    LAST_VALUE(s.price IGNORE NULLS) OVER (
        PARTITION BY fd.product_id, fd.channel 
        ORDER BY fd.date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS price
    -- 其他需要填充的字段可参照上述写法
FROM full_dimension fd
LEFT JOIN sales_table s
    ON fd.date = s.date
    AND fd.channel = s.channel
    AND fd.product_id = s.product_id
ORDER BY fd.product_id, fd.channel, fd.date;

为什么之前的左连接会缺失数据？ #

你之前的写法应该是直接用日历表左连接销售表，这种逻辑下：

• 当某个日期-渠道下所有商品都无销售时，会保留该日期-渠道的空商品记录
• 当某个日期-渠道下部分商品有销售时，只有有销售的商品会出现在结果中，无销售的商品对应的日期-渠道组合会直接缺失

而通过CROSS JOIN生成全量维度集合后，无论该商品是否有销售，每个日期-渠道-商品的组合都会被保留，左连接后无销售的记录自然可以用COALESCE填充为0。

注意事项 #

1. 如果需要包含从未产生过销售的商品（不在销售表中），请将unique_products的数据源替换为你的商品主表，而非销售表的DISTINCT结果
2. CROSS JOIN的性能取决于商品数量：6年共2190天×2个渠道，若商品数为1000，总组合数约438万，在BigQuery中完全可以高效处理
3. 使用LAST_VALUE时务必添加IGNORE NULLS，避免中间的空值打断填充逻辑；分区字段必须包含product_id和channel，确保不同商品/渠道的历史值独立填充

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