left-join - Left Join 与部分为空或 null 的多个条件

Question

我有一个包含跟踪数据的表格。在其他值中，该表具有列 traffic_medium、traffic_source 和 traffic_campaign。这些列有时确实包含 (none) 或 null 作为值。

我想使用左连接匹配来自其他表的访问者总和，并将媒介、源和事件作为匹配条件。

如果所有列都包含数据，这会很好地工作。如果一列具有 (none) 或 null 作为值，则它不起作用。

我使用 BigQuery 和旧版 SQL。

SELECT  
A.id,
A.trafficSource_medium,
A.trafficSource_source,
A.trafficSource_campaign,
B.sum_visitor AS sum_visitor

FROM [table] AS A
left outer join (Select 
count(distinct fullvisitorID) as sum_visitor,
trafficSource_medium,
trafficSource_source,
trafficSource_campaign
FROM [table2] 
GROUP BY trafficSource_medium,
trafficSource_source,
trafficSource_campaign)
AS B
on A.trafficSource_medium=B.trafficSource_medium AND     
A.trafficSource_source=B.trafficSource_source AND 
A.trafficSource_campaign=B.trafficSource_campaign

感谢您的帮助!

Answer 1

试试下面的方法
假设各个字段都是 STRING 类型。如果它们是 INT - 将“n/a”替换为 -999 - 选择不用作相应字段值的常量很重要

#legacySQL
SELECT  
  A.id,
  CASE WHEN A.trafficSource_medium = 'n/a' THEN NULL ELSE A.trafficSource_medium END AS trafficSource_medium,
  CASE WHEN A.trafficSource_source = 'n/a' THEN NULL ELSE A.trafficSource_source END AS trafficSource_source,
  CASE WHEN A.trafficSource_campaign = 'n/a' THEN NULL ELSE A.trafficSource_campaign END AS trafficSource_campaign,
  B.sum_visitor AS sum_visitor
FROM (
  SELECT 
    id,
    IFNULL(trafficSource_medium, 'n/a') AS trafficSource_medium,
    IFNULL(trafficSource_source, 'n/a') AS trafficSource_source,
    IFNULL(trafficSource_campaign 'n/a') AS trafficSource_campaign
  FROM [table] 
) AS A
LEFT OUTER JOIN (
  SELECT 
    COUNT(DISTINCT fullvisitorID) AS sum_visitor,
    IFNULL(trafficSource_medium, 'n/a') AS trafficSource_medium,
    IFNULL(trafficSource_source, 'n/a') AS trafficSource_source,
    IFNULL(trafficSource_campaign 'n/a') AS trafficSource_campaign
  FROM [table2] 
  GROUP BY 
    trafficSource_medium,
    trafficSource_source,
    trafficSource_campaign
) AS B
ON A.trafficSource_medium = B.trafficSource_medium 
AND A.trafficSource_source = B.trafficSource_source 
AND A.trafficSource_campaign = B.trafficSource_campaign  

这里的想法是将 NULL“转换”为某个值，以便它们可以连接 - 然后在最终的 SELECT 中将其“转换”回 NULL

如果您可以迁移到标准 SQL - 您可以尝试下面的方法 - 要做的更改较少 - 主要是在 ON 子句中

#standardSQL
SELECT  
  A.id,
  A.trafficSource_medium,
  A.trafficSource_source,
  A.trafficSource_campaign,
  B.sum_visitor AS sum_visitor
FROM `table` AS A
LEFT OUTER JOIN (
  SELECT 
    COUNT(DISTINCT fullvisitorID) AS sum_visitor,
    trafficSource_medium,
    trafficSource_source,
    trafficSource_campaign
  FROM `table2`
  GROUP BY 
    trafficSource_medium,
    trafficSource_source,
    trafficSource_campaign
) AS B
ON IFNULL(A.trafficSource_medium, 'n/a') = IFNULL(B.trafficSource_medium, 'n/a') 
AND IFNULL(A.trafficSource_source, 'n/a') = IFNULL(B.trafficSource_source, 'n/a') 
AND IFNULL(A.trafficSource_campaign, 'n/a') = IFNULL(B.trafficSource_campaign, 'n/a')