如何在BigQuery中生成日期序列并填充缺失销售数据为0？查询结果缺失日期问题求助

你的问题核心是WHERE子句的过滤条件破坏了左连接的逻辑，导致没有匹配销售数据的日期被直接过滤掉，这就是为什么只返回了2021-04-01这一行的原因。

为什么原查询不符合预期？ #

当使用LEFT JOIN时，days表的所有行本应被保留，即使sales表没有匹配记录（此时sales表的字段会呈现NULL）。但你的WHERE子句加了两个关键限制：

1. DATE(B.date_local) BETWEEN "2021-04-01" AND CURRENT_DATE()：对于无匹配的行，B.date_local是NULL，DATE(NULL)同样是NULL，而NULL无法满足任何范围匹配条件，这些行因此被过滤。
2. location_id = '164'：无匹配行的location_id为NULL，自然不满足等于'164'的条件，也会被排除。

正确的查询写法 #

需要把针对sales表的过滤条件（日期范围、门店ID）放到LEFT JOIN的ON子句中，而非WHERE子句。这样只会在连接阶段过滤sales表的行，不会影响days表所有日期的保留：

SELECT 
  A.day AS date,
  COUNT(B.transaction_id) AS sales 
FROM days AS A 
LEFT JOIN sales AS B 
  ON A.day = DATE(B.date_local)
  AND DATE(B.date_local) BETWEEN "2021-04-01" AND CURRENT_DATE()
  AND B.location_id = '164'
GROUP BY A.day 
ORDER BY A.day

额外说明 #

• COUNT(B.transaction_id)会自动将无匹配的行（transaction_id为NULL）统计为0，无需额外用IFNULL处理。
• 如果你的days表已经仅包含2021-04-01至2021-04-07的日期，DATE(B.date_local) BETWEEN ...这个条件可以省略——因为ON里的A.day = DATE(B.date_local)已经限制了日期匹配范围。

执行这个查询后，就能得到你期望的结果：所有日期都会被列出，无销售的日期sales列显示为0。

内容的提问来源于stack exchange，提问作者Marc H.