BigQuery如何按分区补全缺失日期且不做全局前后填充

核心逻辑 #

不需要生成全局的连续日期，仅针对每个product + store分组的自有日期区间补全即可，实现步骤如下：

• 统计每个分组的最小、最大订单日期，确定需要补全的日期范围
• 生成每个分组对应区间的连续日期，拆分得到所有需要保留的行
• 左关联原始销售数据，缺失的销售额默认赋值为0

可直接运行的SQL代码 #

WITH group_date_range AS (
  -- 计算每个(商品,门店)分组的日期起止范围
  SELECT 
    product, 
    store,
    MIN(order_date) AS min_date,
    MAX(order_date) AS max_date
  FROM mydata -- 替换为你的实际表名
  GROUP BY product, store
),
full_group_dates AS (
  -- 生成每个分组从最小到最大日期的所有连续日期
  SELECT 
    product,
    store,
    order_date
  FROM group_date_range,
  UNNEST(GENERATE_DATE_ARRAY(min_date, max_date, INTERVAL 1 DAY)) AS order_date
)
-- 左关联原表，缺失销售额填0
SELECT 
  f.order_date,
  f.product,
  f.store,
  IFNULL(m.sales, 0) AS sales
FROM full_group_dates f
LEFT JOIN mydata m
  ON f.product = m.product 
  AND f.store = m.store 
  AND f.order_date = m.order_date
ORDER BY f.product, f.store, f.order_date

效果说明 #

运行以上代码后，每个product + store分组只会补全自身首次下单到末次下单之间的缺失日期，不会扩展到全局的最小/最大日期，完全符合要求的输出效果，缺失日期对应的sales字段统一为0。

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