基于JSON动态生成BigQuery CASE WHEN语句批量生成多指标变量

原生BigQuery实现（适配任意长度rules数组） #

核心逻辑是通过UNNEST展开JSON数组后做存在性判断，无需硬编码数组索引，适配任意长度的rules列表：

SELECT 
  id,
  -- interests指标
  CASE WHEN EXISTS(
    SELECT 1 
    FROM UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) AS rule
    WHERE JSON_EXTRACT_SCALAR(rule, '$.group') = 'interests'
  ) THEN 1 ELSE 0 END AS has_interests,
  -- age指标
  CASE WHEN EXISTS(
    SELECT 1 
    FROM UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) AS rule
    WHERE JSON_EXTRACT_SCALAR(rule, '$.group') = 'age'
  ) THEN 1 ELSE 0 END AS has_age,
  -- 其他group对应的指标按上述格式依次添加即可
  CASE WHEN EXISTS(
    SELECT 1 
    FROM UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) AS rule
    WHERE JSON_EXTRACT_SCALAR(rule, '$.group') = 'gender'
  ) THEN 1 ELSE 0 END AS has_gender
FROM `你的项目名.你的数据集名.你的表名`

Jinja简化实现（多指标批量生成） #

如果需要生成的指标数量较多，可通过Jinja循环批量生成代码，仅需维护目标group列表即可：

{% set target_groups = ['interests', 'age', 'gender', 'geog', 'o_version', 'o_version_min_group'] %}

SELECT 
  id,
  {% for group in target_groups %}
  CASE WHEN EXISTS(
    SELECT 1 
    FROM UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) AS rule
    WHERE JSON_EXTRACT_SCALAR(rule, '$.group') = '{{ group }}'
  ) THEN 1 ELSE 0 END AS has_{{ group }}{% if not loop.last %},{% endif %}
  {% endfor %}
FROM `你的项目名.你的数据集名.你的表名`

大数据量性能优化方案 #

数据量较大时可提前提取所有group值，避免多次UNNEST数组，性能提升更明显：

WITH all_rule_groups AS (
  SELECT 
    id,
    ARRAY_AGG(DISTINCT JSON_EXTRACT_SCALAR(rule, '$.group')) AS exist_groups
  FROM `你的项目名.你的数据集名.你的表名`,
  UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) AS rule
  GROUP BY id
)
SELECT 
  t.id,
  IF('interests' IN UNNEST(COALESCE(exist_groups, [])), 1, 0) AS has_interests,
  IF('age' IN UNNEST(COALESCE(exist_groups, [])), 1, 0) AS has_age,
  IF('gender' IN UNNEST(COALESCE(exist_groups, [])), 1, 0) AS has_gender
FROM `你的项目名.你的数据集名.你的表名` t
LEFT JOIN all_rule_groups rg ON t.id = rg.id

该方案同样可配合Jinja循环批量生成指标列。

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