Big Query中基于UNNEST关联Analytics数据的统计问题

我在BigQuery中有两张表：

Google Analytics事件表 #

销售表 #

我想要生成如下输出：

但我无法正确关联数据，以下是我尝试的代码：

with sales_totals as (
  select
  retailerId,
  sum(sale_amount) as earnings
  from `sales`
  group by 1
)

SELECT 
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'priority') as priority,
earnings
FROM `analytics.events_*` 
join sales_totals on sales_totals.retailerId = (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'retailerId')
where event_name = 'sale_event'
and retailerId is not null
group by 1, 2
order by 2 desc
LIMIT 1000

原代码存在几个核心问题：

1. 过滤条件歧义：WHERE子句中的retailerId属于销售表字段，无法直接用来过滤GA事件表中缺少对应参数的记录；
2. 嵌套参数处理冗余：关联条件中重复使用子查询提取参数，既影响性能，也可能因同一事件存在多同键参数导致匹配错误；
3. 聚合逻辑偏差：按priority和earnings分组无法实现按优先级汇总销售总额的需求，会出现重复统计。

修正后的SQL如下：

-- 计算每个零售商的累计销售额
WITH sales_totals AS (
  SELECT
    retailerId,
    SUM(sale_amount) AS total_sales
  FROM `sales`
  GROUP BY retailerId
),
-- 将GA事件表的嵌套参数转换为结构化字段，同时过滤无效记录
ga_events AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'retailerId') AS retailerId,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'priority') AS priority
  FROM `analytics.events_*`
  WHERE
    event_name = 'sale_event'
    -- 过滤存在retailerId参数的事件
    AND EXISTS (SELECT 1 FROM UNNEST(event_params) WHERE key = 'retailerId')
    -- 过滤存在priority参数的事件
    AND EXISTS (SELECT 1 FROM UNNEST(event_params) WHERE key = 'priority')
)
-- 关联表并按优先级汇总销售额
SELECT
  ge.priority,
  SUM(st.total_sales) AS sum_sales
FROM ga_events ge
JOIN sales_totals st ON ge.retailerId = st.retailerId
GROUP BY ge.priority
ORDER BY sum_sales DESC
LIMIT 1000;

关键优化点 #

• 新增ga_events公共表表达式，提前将嵌套的event_params提取为扁平化字段，同时过滤缺少关键参数的无效事件，让关联逻辑更清晰；
• 明确命名聚合字段语义，避免混淆；
• 最终按priority聚合，直接得到每个优先级对应的总销售额，匹配预期输出格式。

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