UNPIVOT 语句

UNPIVOT 语句可以将多列堆叠为更少的列。在最基本的情况下，多列会堆叠为两列：NAME 列（包含源列名）和 VALUE 列（包含源列值）。

Goose 同时实现了 SQL 标准 UNPIVOT 语法和简化版 UNPIVOT 语法。两者都可以使用 COLUMNS 表达式自动检测要反透视的列。也可以使用 PIVOT_LONGER 关键字来替代 UNPIVOT。

关于 UNPIVOT 语句的实现细节，请参见 Pivot Internals site。

PIVOT 语句是 UNPIVOT 语句的逆操作。

简化版 `UNPIVOT` 语法

完整语法图见下方，简化版 UNPIVOT 语法可按电子表格透视表的命名习惯概括为：

UNPIVOT ⟨dataset⟩
ON ⟨column(s)⟩
INTO
    NAME ⟨name_column_name⟩
    VALUE ⟨value_column_name(s)⟩
ORDER BY ⟨column(s)_with_order_direction(s)⟩
LIMIT ⟨number_of_rows⟩;

示例数据

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

CREATE OR REPLACE TABLE monthly_sales
    (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
INSERT INTO monthly_sales VALUES
    (1, 'electronics', 1, 2, 3, 4, 5, 6),
    (2, 'clothes', 10, 20, 30, 40, 50, 60),
    (3, 'cars', 100, 200, 300, 400, 500, 600);

FROM monthly_sales;

empid	dept	Jan	Feb	Mar	Apr	May	Jun
1	electronics	1	2	3	4	5	6
2	clothes	10	20	30	40	50	60
3	cars	100	200	300	400	500	600

手动 `UNPIVOT`

最常见的 UNPIVOT 转换，是将已经透视过的数据重新堆叠为“名称列 + 值列”。在这里，所有月份会被堆叠到 month 列和 sales 列中。

UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
    NAME month
    VALUE sales;

empid	dept	month	sales
1	electronics	Jan	1
1	electronics	Feb	2
1	electronics	Mar	3
1	electronics	Apr	4
1	electronics	May	5
1	electronics	Jun	6
2	clothes	Jan	10
2	clothes	Feb	20
2	clothes	Mar	30
2	clothes	Apr	40
2	clothes	May	50
2	clothes	Jun	60
3	cars	Jan	100
3	cars	Feb	200
3	cars	Mar	300
3	cars	Apr	400
3	cars	May	500
3	cars	Jun	600

使用 `COLUMNS` 表达式动态 `UNPIVOT`

在很多情况下，要反透视的列数并不容易提前确定。对于此数据集而言，每新增一个月份，上面的查询都需要修改。可使用 COLUMNS 表达式选取除 empid 和 dept 之外的所有列。这使得反透视可动态适配新增月份数量。下方查询与上方查询返回的结果完全一致。

UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
    NAME month
    VALUE sales;

empid	dept	month	sales
1	electronics	Jan	1
1	electronics	Feb	2
1	electronics	Mar	3
1	electronics	Apr	4
1	electronics	May	5
1	electronics	Jun	6
2	clothes	Jan	10
2	clothes	Feb	20
2	clothes	Mar	30
2	clothes	Apr	40
2	clothes	May	50
2	clothes	Jun	60
3	cars	Jan	100
3	cars	Feb	200
3	cars	Mar	300
3	cars	Apr	400
3	cars	May	500
3	cars	Jun	600

将 `UNPIVOT` 输出到多个值列

UNPIVOT 语句还有更高的灵活性：支持超过 2 个目标列。当目标是“降低数据集透视程度”，但又不希望把所有透视列全部堆叠时，这种能力很有用。为演示这一点，下方查询会生成一个数据集：将季度内每个月份编号（第 1、2、3 个月）拆分为独立列，并为每个季度生成独立行。由于季度数少于月份数，数据集会变长，但不会像上面的示例那样长。

要实现这一点，需要在 ON 子句中包含多组列。 q1 和 q2 别名是可选的。 ON 子句中每组列的列数，必须与 VALUE 子句中的列数一致。

UNPIVOT monthly_sales
    ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
    INTO
        NAME quarter
        VALUE month_1_sales, month_2_sales, month_3_sales;

empid	dept	quarter	month_1_sales	month_2_sales	month_3_sales
1	electronics	q1	1	2	3
1	electronics	q2	4	5	6
2	clothes	q1	10	20	30
2	clothes	q2	40	50	60
3	cars	q1	100	200	300
3	cars	q2	400	500	600

在 `SELECT` 语句中使用 `UNPIVOT`

UNPIVOT 语句可以在 SELECT 语句中作为 CTE（通用表表达式，即 WITH 子句）或子查询使用。这允许你将 UNPIVOT 与其他 SQL 逻辑结合，也可在同一查询中使用多个 UNPIVOT。

在 CTE 内无需再写 SELECT，可将 UNPIVOT 关键字理解为其占位替代。

WITH unpivot_alias AS (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
)
SELECT * FROM unpivot_alias;

UNPIVOT 也可用于子查询，并且必须用括号包裹。请注意，这一行为与 SQL 标准 UNPIVOT 不同，后续示例会说明差异。

SELECT *
FROM (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
) unpivot_alias;

`UNPIVOT` 语句中的表达式

Goose 允许在 UNPIVOT 语句中使用表达式，前提是表达式只涉及单个列。它们既可用于计算，也可用于显式类型转换。例如：

UNPIVOT
    (SELECT 42 AS col1, 'woot' AS col2)
    ON
        (col1 * 2)::VARCHAR,
        col2;

name	value
col1	84
col2	woot

简化版 `UNPIVOT` 完整语法图

下方是 UNPIVOT 语句的完整语法图。

SQL 标准 `UNPIVOT` 语法

完整语法图见下方，SQL 标准 UNPIVOT 语法可概括为：

FROM [dataset]
UNPIVOT [INCLUDE NULLS] (
    [value-column-name(s)]
    FOR [name-column-name] IN [column(s)]
);

请注意，name-column-name 表达式中只能包含一个列。

手动编写 SQL 标准 `UNPIVOT`

使用 SQL 标准语法完成基础 UNPIVOT 操作时，只需少量额外语法。

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
);

empid	dept	month	sales
1	electronics	Jan	1
1	electronics	Feb	2
1	electronics	Mar	3
1	electronics	Apr	4
1	electronics	May	5
1	electronics	Jun	6
2	clothes	Jan	10
2	clothes	Feb	20
2	clothes	Mar	30
2	clothes	Apr	40
2	clothes	May	50
2	clothes	Jun	60
3	cars	Jan	100
3	cars	Feb	200
3	cars	Mar	300
3	cars	Apr	400
3	cars	May	500
3	cars	Jun	600

使用 `COLUMNS` 表达式动态执行 SQL 标准 `UNPIVOT`

COLUMNS 表达式可用于动态确定 IN 子句中的列列表。即使数据集中新增了 month 列，该写法仍可继续工作。其结果与上方查询相同。

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (columns(* EXCLUDE (empid, dept)))
);

SQL 标准 `UNPIVOT` 输出到多个值列

UNPIVOT 语句还具备额外灵活性：支持超过 2 个目标列。当目标是减少数据集的透视程度，但不希望把全部透视列完全堆叠时，这很有用。为演示这一点，下方查询会生成一个数据集：将季度内每个月份编号（第 1、2、3 个月）拆分为独立列，并为每个季度生成独立行。由于季度数少于月份数，数据集会变长，但不会像前面的示例那样长。

要实现这一点，需要在 UNPIVOT 的 value-column-name 部分包含多个列。 IN 子句中也要包含多组列。 q1 和 q2 别名是可选的。 IN 子句中每组列的列数必须与 value-column-name 部分的列数一致。

FROM monthly_sales
UNPIVOT (
    (month_1_sales, month_2_sales, month_3_sales)
    FOR quarter IN (
        (jan, feb, mar) AS q1,
        (apr, may, jun) AS q2
    )
);

empid	dept	quarter	month_1_sales	month_2_sales	month_3_sales
1	electronics	q1	1	2	3
1	electronics	q2	4	5	6
2	clothes	q1	10	20	30
2	clothes	q2	40	50	60
3	cars	q1	100	200	300
3	cars	q2	400	500	600

SQL 标准 `UNPIVOT` 完整语法图

下方是 SQL 标准版本 UNPIVOT 语句的完整语法图。

简化版 UNPIVOT 语法​

示例数据​

手动 UNPIVOT​

使用 COLUMNS 表达式动态 UNPIVOT​

将 UNPIVOT 输出到多个值列​

在 SELECT 语句中使用 UNPIVOT​

UNPIVOT 语句中的表达式​

简化版 UNPIVOT 完整语法图​

SQL 标准 UNPIVOT 语法​

手动编写 SQL 标准 UNPIVOT​

使用 COLUMNS 表达式动态执行 SQL 标准 UNPIVOT​

SQL 标准 UNPIVOT 输出到多个值列​

SQL 标准 UNPIVOT 完整语法图​