如何在Google BigQuery中使用SQL转置数据？动态日期处理方案

一、已知日期时使用PIVOT转置 #

BigQuery的PIVOT语法必须配合聚合函数使用，这是转置逻辑的要求——需要对同一分组下的数据做聚合计算。针对你的场景，每个timestamp_30seconds和date的组合仅对应一个profit_loss_pct值，因此用MAX()或MIN()这类聚合函数就能保留原始值（单个值的聚合结果就是自身）。

示例代码 #

WITH sample_data AS (
  SELECT '2023-09-05' AS date, '14:46:30' AS timestamp_30seconds, -0.05 AS profit_loss_pct UNION ALL
  SELECT '2023-09-06' AS date, '14:46:30' AS timestamp_30seconds, -0.06 AS profit_loss_pct UNION ALL
  SELECT '2023-09-07' AS date, '14:46:30' AS timestamp_30seconds, -0.04 AS profit_loss_pct UNION ALL
  SELECT '2023-09-05' AS date, '14:47:00' AS timestamp_30seconds, -0.05 AS profit_loss_pct UNION ALL
  SELECT '2023-09-06' AS date, '14:47:00' AS timestamp_30seconds, -0.04 AS profit_loss_pct UNION ALL
  SELECT '2023-09-07' AS date, '14:47:00' AS timestamp_30seconds, -0.06 AS profit_loss_pct
)
SELECT 
  timestamp_30seconds AS timestamp,
  `2023-09-05`,
  `2023-09-06`,
  `2023-09-07`
FROM sample_data
PIVOT (
  MAX(profit_loss_pct) -- 单个值聚合，MAX/MIN效果一致
  FOR date IN ('2023-09-05', '2023-09-06', '2023-09-07')
)

关键说明 #

• PIVOT子句中，FOR date IN (...)指定要转置为列的原始字段值；
• 由于日期字符串包含-，查询结果中的列名需要用反引号（`）包裹，避免语法错误；
• 选择MAX()是因为每个分组仅一条数据，不会改变原始值。

二、未知日期时使用EXECUTE IMMEDIATE动态转置 #

如果日期是动态变化的（无法提前枚举），可以用EXECUTE IMMEDIATE生成动态SQL实现转置，步骤分为：

1. 提取所有唯一日期并拼接成符合语法的列名列表；
2. 动态构建PIVOT语句并执行。

示例代码 #

DECLARE date_columns STRING;

-- 从目标表中获取所有唯一日期，拼接成带反引号的列名字符串
SET date_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('`', date, '`'), ', ')
  FROM `your-project.your-dataset.your-table` -- 替换为你的实际表路径
);

-- 动态生成并执行转置SQL
EXECUTE IMMEDIATE FORMAT("""
  SELECT 
    timestamp_30seconds AS timestamp,
    %s
  FROM `your-project.your-dataset.your-table`
  PIVOT (
    MAX(profit_loss_pct)
    FOR date IN (%s)
  )
""", date_columns, REPLACE(date_columns, '`', ''));

关键说明 #

• STRING_AGG()用于将所有日期拼接成逗号分隔的列名列表，CONCAT('', date, '')为列名添加反引号，适配含特殊字符的日期；
• FORMAT()函数将拼接好的列名插入到动态SQL模板中；
• REPLACE(date_columns, '', '')用于去除IN子句中日期值的反引号，因为IN`里的是原始字段值，不需要反引号。

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