PIVOT 语句

PIVOT 语句允许将某一列中的不同值拆分为各自独立的列。 这些新列中的值通过聚合函数计算得到，聚合范围是与每个不同值匹配的那部分行。

Goose 同时实现了 SQL 标准 PIVOT 语法和简化版 PIVOT 语法；后者会在透视时自动检测要创建的列。 也可以使用 PIVOT_WIDER 代替 PIVOT 关键字。

有关 PIVOT 语句实现细节，请参见 Pivot Internals site。

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;

country	name	year	population
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

PIVOT ON 与 USING

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

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 函数同样非常适合透视文本列。 （这在电子表格数据透视表中较难实现，但在 Goose 中很容易！）

这条查询会生成与上面相同的结果：

PIVOT cities
ON year
USING first(population);

注意：SQL 语法允许在 USING 子句中的聚合函数上使用 FILTER clauses。 但在 Goose 中，PIVOT 语句当前不支持这些子句，并会静默忽略。

PIVOT ON、USING 与 GROUP BY

默认情况下，PIVOT 语句会保留所有未在 ON 或 USING 子句中指定的列。 如果只想保留部分列并进一步聚合，请在 GROUP BY 子句中指定列。 这等价于电子表格数据透视表中的 rows 参数。

在下面的示例中，name 列不再出现在输出中，数据按 country 层级聚合。

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

country	2000	2010	2020
NL	1005	1065	1158
US	8579	8783	9510

ON 子句中的 IN 过滤

若只想为 ON 子句列中的特定值创建独立列，可使用可选的 IN 表达式。 例如，假设我们出于某种原因不考虑 2020 年...

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

country	2000	2010
NL	1005	1065
US	8579	8783

每个子句支持多个表达式

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

多个 ON 列与 ON 表达式

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

在下面的示例中，唯一国家与唯一城市的所有组合都会得到各自的列。 某些组合可能不存在于底层数据中，因此对应列会填充 NULL。

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

year	NL_Amsterdam	NL_New York City	NL_Seattle	US_Amsterdam	US_New York City	US_Seattle
2000	1005	NULL	NULL	NULL	8015	564
2010	1065	NULL	NULL	NULL	8175	608
2020	1158	NULL	NULL	NULL	8772	738

如果只想透视底层数据中真实存在的值组合，请在 ON 子句中使用表达式。 可提供多个表达式和/或列。

这里将 country 和 name 连接在一起，连接结果各自获得一列。 可以使用任意非聚合表达式。 此处使用下划线连接，是为了模拟在提供多个 ON 列时 PIVOT 子句使用的命名约定（如上一示例）。

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

year	NL_Amsterdam	US_New York City	US_Seattle
2000	1005	8015	564
2010	1065	8175	608
2020	1158	8772	738

多个 USING 表达式

USING 子句中的每个表达式也可以包含别名。 该别名会追加到生成列名后，并以下划线（_）分隔。 当 USING 子句包含多个表达式时，这会让列命名更清晰。

此示例中，对每一年都会计算 population 列的 sum 与 max，并拆分为独立列。

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

country	2000_total	2000_max	2010_total	2010_max	2020_total	2020_max
US	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（Common Table Expression，即 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);

country	2000	2010	2020	Amsterdam	New York City	Seattle
NL	1005	1065	1158	3228	NULL	NULL
US	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 表达式必须使用逗号分隔！

示例

这个示例使用了单个 value 表达式、单个列表达式和单个行表达式：

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 子句中使用多个 value 表达式和多个列。

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

name	2000_NL_total	2000_NL_count	2000_US_total	2000_US_count	2010_NL_total	2010_NL_count	2010_US_total	2010_US_count
Amsterdam	1005	1	NULL	0	1065	1	NULL	0
Seattle	NULL	0	564	1	NULL	0	608	1
New York City	NULL	0	8015	1	NULL	0	8175	1

SQL 标准 PIVOT 完整语法图

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

限制

PIVOT 当前仅接受聚合函数，不允许表达式。 例如，以下查询尝试将 population 从“千人”转换为“人数”（即把 564 变为 564000）：

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

但它会报如下错误：

Catalog Error:
* is not an aggregate function

要绕过这一限制，可先仅执行聚合 PIVOT，再使用 COLUMNS expression：

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