BigQuery SQL中如何优雅创建临时变量？求更优实现方案

场景说明 #

BigQuery表每行包含Time和Data字段，Data为JSON字典，其中包含Table_ID字符串。现有查询需提取Table_ID和Q字段，并过滤Table_ID以AB或CD开头的行，但原始查询重复调用JSON函数，希望优化写法并提升性能。

原始可行查询 #

SELECT Time, JSON_VALUE(Data,"$.Table_ID") As Table_ID,
  JSON_QUERY(Data,"$.Q") From mytable where
  JSON_VALUE(Data,"$.Table_ID") LIKE "AB%" or 
    JSON_VALUE(Data,"$.Table_ID") LIKE "CD%"

期望的简洁写法（无效SQL） #

希望复用已定义的Table_ID别名做过滤，但BigQuery不支持在WHERE子句中直接引用SELECT子句的别名：

SELECT Time, JSON_VALUE(Data,"$.Table_ID") As Table_ID, JSON_QUERY(Data,"$.Q") 
From mytable where Table_ID LIKE "AB%" or Table_ID LIKE "CD%"

错误的CTE写法及问题 #

尝试仅提取Table_ID到CTE后与原表关联，导致全连接，触发表过大报错：

WITH CTE AS (SELECT JSON_VALUE(Data,"$.Table_ID") As Table_ID FROM mytable)
SELECT TIME, Table_ID, JSON_QUERY(Data,"$.Q") 
 FROM CTE, mytable WHERE Table_ID LIKE "AB%" or Table_ID LIKE "CD%"

正确但无性能提升的CTE写法 #

将整张表及计算后的Table_ID放入CTE，避免了连接，但性能与原查询相当：

WITH CTE AS
(SELECT Time, Data, JSON_VALUE(Data,"$.Table_ID") As Table_ID FROM mytable)
SELECT TIME, Table_ID, JSON_QUERY(Data,"$.Q") 
 FROM CTE WHERE Table_ID LIKE "AB%" or Table_ID LIKE "CD%"

优化方案 #

1. 简化过滤条件减少判断 #

使用REGEXP_LIKE合并两个LIKE条件，减少条件判断的执行次数：

WITH CTE AS (
  SELECT 
    Time, 
    Data, 
    JSON_VALUE(Data,"$.Table_ID") AS Table_ID 
  FROM mytable
)
SELECT 
  Time, 
  Table_ID, 
  JSON_QUERY(Data,"$.Q") 
FROM CTE 
WHERE REGEXP_LIKE(Table_ID, r'^(AB|CD)')

2. 创建物化视图预计算结构化字段 #

若该查询高频执行，创建物化视图预先提取JSON中的字段，避免每次查询重复解析JSON：

CREATE MATERIALIZED VIEW mytable_structured AS
SELECT 
  Time,
  JSON_VALUE(Data,"$.Table_ID") AS Table_ID,
  JSON_QUERY(Data,"$.Q") AS Q,
  Data  -- 保留原始JSON字段（按需选择）
FROM mytable

后续查询直接使用物化视图，性能会有显著提升：

SELECT Time, Table_ID, Q
FROM mytable_structured
WHERE Table_ID LIKE "AB%" OR Table_ID LIKE "CD%"

3. 一次性解析JSON对象 #

使用PARSE_JSON将Data解析为JSON对象后直接访问属性，减少重复解析的开销：

WITH CTE AS (
  SELECT 
    Time,
    PARSE_JSON(Data) AS parsed_data,
    JSON_VALUE(Data,"$.Table_ID") AS Table_ID
  FROM mytable
)
SELECT 
  Time,
  Table_ID,
  parsed_data.Q  -- 直接访问解析后的JSON属性
FROM CTE
WHERE Table_ID LIKE "AB%" OR Table_ID LIKE "CD%"

注：PARSE_JSON返回JSON对象，BigQuery会自动处理多数类型转换场景，若需显式转换可使用CAST。

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