如何在BigQuery中同时查询并返回session id与order id？

问题描述 #

需要在单个BigQuery查询中同时返回session id、order id及对应的plan字段，尝试整合两个独立查询后语句无法正常运行。

错误原因分析 #

原查询存在两个核心问题：

• 引用tbl.event_params但未给主表定义tbl别名，且event_params是重复字段，需要通过UNNEST展开后才能查询其中的key值
• items字段的引用未明确关联主表，可能导致字段解析错误

修正后的查询语句 #

基础修正版 #

SELECT 
  DISTINCT
  CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(tbl.event_params) WHERE key = 'ga_session_id') AS STRING)) AS sessionId,
  ecommerce.transaction_id AS orderId,
  (SELECT value.string_value FROM UNNEST(items.item_params) ep WHERE key = 'dimension') AS plan
FROM `mytable` AS tbl, UNNEST(tbl.items) AS items
WHERE ecommerce.transaction_id IS NOT NULL -- 可选：过滤无交易记录的数据

增强容错版（避免转换/空值报错） #

SELECT 
  DISTINCT
  CONCAT(user_pseudo_id, COALESCE(SAFE_CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING), '')) AS sessionId,
  ecommerce.transaction_id AS orderId,
  COALESCE((SELECT value.string_value FROM UNNEST(items.item_params) WHERE key = 'dimension'), 'N/A') AS plan
FROM `mytable`, UNNEST(items) AS items
WHERE ecommerce.transaction_id IS NOT NULL

关键修正点说明 #

1. 表别名与字段引用：给主表添加tbl别名，明确通过UNNEST(tbl.event_params)展开会话参数字段，确保子查询能正确定位到ga_session_id
2. 空值与转换容错：使用SAFE_CAST避免整数转字符串时的报错，COALESCE将空值替换为默认值（空字符串或N/A），保证结果字段完整性
3. 过滤无效数据：添加WHERE ecommerce.transaction_id IS NOT NULL，只保留有交易ID的记录，避免无意义的空值结果

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