如何在BigQuery/PostgreSQL中实现Unpivot、Split Column与Pivot表转换？

由于缺少源表和目标表的具体字段结构，以下基于Power Query中最常见的**宽表转窄表（逆透视列）和窄表转宽表（透视列）**场景给出通用解决方案，你可根据实际表结构调整。

一、宽表转窄表（Unpivot） #

适用于将一行中多列指标拆分为多行单指标的场景，对应Power Query的「逆透视列」操作。

BigQuery 实现 #

通过UNNEST结合STRUCT批量拆分列：

-- 示例：源表sales包含date、product_a_sales、product_b_sales字段
SELECT
  date,
  metric_name,
  metric_value
FROM
  `your_project.your_dataset.sales`,
  UNNEST([
    STRUCT('product_a_sales' AS metric_name, product_a_sales AS metric_value),
    STRUCT('product_b_sales' AS metric_name, product_b_sales AS metric_value)
    -- 按需添加其他需要逆透视的列
  ])

PostgreSQL 实现 #

通过UNNEST结合数组拆分列：

-- 示例：源表sales包含date、product_a_sales、product_b_sales字段
SELECT
  s.date,
  u.metric_name,
  u.metric_value
FROM
  sales s,
  UNNEST(ARRAY[
    ('product_a_sales', s.product_a_sales),
    ('product_b_sales', s.product_b_sales)
    -- 按需添加其他需要逆透视的列
  ]) u(metric_name, metric_value)

二、窄表转宽表（Pivot） #

适用于将多行单指标合并为一行多列的场景，对应Power Query的「透视列」操作。

BigQuery 实现 #

使用原生PIVOT关键字：

-- 示例：源表sales_details包含date、product_name、sales_value字段
SELECT
  *
FROM
  `your_project.your_dataset.sales_details`
PIVOT(
  SUM(sales_value) -- 根据需求选择聚合函数（SUM/AVG/MAX等）
  FOR product_name IN ('Product A', 'Product B') -- 指定需要转为列的字段值
)

PostgreSQL 实现 #

通过条件聚合模拟透视（PostgreSQL无原生PIVOT）：

-- 示例：源表sales_details包含date、product_name、sales_value字段
SELECT
  date,
  SUM(CASE WHEN product_name = 'Product A' THEN sales_value ELSE 0 END) AS product_a_sales,
  SUM(CASE WHEN product_name = 'Product B' THEN sales_value ELSE 0 END) AS product_b_sales
  -- 按需添加其他需要透视的维度值
FROM
  sales_details
GROUP BY
  date

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