BigQuery中聚合函数、分析函数与子查询的最优方案对比

数据表结构 #

User表 #

Purchase表 #

需求 #

获取每个用户最后一次购买的状态，期望结果如下：

三种BigQuery查询方案 #

现有三种可得到相同结果的查询方案，以下从可读性、性能、成本维度分析最优选择：

聚合函数方案 #

SELECT 
  user_id,
  MAX(purchase_date) as last_purchase_date,
  ARRAY_AGG(status ORDER BY purchase_date DESC LIMIT 1)[SAFE_OFFSET(0)] as last_status
FROM user
LEFT JOIN purchase USING (user_id)
GROUP BY user_id

分析函数方案 #

SELECT
  DISTINCT
  user_id,
  MAX(purchase_date) OVER (PARTITION BY user_id) as last_purchase_date,
  LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY purchase_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_status,
FROM user
LEFT JOIN purchase USING (user_id)

子查询方案 #

SELECT 
  user_id,
  purchase_date as last_purchase_date,
  status as last_status
FROM user
LEFT JOIN purchase USING (user_id)
WHERE purchase_date IN (
  SELECT 
    MAX(purchase_date) as purchase_date
  FROM purchase
  GROUP BY user_id
)

测试数据集 #

WITH purchase as (
  SELECT 1 as purchase_id, "2020-01-01" as purchase_date, "sold" as status, "abc" as user_id
  UNION ALL SELECT 2 as purchase_id, "2020-02-01" as purchase_date, "refunded" as status, "abc" as user_id
  UNION ALL SELECT 3 as purchase_id, "2020-03-01" as purchase_date, "sold" as status, "def" as user_id
  UNION ALL SELECT 4 as purchase_id, "2020-04-01" as purchase_date, "sold" as status, "def" as user_id
  UNION ALL SELECT 5 as purchase_id, "2020-05-01" as purchase_date, "sold" as status, "def" as user_id
), user as (
    SELECT "abc" as user_id
    UNION ALL SELECT "def" as user_id
)

方案对比与最优结论 #

可读性 #

• 聚合函数方案：逻辑直观，通过分组+聚合直接锁定目标数据，ARRAY_AGG取最新状态的写法清晰，后续维护成本低。
• 分析函数方案：LAST_VALUE的窗口范围设置容易踩坑（默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，会导致结果错误），加上DISTINCT去重的操作略显生硬，可读性最差。
• 子查询方案：逻辑易懂，但嵌套结构不如聚合方案简洁，且存在隐含风险（若用户有多个相同最大日期的订单，会返回重复行）。

性能与成本（BigQuery环境） #

• 聚合函数方案：仅需一次关联+分组聚合，BigQuery优化器能高效处理带排序和限制的ARRAY_AGG操作，扫描数据量最少，成本最低。
• 分析函数方案：窗口函数会先对每个用户的所有行计算窗口结果，再通过DISTINCT去重，比聚合方案多了一次全量行处理，性能和成本略高。
• 子查询方案：需要先执行子查询获取每个用户的最新日期，再关联原表过滤，相当于两次扫描计算，在数据量大时性能和成本是三者中最高的。

最优选择 #

聚合函数方案是最优解，兼顾了可读性、性能和成本，逻辑严谨且不易出错。

内容的提问来源于stack exchange，提问作者L.GAYET