BigQuery SQL实现匹配行保留、不匹配行置空的查询方案

现有表结构与数据 #

Table A #

user_id | acc_activation_date | generated_Date
1       | 13-04-2018          | 13-04-2018 
1       | 13-04-2018          | 14-04-2018 
1       | 13-04-2018          | 15-04-2018 
1       | 13-04-2018          | 16-04-2018 
1       | 13-04-2018          | 17-04-2018 
1       | 13-04-2018          | 18-04-2018 
1       | 13-04-2018          | 19-04-2018 
1       | 13-04-2018          | 20-04-2018 
1       | 13-04-2018          | 21-04-2018 
1       | 13-04-2018          | 22-04-2018 
1       | 13-04-2018          | 23-04-2018 
1       | 13-04-2018          | 24-04-2018 
1       | 13-04-2018          | 25-04-2018 

Table B #

user_id | acc_activation_date | joined_date | engagement_l1
1       | 13-04-2018          | 13-04-2018  | 1
1       | 13-04-2018          | 22-04-2018  | 1
1       | 13-04-2018          | 25-04-2018  | 0

期望输出 #

user_id | generated_date | acc_activation_date | joined_date | engagement_l1
1       | 13-04-2018     | 13-04-2018          | 13-04-2018  | 1 
1       | 14-04-2018     | 13-04-2018          |             |  
1       | 15-04-2018     | 13-04-2018          |             |  
1       | 16-04-2018     | 13-04-2018          |             |  
1       | 17-04-2018     | 13-04-2018          |             |  
1       | 18-04-2018     | 13-04-2018          |             | 
1       | 19-04-2018     | 13-04-2018          |             |  
1       | 20-04-2018     | 13-04-2018          |             |
1       | 21-04-2018     | 13-04-2018          |             | 
1       | 22-04-2018     | 13-04-2018          | 22-04-2018  | 1 
1       | 23-04-2018     | 13-04-2018          |             | 
1       | 24-04-2018     | 13-04-2018          |             |
1       | 25-04-2018     | 13-04-2018          | 25-04-2018  | 0 

问题分析 #

你之前的查询存在两个核心问题：

1. 第一个查询仅通过user_id关联两张表，未添加日期匹配条件，导致TableB的每一行都会和TableA的所有行匹配，从而产生大量重复行；
2. 第二个查询存在拼写错误（g.user_id应为b.user_id，且b.generated_date字段不存在），同时关联方向错误，未能以TableA为主表保留所有需要的行。

正确的BigQuery SQL实现 #

SELECT
  a.user_id,
  a.generated_Date,
  a.acc_activation_date,
  b.joined_date,
  b.engagement_l1
FROM
  `your-project.your-dataset.TableA` a
LEFT JOIN
  `your-project.your-dataset.TableB` b
ON
  a.user_id = b.user_id
  AND a.generated_Date = b.joined_date
ORDER BY
  a.generated_Date;

关键说明 #

• 使用LEFT JOIN：以TableA为主表，保留TableA的所有行，仅匹配TableB中满足关联条件的记录，不满足的字段自动填充为NULL（对应期望输出中的空值）；
• 关联条件：同时匹配user_id和日期（generated_Date = joined_date），确保仅关联同一用户同一日期的记录；
• 无需DISTINCT：TableA中每行唯一，且关联条件精准，不会产生重复行。

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