如何编写BigQuery查询关联两类不同参数事件的用户数据

你已经成功拿到提交反馈的用户列表啦，接下来只需要把这部分用户和他们的汉堡选择事件做关联就行。核心思路是把两个事件分别整理成结构化的子查询，再通过user_id和统一的时间区间来关联，确保拿到的是同一时间段内的用户行为数据。

完整SQL语句 #

WITH feedback_users AS (
  SELECT 
    user_id,
    event_name AS event_name_1,
    param.value.string_value AS rating
  FROM `table.events_*`, UNNEST(event_params) AS param 
  WHERE 
    event_name = "clicked_on_feedback" 
    AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)) 
    AND param.key = "rating"
    -- 要是你只想筛选负面评分（比如1、2分），可以加上下面这行
    -- AND param.value.string_value IN ('1', '2')
),
burger_choices AS (
  SELECT 
    user_id,
    event_name AS event_name_2,
    param.value.string_value AS burger_type
  FROM `table.events_*`, UNNEST(event_params) AS param 
  WHERE 
    event_name = "burger_chosen" 
    AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)) 
    AND param.key = "burger_type"
)
SELECT 
  fu.user_id,
  fu.event_name_1,
  fu.rating,
  bc.event_name_2,
  bc.burger_type
FROM feedback_users fu
LEFT JOIN burger_choices bc 
  ON fu.user_id = bc.user_id
-- 要是你只需要同时完成两个动作的用户，把LEFT JOIN改成INNER JOIN就行
ORDER BY fu.user_id;

代码逻辑解释 #

1. 拆分行为数据为独立数据集：
   • feedback_users：复用你原来的查询逻辑，把提交反馈的用户数据整理成带user_id、反馈事件名和评分的结构化数据。
   • burger_choices：用同样的方式提取用户选择汉堡的行为，整理成带user_id、汉堡事件名和汉堡类型的数据集。
2. 关联两个行为数据集：
   • 用LEFT JOIN可以保留所有提交反馈的用户，哪怕他们没选汉堡（此时汉堡相关字段会显示为NULL）；如果只需要同时完成两个动作的用户，换成INNER JOIN即可。
3. 统一时间范围：两个子查询都用了相同的时间过滤条件，确保关联的是同一10天内的用户行为。

预期输出 #

执行后就能得到你想要的合并结果：

如果有用户只提交了反馈没选汉堡，event_name_2和burger_type会显示为NULL，你可以根据实际需求调整JOIN类型来过滤这类用户。

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