Google BigQuery -将两个查询合并为一个(sql)

Question

在使用SQL的Google BigQuery中，我有两个独立的查询，我想合并成一个。它们都从相同的表中提取相同的字段，但由于时间、项和位置的不同而不同。我不希望数据从一个查询交叉到另一个查询，但我希望输出在一起。

查询1

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value
,COUNT(Ticket_Number)   
FROM  
Revenue_Table  
WHERE 
Revenue_Date ("201501","201502","201601",201602")  
AND Item_Number ("2987","9876","2345")  
AND Location_Number ("23456","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

查询2

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value 
,COUNT(Ticket_Number)  
FROM  
Revenue_Table  
WHERE 
Revenue_Date ("201502","201503","201602",201603")  
AND Item_Number ("1678","2225","1098")  
AND Location_Number ("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 Revenue_Date ("201501“、"201502”、“201601”、201602)

查询2 Revenue_Date ("201502“、"201503”、“201602”、201603)

项目查询1 Item_Number ("2987“、"9876”、"2345")

查询2 Item_Number ("1678“、"2225”、"1098")

位置查询1 Location_Number ("23456“、"23456”、"0987")

查询2 Location_Number ("09876“、"23456”、"2111")

我希望这两个查询分别提取数据(即，我不希望在日期201501 (YYYYMM)或位置23456中的项目1678的收入)，一旦数据被提取，我希望它显示在输出中的一个表中。

有人能帮上忙吗。谢谢。

Answer 1

这看起来确实很直截了当，还是我遗漏了什么？你试过使用UNION ALL吗。这将将您的两个结果合并为一个：

您需要确保由UNION或UNION ALL分隔的两个查询都需要有完全相同的列数。

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value
,COUNT(Ticket_Number)   
FROM  
Revenue_Table  
WHERE 
Revenue_Date ('201501','201502','201601','201602')  
AND Item_Number ('2987','9876','2345')  
AND Location_Number ('23456','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

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value 
,COUNT(Ticket_Number)  
FROM  
Revenue_Table  
WHERE 
Revenue_Date ('201502','201503','201602','201603')  
AND Item_Number ('1678','2225','1098')  
AND Location_Number ('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

Answer 2

或者在这样的一个查询中尝试：

SELECT Revenue_Date, Item_Number ,Location_Number, Revenue_Group, Revenue_Value,COUNT (Ticket_Number)
FROM Revenue_Table
WHERE
    (( Revenue_Date ("201501","201502","201601",201602")
      AND Item_Number ("2987","9876","2345")
      AND Location_Number ("23456","23456","0987")
    )
    OR
    (
     Revenue_Date ("201502","201503","201602",201603")
     AND Item_Number ("1678","2225","1098")
     AND Location_Number ("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;