PIVOT 语句详解

功能概述

PIVOT 用于将行数据转成列数据。它接受表、CTE 或子查询作为输入，按照 IN (...) 中列出的取值生成输出列，并对对应的值列执行聚合。

这适合把长表结果改造成宽表结果，方便横向对比。

基本语法

SELECT ...
FROM <input>
PIVOT (
  <aggregate_function>(<value_column>)
  FOR <pivot_column> IN (<pivot_value_1>, <pivot_value_2>, ...)
  [ DEFAULT ON NULL (<default_value>) ]
) [ AS <alias> (<output_column_1>, <output_column_2>, ...) ]

• <input>：输入数据，可以是基础表、CTE 或子查询。
• <aggregate_function>：聚合函数，如 SUM、MAX、MIN、AVG、COUNT。
• <value_column>：传给聚合函数的值列。
• <pivot_column>：用于展开成列的参考列。
• <pivot_value_n>：静态列值列表，每个值对应一个输出列。
• DEFAULT ON NULL (<default_value>)：将透视后的 NULL 结果替换为默认值。
• AS <alias> (...)：给透视后的关系和输出列重命名。

注意事项

• 当前只支持单个 pivot_column。
• IN (...) 中必须是静态值列表，不支持动态列值。
• 输出会保留输入中的其他列，但不会保留 pivot_column 和聚合使用的值列。
• 如果同一个分组内某个 pivot 值对应多行数据，会先对这些行执行聚合。

示例数据

CREATE TABLE quarterly_sales (
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  empid INT32,
  amount INT32,
  quarter STRING,
  timestamp key(ts)
)
PARTITION BY HASH (amount) PARTITIONS 1;

INSERT INTO quarterly_sales (ts, empid, amount, quarter) VALUES
  ('2025-06-20 10:00:00Z', 1, 11000, '2023_Q1'),
  ('2025-06-20 10:00:00Z', 1, 400, '2023_Q1'),
  ('2025-06-20 10:00:00Z', 2, 4600, '2023_Q1'),
  ('2025-06-20 10:00:00Z', 2, 35000, '2023_Q1'),
  ('2025-06-20 10:00:00Z', 1, 5100, '2023_Q2'),
  ('2025-06-20 10:00:00Z', 1, 3000, '2023_Q2'),
  ('2025-06-20 10:00:00Z', 2, 200, '2023_Q2'),
  ('2025-06-20 10:00:00Z', 2, 90500, '2023_Q2'),
  ('2025-06-20 10:00:00Z', 1, 6000, '2023_Q3'),
  ('2025-06-20 10:00:00Z', 1, 5000, '2023_Q3'),
  ('2025-06-20 10:00:00Z', 2, 2500, '2023_Q3'),
  ('2025-06-20 10:00:00Z', 2, 9500, '2023_Q3'),
  ('2025-06-20 10:00:00Z', 3, 2800, '2023_Q3'),
  ('2025-06-20 10:00:00Z', 1, 8000, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 1, 10000, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 2, 800, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 2, 4500, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 3, 2700, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 3, 16000, '2023_Q4'),
  ('2025-06-20 10:00:00Z', 3, 10200, '2023_Q4');

基础用法

SELECT *
FROM quarterly_sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('2023_Q1', '2023_Q2', '2023_Q3')
)
ORDER BY empid;

+---------------------------+-------+---------+---------+---------+
| ts                        | empid | 2023_Q1 | 2023_Q2 | 2023_Q3 |
+---------------------------+-------+---------+---------+---------+
| 2025-06-20T18:00:00+08:00 | 1     | 11400   | 8100    | 11000   |
| 2025-06-20T18:00:00+08:00 | 2     | 39600   | 90700   | 12000   |
| 2025-06-20T18:00:00+08:00 | 3     |         |         | 2800    |
+---------------------------+-------+---------+---------+---------+

使用默认值替换 NULL

SELECT *
FROM quarterly_sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('2023_Q1', '2023_Q2', '2023_Q3')
  DEFAULT ON NULL (0)
)
ORDER BY empid;

+---------------------------+-------+---------+---------+---------+
| ts                        | empid | 2023_Q1 | 2023_Q2 | 2023_Q3 |
+---------------------------+-------+---------+---------+---------+
| 2025-06-20T18:00:00+08:00 | 1     | 11400   | 8100    | 11000   |
| 2025-06-20T18:00:00+08:00 | 2     | 39600   | 90700   | 12000   |
| 2025-06-20T18:00:00+08:00 | 3     | 0       | 0       | 2800    |
+---------------------------+-------+---------+---------+---------+

重命名输出列

SELECT *
FROM quarterly_sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('2023_Q1', '2023_Q2', '2023_Q3', '2023_Q4')
) AS p (ts, employee, q1, q2, q3, q4)
ORDER BY employee;

+---------------------------+----------+-------+-------+-------+-------+
| ts                        | employee | q1    | q2    | q3    | q4    |
+---------------------------+----------+-------+-------+-------+-------+
| 2025-06-20T18:00:00+08:00 | 1        | 11400 | 8100  | 11000 | 18000 |
| 2025-06-20T18:00:00+08:00 | 2        | 39600 | 90700 | 12000 | 5300  |
| 2025-06-20T18:00:00+08:00 | 3        |       |       | 2800  | 28900 |
+---------------------------+----------+-------+-------+-------+-------+

使用 CTE 或子查询作为输入

可以先过滤或预处理数据，再做透视。

WITH filtered_sales AS (
  SELECT empid, amount, quarter
  FROM quarterly_sales
  WHERE amount > 1000
)
SELECT *
FROM filtered_sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('2023_Q1', '2023_Q2', '2023_Q3')
)
ORDER BY empid;

+-------+---------+---------+---------+
| empid | 2023_Q1 | 2023_Q2 | 2023_Q3 |
+-------+---------+---------+---------+
| 1     | 11000   | 8100    | 11000   |
| 2     | 39600   | 90500   | 12000   |
| 3     |         |         | 2800    |
+-------+---------+---------+---------+