BigQuery中NOT IN子查询报错，求正确改写方案

问题场景 #

执行以下SQL语句时触发报错，该语句试图通过NOT IN子查询筛选数据：

SELECT country, city, count(cases) as total_cases 
FROM Table1 
where country = 'US'
AND country || city NOT IN (SELECT distinct country || city 
FROM Tablex)
GROUP BY 1, 2;

报错信息 #

"Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN."
（中文翻译：不支持引用其他表的关联子查询，除非可以将其去关联，例如转换为高效的JOIN）

尝试的改写（未匹配原逻辑） #

曾尝试用WITH子句结合LEFT JOIN改写，但未贴合原需求，代码如下：

WITH Alias_table1 AS (Query 1),

Alias_table2 AS (Query 2),

query as (Select * from Alias_Table1
LEFT JOIN Alias_Tabl2
ON Alias_Table1.ID = Alias_Table2.ID
WHERE Alias_Table2 IS NULL)

SELECT * FROM query

正确的改写方案 #

原逻辑是筛选出Table1中country='US'且country||city不存在于Tablex中的数据并聚合，正确的LEFT JOIN改写需基于country和city（或拼接后的字段）关联，而非ID。以下是两种可行方案：

方案1：基于拼接字段关联 #

WITH excluded_pairs AS (
  SELECT DISTINCT country || city AS country_city
  FROM Tablex
)
SELECT 
  t1.country, 
  t1.city, 
  COUNT(t1.cases) AS total_cases
FROM Table1 t1
LEFT JOIN excluded_pairs ep
  ON t1.country || t1.city = ep.country_city
WHERE t1.country = 'US'
  AND ep.country_city IS NULL
GROUP BY 1, 2;

方案2：基于country和city分别关联（更高效） #

拼接字段可能影响查询性能，推荐直接用两个字段关联：

WITH excluded_locations AS (
  SELECT DISTINCT country, city
  FROM Tablex
)
SELECT 
  t1.country, 
  t1.city, 
  COUNT(t1.cases) AS total_cases
FROM Table1 t1
LEFT JOIN excluded_locations el
  ON t1.country = el.country
  AND t1.city = el.city
WHERE t1.country = 'US'
  AND el.country IS NULL
GROUP BY 1, 2;

方案说明 #

• 先通过WITH子句提取Tablex中需要排除的国家-城市组合
• 使用LEFT JOIN关联Table1和排除列表，筛选出关联不上的记录（即不在排除列表中的数据）
• 最后按country和city聚合统计病例数

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