BigQuery中session number异常：修改官方示例代码后报语法错误

错误原因 #

BigQuery要求DECLARE语句和临时函数定义必须放在整个查询的最开头，不能嵌套在CTE（WITH子句定义的部分）内部。你将DECLARE放在了session_counts这个CTE的内部，违反了语法规则，导致报错。同时，试图用DECLARE USER_PSEUDO_ID_LIST ARRAY<STRING> DEFAULT select * from removed;直接从CTE获取数组值的写法也无效，因为DECLARE的默认值无法引用后续定义的CTE。

修改后的正确代码 #

-- 先声明时区变量和创建临时函数
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'Asia/Karachi';

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

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
  (SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

with removed as (
  SELECT distinct user_pseudo_id as removed_users
  from `rayn-deen-app.analytics_317927526.events_*`
  where event_name = "app_remove"
    AND DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) 
        between DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        and DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),
session_counts as (
  SELECT DISTINCT
    user_pseudo_id,
    FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
      OVER (UserWindow) AS ga_session_id,
    FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
      OVER (UserWindow) AS ga_session_number
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
  WHERE
    -- 直接关联removed CTE获取目标用户列表，替代原数组变量逻辑
    user_pseudo_id IN (SELECT removed_users FROM removed)
    AND RIGHT(_TABLE_SUFFIX, 8)
      BETWEEN GetDateSuffix(-30, REPORTING_TIMEZONE)
      AND GetDateSuffix(0, REPORTING_TIMEZONE)
  WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC)
)

select * from session_counts

修改说明 #

• 将DECLARE和临时函数定义移至整个查询的最开头，符合BigQuery语法要求
• 移除无效的USER_PSEUDO_ID_LIST数组变量，改用user_pseudo_id IN (SELECT removed_users FROM removed)直接关联CTE获取目标用户，逻辑更简洁且避免语法问题
• 保留了你原有的时区设置、30天时间范围以及目标数据表的修改

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