BigQuery SQL如何对比两表查找各日期下的缺失门店记录

原SQL错误原因 #

你当前的写法仅能筛选出从未在Table1中出现过的门店，无法按每个日期维度匹配缺失门店，左连后没有匹配的行自然Report_Date字段返回null，不符合需求逻辑。

正确实现逻辑 #

要实现按每个日期匹配缺失门店，需先生成「所有统计日期 × 全量门店」的完整基准组合，再和Table1的实际上报数据对比，匹配为空的即为对应日期的缺失门店：

1. 提取Table1中所有去重的上报日期
2. 将去重日期与Table2全量门店做交叉连接，得到每个日期应有的全部门店基准集
3. 左连接Table1去重后的门店-日期上报记录，连接条件同时匹配Store和Report_Date
4. 筛选左连后Table1字段为空的行，即为对应日期缺失的门店

正确SQL代码 #

WITH
-- 提取Table1去重后的门店-日期上报记录
distinct_tab1 AS (
  SELECT DISTINCT
    Store,
    Report_Date
  FROM Table1
),
-- 提取Table1中所有去重的上报日期
all_dates AS (
  SELECT DISTINCT Report_Date
  FROM Table1
)
SELECT
  t2.Store AS MissingStore,
  d.Report_Date
FROM all_dates d
-- 生成所有日期+全量门店的完整基准组合
CROSS JOIN Table2 t2
-- 左连实际上报记录
LEFT JOIN distinct_tab1 t1
  ON t1.Store = t2.Store
  AND t1.Report_Date = d.Report_Date
-- 筛选未上报的组合
WHERE t1.Store IS NULL
ORDER BY d.Report_Date

上述代码执行后输出结果与你给出的预期格式完全一致。

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