BigQuery SQL查询返回双倍行数问题排查请求

你的问题出在JOIN的关联条件过于宽泛：仅用user_pseudo_id连接两个子查询，但两个子查询都是按user_pseudo_id + event_date分组得到2条记录，这会导致笛卡尔积——每个learner_cohort的记录都会和level_data中同用户的所有记录匹配，2×2=4条重复结果。

修正方案 #

把JOIN条件从仅匹配user_pseudo_id改成同时匹配user_pseudo_id和event_date，因为这两个字段组合才是两个子查询结果的唯一关联键。

修正后的完整SQL #

SELECT 
  learner_cohort.event_date,
  learner_cohort.user_pseudo_id,
  learner_cohort.app_id,
  learner_cohort.country,
  learner_cohort.first_open,
  level_data.level,
  level_data.level_success
 FROM
(
  SELECT user_pseudo_id, event_date, app_info.id AS app_id, geo.country AS country,user_first_touch_timestamp AS first_open
  FROM 
  (
    SELECT * FROM `ftm-b9d99.analytics_159643920.events_20*`
    WHERE PARSE_DATE('%y%m%d', _table_suffix) BETWEEN '2021-01-01' AND CURRENT_DATE()
    and event_name = 'level_completed'
    AND  user_pseudo_id like '942389096%'
  )
  GROUP BY user_pseudo_id, app_info.id, geo.country,event_date,user_first_touch_timestamp
) AS learner_cohort
left JOIN
(
  SELECT user_pseudo_id, event_date, level, level_success
  FROM
  (
    SELECT user_pseudo_id, event_date ,
    max(CASE WHEN params.key = 'level_number' THEN params.value.int_value END) as level,
    max(CASE WHEN params.key = 'success_or_failure' THEN params.value.string_value END )as level_success
    FROM 
    (
      SELECT * FROM `ftm-b9d99.analytics_159643920.events_20*`
      WHERE PARSE_DATE('%y%m%d', _table_suffix) BETWEEN '2021-01-01' AND CURRENT_DATE()
    ),
    UNNEST(event_params) AS params
    WHERE event_name LIKE 'level_completed'
    AND user_pseudo_id LIKE '942389096%'
    GROUP BY user_pseudo_id,event_date
  ) 
) 
AS level_data ON learner_cohort.user_pseudo_id = level_data.user_pseudo_id 
AND learner_cohort.event_date = level_data.event_date -- 新增的关联条件

关键修改点 #

• 在level_data子查询的外层SELECT中保留event_date字段（原代码遗漏，必须补充才能用于关联）
• JOIN条件新增learner_cohort.event_date = level_data.event_date，确保同用户同日期的记录唯一匹配，彻底避免笛卡尔积

修改后，两个子查询的2条记录会一一对应，最终返回你预期的2条结果。

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