如何在Google BigQuery计划查询中无需手动输入列名实现透视表

解决方案代码 #

DECLARE pivot_columns STRING;

-- 动态获取所有唯一category，生成透视列列表
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('`', category, '`'), ', ')
  FROM (
    SELECT DATE '2023-05-01' AS event_date, 'action_CN' AS category, 'A' AS user_id UNION ALL
    SELECT DATE '2023-05-01', 'action_EN', 'B' UNION ALL
    SELECT DATE '2023-05-02', 'action_JP', 'A' UNION ALL
    SELECT DATE '2023-05-02', 'action_KO', 'B' UNION ALL
    SELECT DATE '2023-05-02', 'action_OTHER', 'D' UNION ALL
    SELECT DATE '2023-05-02', 'adventure_CN', 'D' UNION ALL
    SELECT DATE '2023-05-02', 'action_CN', 'D' UNION ALL
    SELECT DATE '2023-05-03', 'action_EN', 'A' UNION ALL
    SELECT DATE '2023-05-03', 'action_JP', 'B' UNION ALL
    SELECT DATE '2023-05-03', 'action_EN', 'C' UNION ALL
    SELECT DATE '2023-05-03', 'action_JP', 'D' UNION ALL
    SELECT DATE '2023-05-03', 'action_EN', 'D' UNION ALL
    SELECT DATE '2023-05-03', 'action_JP', 'D' UNION ALL
    SELECT DATE '2023-05-04', 'action_EN', 'A' UNION ALL
    SELECT DATE '2023-05-04', 'action_JP', 'C' UNION ALL
    SELECT DATE '2023-05-05', 'action_EN', 'A' UNION ALL
    SELECT DATE '2023-05-05', 'action_JP', 'C' UNION ALL
    SELECT DATE '2023-05-06', 'action_EN', 'A' UNION ALL
    SELECT DATE '2023-05-06', 'action_JP', 'C'
  )
);

-- 动态执行透视查询
EXECUTE IMMEDIATE CONCAT('
WITH data AS (
  SELECT DATE ''2023-05-01'' AS event_date, ''action_CN'' AS category, ''A'' AS user_id UNION ALL
  SELECT DATE ''2023-05-01'', ''action_EN'', ''B'' UNION ALL
  SELECT DATE ''2023-05-02'', ''action_JP'', ''A'' UNION ALL
  SELECT DATE ''2023-05-02'', ''action_KO'', ''B'' UNION ALL
  SELECT DATE ''2023-05-02'', ''action_OTHER'', ''D'' UNION ALL
  SELECT DATE ''2023-05-02'', ''adventure_CN'', ''D'' UNION ALL
  SELECT DATE ''2023-05-02'', ''action_CN'', ''D'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_EN'', ''A'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_JP'', ''B'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_EN'', ''C'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_JP'', ''D'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_EN'', ''D'' UNION ALL
  SELECT DATE ''2023-05-03'', ''action_JP'', ''D'' UNION ALL
  SELECT DATE ''2023-05-04'', ''action_EN'', ''A'' UNION ALL
  SELECT DATE ''2023-05-04'', ''action_JP'', ''C'' UNION ALL
  SELECT DATE ''2023-05-05'', ''action_EN'', ''A'' UNION ALL
  SELECT DATE ''2023-05-05'', ''action_JP'', ''C'' UNION ALL
  SELECT DATE ''2023-05-06'', ''action_EN'', ''A'' UNION ALL
  SELECT DATE ''2023-05-06'', ''action_JP'', ''C''
),
aggregated AS (
  SELECT user_id, category, COUNT(DISTINCT event_date) AS date_count
  FROM data
  GROUP BY user_id, category
)
SELECT *
FROM aggregated
PIVOT (
  MAX(date_count) FOR category IN (', pivot_columns, ')
)
ORDER BY user_id;
');

关键说明 #

• 绕开计划查询变量限制：BigQuery计划查询不支持直接用DECLARE/SET变量替换静态SQL中的列名，使用EXECUTE IMMEDIATE动态构建SQL字符串可解决该问题。
• 动态列名生成：通过STRING_AGG将所有唯一的category值拼接成符合PIVOT语法的列列表，用反引号包裹列名避免特殊字符冲突。
• 聚合逻辑：先按user_id和category分组，统计每个组合下不同event_date的数量，再通过PIVOT转成列。
• 空值处理：PIVOT后无数据的列会显示为NULL，与期望结果一致。

生产环境适配 #

如果数据来自实际表而非CTE，只需修改两处：

1. 生成pivot_columns的子查询替换为你的表：

   SET pivot_columns = (
     SELECT STRING_AGG(DISTINCT CONCAT('`', category, '`'), ', ')
     FROM `your-project.your-dataset.your-table`
   );
   

2. 将EXECUTE IMMEDIATE中的CTE部分替换为表引用：

   aggregated AS (
     SELECT user_id, category, COUNT(DISTINCT event_date) AS date_count
     FROM `your-project.your-dataset.your-table`
     GROUP BY user_id, category
   )
   

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