BigQuery查询执行资源超限问题及SQL优化求助

你遇到的这个内存超限问题，核心确实是那个无分区的全局窗口函数搞的鬼——SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) 会尝试对所有数据做全局排序和累加，数据量一大，内存直接就顶不住了。我给你调整了查询逻辑，既能保留原有的会话统计结果，又能大幅降低内存压力：

SELECT 
  event_date, 
  country, 
  COUNT(*) AS sessions, 
  AVG(length) AS average_session_length 
FROM ( 
  SELECT 
    country, 
    event_date, 
    user_pseudo_id || '-' || user_session_id AS global_session_id, -- 用用户ID+会话ID生成全局唯一标识
    (MAX(event_timestamp) - MIN(event_timestamp))/(60 * 1000 * 1000) AS length 
  FROM ( 
    SELECT 
      user_pseudo_id, 
      event_timestamp, 
      country, 
      event_date, 
      SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS user_session_id 
    FROM ( 
      SELECT *, 
        CASE 
          WHEN event_timestamp - last_event >= (30*60*1000*1000) OR last_event IS NULL THEN 1 
          ELSE 0 
        END AS is_new_session 
      FROM ( 
        SELECT 
          user_pseudo_id, 
          event_timestamp, 
          geo.country, 
          event_date, 
          LAG(event_timestamp,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_event 
        FROM `xxx.events*` 
      ) last 
    ) final 
  ) session 
  GROUP BY user_pseudo_id, user_session_id, country, event_date 
) agg 
WHERE length >= (10/60) 
GROUP BY country, event_date

关键优化点说明： #

• 砍掉全局窗口函数：原查询里的global_session_id依赖无分区窗口，这是内存爆炸的根源。我们改用user_pseudo_id加用户级的user_session_id拼接成全局唯一会话ID，效果和原逻辑完全一致，但不需要处理全量数据的全局排序。
• 拆分分组粒度：内层分组改为按user_pseudo_id和user_session_id分组，每个分组只处理单个用户的单个会话数据，内存负载被拆解得非常小，BigQuery能轻松处理。
• 保留核心业务逻辑：30分钟会话超时判定、会话时长计算这些核心逻辑全部保留，最终统计结果和原查询完全相同。

额外优化建议： #

• 如果不需要全量历史数据，建议在最内层查询加上WHERE event_date BETWEEN '起始日期' AND '结束日期'，进一步缩小处理的数据范围。
• 如果你的events*表是按event_date分区的，BigQuery会自动利用分区特性只扫描指定日期的数据，性能会更上一层楼。

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