基于用户行数据动态创建列的BigQuery SQL实现

针对你需要将含Color的调研问题动态转为列，以Salary为主维度的需求，提供两种BigQuery SQL方案：静态固定列和动态自动识别列的版本。

场景假设 #

假设你的原始调研表survey_data是宽表结构，每行对应一位受访者，包含列：Eye_Color、Hair_Color、Salary（若原始是长表结构，可先转宽表后再用以下方案）。

方案1：静态转列（已知所有Color列） #

如果确定只有Eye_Color和Hair_Color两个颜色相关列，可直接写静态SQL，手动指定需要聚合的颜色值：

SELECT
  Salary,
  -- 统计各眼睛颜色的受访者数量
  SUM(IF(Eye_Color = 'Blue', 1, 0)) AS Eye_Color_Blue,
  SUM(IF(Eye_Color = 'Green', 1, 0)) AS Eye_Color_Green,
  SUM(IF(Eye_Color = 'Brown', 1, 0)) AS Eye_Color_Brown,
  -- 统计各头发颜色的受访者数量
  SUM(IF(Hair_Color = 'Black', 1, 0)) AS Hair_Color_Black,
  SUM(IF(Hair_Color = 'Brown', 1, 0)) AS Hair_Color_Brown,
  SUM(IF(Hair_Color = 'Blonde', 1, 0)) AS Hair_Color_Blonde
FROM `your-project.your-dataset.survey_data`
GROUP BY Salary
ORDER BY Salary

说明 #

• 替换your-project.your-dataset.survey_data为你的实际项目、数据集和表名
• 可根据实际存在的颜色值增减IF语句
• 若需按薪资区间聚合（如每10000为一档），将Salary替换为FLOOR(Salary / 10000) * 10000 AS Salary_Range即可

方案2：动态转列（自动识别所有Color列） #

如果后续可能新增Skin_Color等含Color的列，或者颜色值较多不想手动维护，可使用BigQuery的动态SQL（EXECUTE IMMEDIATE）自动识别并转列：

DECLARE pivot_columns STRING;

-- 生成所有需要转成列的颜色类别（如Eye_Color_Blue、Hair_Color_Black）
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('"', column_name, '_', value, '"'), ', ')
  FROM (
    SELECT column_name, value
    FROM `your-project.your-dataset.survey_data`
    UNPIVOT (value FOR column_name IN (
      -- 动态获取所有含Color的列
      (SELECT STRING_AGG(DISTINCT column_name, ', ') FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'survey_data' AND column_name LIKE '%Color%')
    ))
    WHERE column_name LIKE '%Color%'
  )
);

-- 执行动态PIVOT查询
EXECUTE IMMEDIATE FORMAT("""
SELECT
  Salary,
  %s
FROM (
  SELECT
    Salary,
    CONCAT(column_name, '_', value) AS color_category,
    1 AS count
  FROM `your-project.your-dataset.survey_data`
  UNPIVOT (value FOR column_name IN (%s))
  WHERE column_name LIKE '%%Color%%'
)
PIVOT (
  SUM(count) FOR color_category IN (%s)
)
ORDER BY Salary
""", pivot_columns, 
(SELECT STRING_AGG(DISTINCT column_name, ', ') FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'survey_data' AND column_name LIKE '%Color%'),
pivot_columns);

说明 #

1. 首先通过查询INFORMATION_SCHEMA.COLUMNS自动识别表中所有含Color的列
2. 生成所有颜色类别（列名+颜色值）作为PIVOT的目标列
3. 动态执行PIVOT，自动统计每个Salary对应的各颜色类别受访者数量
4. 新增含Color的列后无需修改SQL，会自动纳入统计

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