⌘+k ctrl+k
1.1.3 (stable)
Search Shortcut cmd + k | ctrl + k
PIVOT Statement

PIVOT 语句允许将列中的不同值分离到它们自己的列中。这些新列中的值是使用聚合函数在匹配每个不同值的行子集上计算的。

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

有关PIVOT语句如何实现的详细信息，请参阅Pivot Internals 网站。

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 子句都是可选的，但它们不能全部省略。

Example Data

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

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;
国家 名称 年份 人口
NL 阿姆斯特丹 2000 1005
NL 阿姆斯特丹 2010 1065
NL 阿姆斯特丹 2020 1158
美国 西雅图 2000 564
美国 西雅图 2010 608
美国 西雅图 2020 738
美国 纽约市 2000 8015
美国 纽约市 2010 8175
美国 纽约市 2020 8772

PIVOT ON 和 USING

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

USING 子句决定了如何聚合被分割到不同列中的值。 这相当于电子表格数据透视表中的 values 参数。 如果未包含 USING 子句，则默认为 count(*)。

PIVOT cities
ON year
USING sum(population);
country name 2000 2010 2020
NL Amsterdam 1005 1065 1158
US Seattle 564 608 738
US New York City 8015 8175 8772

在上面的例子中，sum 聚合总是对单个值进行操作。 如果我们只想改变数据的显示方向而不进行聚合，可以使用 first 聚合函数。 在这个例子中，我们正在对数值进行透视，但 first 函数在透视文本列时也非常有效。 （这是在电子表格透视表中难以实现的功能，但在 DuckDB 中却很容易！）

此查询产生的结果与上面的结果相同：

PIVOT cities
ON year
USING first(population);

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

PIVOT ON, USING, and GROUP BY

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

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

PIVOT cities
ON year
USING sum(population)
GROUP BY country;
国家 2000 2010 2020
NL 1005 1065 1158
美国 8579 8783 9510

IN 用于 ON 子句的过滤器

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

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;
国家 2000 2010
NL 1005 1065
美国 8579 8783

每个子句的多个表达式

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

多个ON列和ON表达式

可以将多列转换为它们自己的列。 DuckDB 将在每个 ON 子句列中找到不同的值，并为这些值的所有组合（笛卡尔积）创建一个新列。

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

PIVOT cities
ON country, name
USING sum(population);
年份 NL_阿姆斯特丹 NL_纽约市 NL_西雅图 US_阿姆斯特丹 US_纽约市 US_西雅图
2000 1005 NULL NULL NULL 8015 564
2010 1065 NULL NULL NULL 8175 608
2020 1158 NULL NULL NULL 8772 738

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

在这里，country 和 name 被连接在一起，每个连接结果都会生成自己的列。 可以使用任何任意的非聚合表达式。 在这种情况下，使用下划线连接是为了模仿 PIVOT 子句在提供多个 ON 列时使用的命名约定（如前面的示例所示）。

PIVOT cities
ON country || '_' || name
USING sum(population);
年份 荷兰_阿姆斯特丹 美国_纽约市 美国_西雅图
2000 1005 8015 564
2010 1065 8175 608
2020 1158 8772 738

多个 USING 表达式

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

在这个例子中，每年的人口列的sum和max都被计算，并分成单独的列。

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;
国家 2000年总计 2000年最大值 2010年总计 2010年最大值 2020年总计 2020年最大值
美国 8579 8015 8783 8175 9510 8772
NL 1005 1005 1065 1065 1158 1158

多个 GROUP BY 列

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

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;
country name 2000 2010 2020
NL Amsterdam 1005 1065 1158
US Seattle 564 608 738
US New York City 8015 8175 8772

在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);
国家 2000 2010 2020 阿姆斯特丹 纽约市 西雅图
NL 1005 1065 1158 3228 NULL NULL
美国 8579 8783 9510 NULL 24962 1910

简化的 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 表达式必须用逗号分隔！

Examples

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

SELECT *
FROM cities
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
);
country 2000 2010 2020
NL 1005 1065 1158
US 8579 8783 9510

这个例子有些人为设计，但可以作为在FOR子句中使用多个值表达式和多列的示例。

SELECT *
FROM cities
PIVOT (
    sum(population) AS total,
    count(population) AS count
    FOR
        year IN (2000, 2010)
        country IN ('NL', 'US')
);
名称 2000年荷兰总计 2000年荷兰数量 2000年美国总计 2000年美国数量 2010年荷兰总计 2010年荷兰数量 2010年美国总计 2010年美国数量
阿姆斯特丹 1005 1 NULL 0 1065 1 NULL 0
西雅图 NULL 0 564 1 NULL 0 608 1
纽约市 NULL 0 8015 1 NULL 0 8175 1

SQL标准PIVOT完整语法图

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

Limitations

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)
);
关于此页面