在BigQuery中按表内计数器迭代记录并拆分数据的查询方案

常见场景1：行转列（PIVOT） #

如果输入表是长表结构（同一维度数据分散在多行），需要转换为宽表结构（维度字段转为列），可以使用BigQuery的PIVOT语法。

假设输入表结构（对应截图1）：

目标输出表结构（对应截图2）：

对应的查询语句：

SELECT *
FROM (
  -- 子查询提取需要转换的核心字段
  SELECT user_id, month, sales
  FROM `your-project.your-dataset.input_table`
)
PIVOT (
  -- 对需要聚合的字段指定函数（这里用SUM，也可替换为MAX/AVG等）
  SUM(sales) AS sales
  -- 指定要转成列的字段及其别名
  FOR month IN ('Jan' AS Jan, 'Feb' AS Feb)
)

常见场景2：列转行（UNPIVOT） #

如果输入表是宽表结构，需要转换为长表结构，使用UNPIVOT语法。

假设输入表结构（对应截图1）：

目标输出表结构（对应截图2）：

对应的查询语句：

SELECT user_id, month, sales
FROM `your-project.your-dataset.input_table`
UNPIVOT (
  -- 指定要展开的字段值
  sales FOR month IN (Jan_sales AS 'Jan', Feb_sales AS 'Feb')
)

其他复杂转换场景 #

如果需求涉及字段拆分、多维度聚合或字符串拼接，可结合以下函数实现：

• 字段拆分：用SPLIT()、REGEXP_EXTRACT()提取特定内容
• 聚合拼接：用STRING_AGG()（字符串）、ARRAY_AGG()（数组）合并多行数据
• 格式转换：用DATE_FORMAT()、PARSE_DATE()调整日期或字符串格式

示例：拆分含分隔符的字段

SELECT
  user_id,
  -- 拆分`user_info`字段（格式如"id:1,name:Alice"）
  REGEXP_EXTRACT(user_info, r'id:(\d+)') AS user_id_extracted,
  REGEXP_EXTRACT(user_info, r'name:(\w+)') AS user_name,
  sales
FROM `your-project.your-dataset.input_table`

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