PIVOT 语句允许将列中的不同值分离为各自的独立列。这些新列中的值是通过对匹配每个不同值的行子集应用聚合函数计算得出的。

DuckDB 既实现了 SQL 标准的 PIVOT 语法，也实现了一种简化的 PIVOT 语法，该语法在透视时会自动检测需要创建的列。PIVOT_WIDER 也可以用来代替 PIVOT 关键字。

关于 PIVOT 语句实现的详细信息，请参阅 Pivot 内部原理页面。

UNPIVOT 语句是 PIVOT 语句的逆运算。

简化版 PIVOT 语法

完整语法图如下所示，但简化版 PIVOT 语法可以归纳为使用电子表格数据透视表的命名惯例：

PIVOT dataset
ON columns
USING values
GROUP BY rows
ORDER BY columns_with_order_directions
LIMIT number_of_rows;

ON、USING 和 GROUP BY 子句都是可选的，但不能全部省略。

示例数据

所有示例均使用以下查询生成的数据集

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

SELECT *
FROM cities;

PIVOT ON 和 USING

使用下面的 PIVOT 语句为每一年创建一个单独的列，并计算每年的总人口。ON 子句指定要拆分为独立列的列。这等同于电子表格数据透视表中的列参数。

USING 子句确定如何聚合拆分到独立列中的值。这等同于电子表格数据透视表中的值参数。如果不包含 USING 子句，则默认为 count(*)。

PIVOT cities
ON year
USING sum(population);

在上面的示例中，sum 聚合始终作用于单个值。如果我们只想更改数据的显示方向而不进行聚合，请使用 first 聚合函数。在此示例中，我们透视的是数值，但 first 函数对于透视文本列也非常有效。（这在电子表格数据透视表中很难做到，但在 DuckDB 中却很容易！）

此查询产生的结果与上述结果完全相同

PIVOT cities
ON year
USING first(population);

注意：SQL 语法允许在 USING 子句的聚合函数中使用 FILTER 子句。在 DuckDB 中，PIVOT 语句目前不支持这些子句，它们会被静默忽略。

PIVOT ON、USING 和 GROUP BY

默认情况下，PIVOT 语句保留所有未在 ON 或 USING 子句中指定的列。若要仅包含特定列并进行进一步聚合，请在 GROUP BY 子句中指定列。这等同于电子表格数据透视表中的行参数。

在下面的示例中，输出中不再包含 name 列，数据被聚合到了 country 级别。

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

ON 子句的 IN 过滤器

若要仅为 ON 子句中列内的特定值创建独立列，请使用可选的 IN 表达式。例如，假设我们出于某种原因想忽略 2020 年……

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;

每个子句支持多个表达式

ON 和 GROUP BY 子句中可以指定多个列，USING 子句中也可以包含多个聚合表达式。

多个 ON 列和 ON 表达式

可以将多个列透视为各自的独立列。DuckDB 会找到每个 ON 子句列中的不同值，并为这些值的所有组合（笛卡尔积）创建一个新列。

在下面的示例中，国家和城市的所有唯一组合都会获得各自的列。某些组合在基础数据中可能不存在，因此这些列会填充 NULL 值。

PIVOT cities
ON country, name
USING sum(population);

若要仅透视基础数据中存在的值组合，请在 ON 子句中使用表达式。可以提供多个表达式和/或列。

此处，country 和 name 被连接在一起，生成的连接结果各自分配一个列。可以使用任何非聚合的任意表达式。在本例中，使用下划线连接是为了模仿提供多个 ON 列时 PIVOT 子句使用的命名惯例（如前例所示）。

PIVOT cities
ON country || '_' || name
USING sum(population);

多个 USING 表达式

USING 子句中的每个表达式也可以包含别名。它将以追加下划线（_）的形式附加到生成的列名之后。当 USING 子句中包含多个表达式时，这使得列命名惯例更加清晰。

在此示例中，人口列的 sum 和 max 会针对每一年分别进行计算，并拆分为单独的列。

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;

多个 GROUP BY 列

也可以提供多个 GROUP BY 列。请注意，必须使用列名而不是列位置（1、2 等），并且 GROUP BY 子句不支持表达式。

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

在 SELECT 语句中使用 PIVOT

PIVOT 语句可以作为 CTE（公用表表达式或 WITH 子句）或子查询包含在 SELECT 语句中。这允许将 PIVOT 与其他 SQL 逻辑结合使用，并允许在单个查询中使用多个 PIVOT。

CTE 内不需要 SELECT，可以认为 PIVOT 关键字直接替代了它。

WITH pivot_alias AS (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
)
SELECT * FROM pivot_alias;

PIVOT 可用于子查询中，且必须用括号括起来。请注意，此行为与 SQL 标准 Pivot 不同，后续示例将对此进行说明。

SELECT *
FROM (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
) pivot_alias;

多个 PIVOT 语句

每个 PIVOT 都可以被视为一个 SELECT 节点，因此它们可以连接在一起或通过其他方式进行处理。

例如，如果两个 PIVOT 语句共享相同的 GROUP BY 表达式，它们可以使用 GROUP BY 子句中的列连接成一个更宽的透视表。

SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);

简化版 PIVOT 完整语法图

下面是 PIVOT 语句的完整语法图。

SQL 标准 PIVOT 语法

完整语法图如下所示，但 SQL 标准 PIVOT 语法可以概括为：

SELECT *
FROM dataset
PIVOT (
    values
    FOR
        column_1 IN (in_list)
        column_2 IN (in_list)
        ...
    GROUP BY rows
);

与简化语法不同，必须为每个要透视的列指定 IN 子句。如果您对动态透视感兴趣，建议使用简化语法。

请注意，FOR 子句中的表达式不使用逗号分隔，但 value 和 GROUP BY 表达式必须用逗号分隔！

示例

此示例使用单个值表达式、单个列表达式和单个行表达式

SELECT *
FROM cities
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
);

此示例虽然有点牵强，但可以用作在 FOR 子句中使用多个值表达式和多个列的示例。

SELECT *
FROM cities
PIVOT (
    sum(population) AS total,
    count(population) AS count
    FOR
        year IN (2000, 2010)
        country IN ('NL', 'US')
);

SQL 标准 PIVOT 完整语法图

下面是 SQL 标准版 PIVOT 语句的完整语法图。

限制

PIVOT 目前仅接受聚合函数，不允许使用表达式。例如，以下查询试图获取人口数量（即人，而不是千人，例如，不应得到 564，而应得到 564000）

PIVOT cities
ON year
USING sum(population) * 1000;

然而，它会失败并报错：

Catalog Error:
* is not an aggregate function

为了规避此限制，请仅使用聚合执行 PIVOT，然后使用 COLUMNS 表达式

SELECT country, name, 1000 * COLUMNS(* EXCLUDE (country, name))
FROM (
    PIVOT cities
    ON year
    USING sum(population)
);