如何使用BigQuery中的UNIONSelect语句将结果限制为仅包括一个特定字段唯一的行

Question

我有以下代码。我试图将这两个表缝合在一起，但限制它只添加一次重复的Opportunity_ID，然后从第二个表(OpportunitiesUpdates(添加。

SELECT
Opportunity.Account_Name,
Opportunity.Opportunity_Name,
Opportunity.Opportunity_Owner,
Opportunity.Opportunity_ID
FROM
Opportunity
UNION DISTINCT
SELECT
OpportunityUpdates.Account_Name,
OpportunityUpdates.Opportunity_Name,
OpportunityUpdates.Opportunity_Owner,
OpportunityUpdates.Opportunity_ID
FROM
OpportunityUpdates
WHERE OpportunityUpdates.Opportunity_ID <> Opportunity.Opportunity_ID

Answer 1

此代码(通过Opportunity_ID(合并两个表中的所有记录，并基于Opportunity_ID为OpportunityUpdates表赋予优先级。

它假设相同的Opportunity_ID可以在任一表中("重复"(，但中的每个表中的Opportunity_ID是唯一的。它还假设Opportunity_ID不可为null(从不为null(。

SELECT DISTINCT
IF(ou.Opportunity_ID IS NOT NULL, ou.Account_Name, o.Account_Name) Account_Name,
IF(ou.Opportunity_ID IS NOT NULL, ou.Opportunity_Name, o.Opportunity_Name) Opportunity_Name,
IF(ou.Opportunity_ID IS NOT NULL, ou.Opportunity_Owner, o.Opportunity_Owner) Opportunity_Owner,
COALESCE(ou.Opportunity_ID, o.Opportunity_ID) Opportunity_ID
FROM OpportunityUpdates ou
FULL OUTER JOIN
Opportunity o
ON o.Opportunity_ID = ou.Opportunity_ID