如何在Google BigQuery中用SQL查询events_*与events_intraday_*表

要同时从events_*和events_intraday_*两类表中提取数据，正确方式是用UNION ALL合并两个表的查询结果（两类表结构完全一致），而非尝试交叉连接或关联。以下是可用的查询语句：

SELECT
  user_id as user_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  experiment_id_param.value.string_value AS experiment_id,
  variation_id_param.value.int_value AS variation_id,
  geo.country as country,
  traffic_source.source as source,
  traffic_source.medium as medium,
  device.category as device,
  device.web_info.browser as browser,
  device.operating_system as os
FROM
  `analytics_xxx.events_intraday_*`,
  UNNEST(event_params) AS experiment_id_param,
  UNNEST(event_params) AS variation_id_param
WHERE
  _TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
  AND event_name = 'experiment_viewed'  
  AND experiment_id_param.key = 'experiment_id'
  AND variation_id_param.key = 'variation_id'
  AND user_id is not null

UNION ALL

SELECT
  user_id as user_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  experiment_id_param.value.string_value AS experiment_id,
  variation_id_param.value.int_value AS variation_id,
  geo.country as country,
  traffic_source.source as source,
  traffic_source.medium as medium,
  device.category as device,
  device.web_info.browser as browser,
  device.operating_system as os
FROM
  `analytics_xxx.events_*`,
  UNNEST(event_params) AS experiment_id_param,
  UNNEST(event_params) AS variation_id_param
WHERE
  _TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
  AND event_name = 'experiment_viewed'  
  AND experiment_id_param.key = 'experiment_id'
  AND variation_id_param.key = 'variation_id'
  AND user_id is not null

之前尝试的错误原因 #

• 变体1、2：用逗号同时列出两个表属于交叉连接（笛卡尔积），会导致event_params等列名歧义（两个表都包含这些列，BigQuery无法确定引用哪一个），同时这种方式会生成大量无效的交叉数据，完全不符合合并表数据的需求。
• 变体3、4：错误地尝试用子查询分组后连接，一方面语法不符合BigQuery要求（子查询内用逗号分隔表和UNNEST的写法不能直接作为JOIN的对象），另一方面JOIN是用于关联两个表的关联数据，而非合并同结构表的全部数据，逻辑方向错误。

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