Google BigQuery中合并两个无交叉数据的SQL查询需求

直接使用UNION ALL合并两个查询即可，结合筛选条件的互斥性确保结果无交叉，完整SQL如下：

-- 查询1：提取指定条件的数据集
SELECT 
  Revenue_Date,
  Item_Number,
  Location_Number,
  Revenue_Group,
  Revenue_Value,
  COUNT(Ticket_Number) AS Ticket_Count
FROM  
  Revenue_Table  
WHERE 
  Revenue_Date IN ("201501","201502","201601","201602")  
  AND Item_Number IN ("2987","9876","2345")  
  AND Location_Number IN ("23456","0987")
  AND (Revenue_Group NOT IN ("123B","765C","345G","3456") OR Revenue_Group IS NULL)  
GROUP BY 
  Revenue_Date,
  Item_Number,
  Location_Number,
  Revenue_Value,
  Revenue_Group

UNION ALL

-- 查询2：提取另一组指定条件的数据集
SELECT 
  Revenue_Date,
  Item_Number,
  Location_Number,
  Revenue_Group,
  Revenue_Value,
  COUNT(Ticket_Number) AS Ticket_Count
FROM  
  Revenue_Table  
WHERE 
  Revenue_Date IN ("201502","201503","201602","201603")  
  AND Item_Number IN ("1678","2225","1098")  
  AND Location_Number IN ("09876","23456","2111")  
  AND (Revenue_Group NOT IN ("123B","765C","345G","3456") OR Revenue_Group IS NULL)  
GROUP BY 
  Revenue_Date,
  Item_Number,
  Location_Number,
  Revenue_Value,
  Revenue_Group

关键逻辑说明 #

1. 天然无交叉保障：两个查询的Item_Number筛选集合完全互斥（查询1的商品编号和查询2没有任何重叠），这直接确保了两个结果集不会出现交叉行，也彻底避免了查询1提取到商品1678数据的可能。
2. 重叠字段不影响：即使日期（如201502）、位置（如23456）存在重叠，由于商品编号的互斥性，不会产生重复数据。
3. 效率优化：使用UNION ALL而非UNION，因为无需去重逻辑，执行效率更高，适合BigQuery的大数据场景。
4. 代码精简：去除了查询1中Location_Number列表里重复的23456，不影响筛选结果但代码更整洁；给计数结果添加别名Ticket_Count，让输出列更直观。

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