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 被连接在一起，生成的连接结果各自分配一个列。可以使用任何任意的非聚合表达式。在这种情况下，使用下划线连接是为了模仿 PIVOT 子句在提供多个 ON 列时（如前一个示例中）所使用的命名约定。

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)
);