GBQ中使用MAX() OVER(PARTITION BY)导致查询处理量激增问题排查

问题背景 #

用户在Google BigQuery中有如下源数据：

SELECT 1 AS client_id, CAST('2022-10-13' AS DATE) AS session_date, 'denied' AS value
UNION ALL
SELECT 1, CAST('2022-10-15' AS DATE), 'granted'
UNION ALL
SELECT 1, CAST('2022-10-18' AS DATE), 'denied'
UNION ALL
SELECT 2, CAST('2022-01-01' AS DATE), 'denied'
UNION ALL
SELECT 2, CAST('2022-01-05' AS DATE), 'granted'
UNION ALL
SELECT 3, CAST('2022-01-01' AS DATE), 'granted'
UNION ALL
SELECT 4, CAST('2022-01-03' AS DATE), 'granted'

需求是生成包含每个client_id的last_activity（最后活动日期），以及存储所有session_date和对应value的STRUCT数组push_permission的结果表。

用户使用以下SQL实现需求，但执行后查询处理量从预期的11GB飙升至1.2TB，此前单独使用MAX() OVER(PARTITION BY)未出现该情况：

WITH max_date AS (
      SELECT
        client_id,
        session_date,
        value,
        MAX(session_date) OVER (PARTITION BY client_id) AS last_activity
      FROM
        source_data )
    SELECT
      client_id,
      last_activity,
      ARRAY_AGG(STRUCT(session_date,
          value)
      ORDER BY
        session_date) AS push_permission
    FROM
      max_date
    GROUP BY
      1,
      2

排查原因 #

核心问题在于窗口函数与后续GROUP BY的组合逻辑导致数据冗余处理：

• 窗口函数MAX(session_date) OVER(PARTITION BY client_id)会为源表的每一行都添加last_activity字段，中间表max_date的行数与源表完全一致，没有任何数据聚合。
• 后续的GROUP BY client_id, last_activity触发了全量数据的shuffle操作——因为ARRAY_AGG需要收集每个分组下的所有session_date和value。此时BigQuery需要处理源表的全部数据，且中间表的冗余字段（重复的last_activity）也增加了数据传输量，最终导致处理量暴增。
• 此前单独使用窗口函数时，仅为每行添加字段，没有后续的全量聚合操作，因此处理量正常。

优化方案 #

方案一：单次分组聚合完成所有需求（最优） #

直接在一次GROUP BY client_id中同时计算last_activity和push_permission，避免中间表的冗余数据：

SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date, value) ORDER BY session_date) AS push_permission
FROM source_data
GROUP BY client_id

该方案将两个计算逻辑合并，所有操作都基于源表的分组聚合，处理量会回归到预期的11GB级别，执行效率最高。

方案二：拆分计算逻辑（适用于复杂场景） #

如果业务逻辑需要拆分计算步骤，可以先分别计算每个client_id的last_activity和push_permission，再通过关联合并结果：

WITH client_last_date AS (
  SELECT
    client_id,
    MAX(session_date) AS last_activity
  FROM source_data
  GROUP BY client_id
),
client_permissions AS (
  SELECT
    client_id,
    ARRAY_AGG(STRUCT(session_date, value) ORDER BY session_date) AS push_permission
  FROM source_data
  GROUP BY client_id
)
SELECT
  c.client_id,
  cl.last_activity,
  c.push_permission
FROM client_permissions c
JOIN client_last_date cl ON c.client_id = cl.client_id

这种方式将计算拆分为两个独立的分组聚合，每个步骤仅处理必要数据，避免了窗口函数带来的全量数据冗余。

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