BigQuery查询Slot耗时超4天异常求助

你的查询性能极差的核心原因是低效的过滤逻辑、冗余的计算步骤以及不合理的关联方式，具体问题和优化点如下：

• 日期过滤逻辑冗余且时区不一致

  • 原removedCTE中用DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY)过滤日期，完全可以用_TABLE_SUFFIX直接限制扫描的表范围，避免全表扫描；同时这里使用默认时区的CURRENT_DATE()，但后续session_counts中用的是Asia/Karachi时区，可能导致扫描多余的表。
  • 优化：统一时区，直接用_TABLE_SUFFIX过滤日期范围，减少扫描的数据量。

• IN UNNEST(ARRAY_AGG(...))的低效关联

  • 这种写法会将removed中的用户ID先聚合为数组再展开，当用户数量较多时，会大幅增加查询的计算开销，远不如直接用JOIN或EXISTS高效。
  • 优化：改用JOIN关联removed表，或者用EXISTS子查询过滤用户。

• DISTINCT与窗口函数的冗余组合

  • 你的窗口函数按user_pseudo_id分区并按时间戳倒序取第一个值，每个用户只会返回一行有效数据，SELECT DISTINCT完全多余，反而会触发额外的去重计算，浪费资源。
  • 优化：去掉DISTINCT，或者改用ROW_NUMBER()取每个用户的最新事件后再提取参数。

• 窗口函数的计算范围过大

  • 原查询对用户的所有事件都应用窗口函数，再提取第一个值，不如先筛选出每个用户的最新事件，再从该事件中提取session参数，这样能减少窗口函数的计算量。
  • 优化：先按用户分组取最新的event_timestamp，再关联原表获取对应事件的参数。

优化后的查询示例 #

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
  (SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

-- 统一用Asia/Karachi时区计算日期范围
DECLARE start_date STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE('Asia/Karachi'), INTERVAL -30 DAY));
DECLARE end_date STRING DEFAULT FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Karachi'));

WITH removed AS (
  SELECT DISTINCT user_pseudo_id
  FROM `rayn-deen-app.analytics_317927526.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
    AND event_name = "app_remove"
    -- 用GA4自带的event_date字段过滤，避免时间戳转换开销
    AND event_date BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Asia/Karachi'), INTERVAL 30 DAY))
                       AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Asia/Karachi'), INTERVAL 1 DAY))
),
-- 先获取每个用户的最新事件时间戳
latest_user_events AS (
  SELECT
    user_pseudo_id,
    MAX(event_timestamp) AS latest_event_ts
  FROM `rayn-deen-app.analytics_317927526.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
    AND user_pseudo_id IN (SELECT user_pseudo_id FROM removed)
  GROUP BY user_pseudo_id
)
-- 关联原表获取最新事件的session参数
SELECT
  e.user_pseudo_id,
  GetParamValue(e.event_params, 'ga_session_id').int_value AS ga_session_id,
  GetParamValue(e.event_params, 'ga_session_number').int_value AS ga_session_number
FROM `rayn-deen-app.analytics_317927526.events_*` e
JOIN latest_user_events l
  ON e.user_pseudo_id = l.user_pseudo_id
  AND e.event_timestamp = l.latest_event_ts
WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date;

额外建议：

• 尽量使用GA4表自带的event_date字段（格式YYYYMMDD），避免手动转换event_timestamp来过滤日期，减少计算开销。
• 对于高频使用的参数提取逻辑，可以考虑构建预计算视图，进一步提升后续查询效率。

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