BigQuery查询日期区间数据时如何为无记录的日期返回默认0值

BigQuery 完全可以实现该需求，你现有语句出现重复值是两个错误导致的：

• dates CTE 中将业务聚合表和 GENERATE_DATE_ARRAY 生成的日期做了交叉连接，相当于把每个生成的日期和原表所有行做笛卡尔积，直接产生大量重复行
• 左关联条件额外增加了d.total = t.total的多余限制，导致匹配逻辑异常，进一步加剧结果错误

你可以直接使用以下修正后的查询：

WITH sales_agg AS (
  SELECT
    DATA_VENDA AS date,
    CAST(SUM(VLR_VENDA_TABELA) AS FLOAT64) AS total
  FROM `project.myproject`
  WHERE
    DATA_VENDA BETWEEN '2020-10-02' AND '2020-10-07'
    AND COD_CP = '0000010232'
  GROUP BY DATA_VENDA
),
-- 生成目标区间内的全部连续日期
continuous_dates AS (
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2020-10-02'), DATE('2020-10-07'))) AS date
)
SELECT 
  d.date,
  IFNULL(s.total, 0) AS total
FROM continuous_dates d
LEFT JOIN sales_agg s 
ON d.date = s.date
ORDER BY d.date

逻辑说明：先单独生成你需要的日期区间内所有连续日期，再和按日期聚合好的业务数据做左连接，匹配不到数据的日期会自动把total值替换为0，不会产生重复数据。

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