UNPIVOT
描述
通过将列组旋转成行并折叠列出的列来转换 table_reference 的行：第一个新列保存原始列组名称（或别名）作为值，此列后接一组列，这些列包含每个列组的值。
语法
table_reference UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ]
{ single_value | multi_value }
( value_column
FOR unpivot_column IN ( { column_name [ column_alias ] } [, ...] ) )
[ table_alias ]
single_value
( value_column
FOR unpivot_column IN ( { column_name [ column_alias ] } [, ...] ) )
multi_value
( ( value_column [, ...] )
FOR unpivot_column IN ( { ( column_name [, ...] ) [ column_alias ] } [, ...] ) )参数
table_reference
标识 UNPIVOT 操作的主题。
INCLUDE NULLS 或 EXCLUDE NULLS
是否筛选掉 value_column 中包含 NULL 的行。 默认为 EXCLUDE NULLS。
value_column
非限定列别名。 此列将保存值。 每个 value_column 的类型是对应的 column_name 列类型中的最不常见类型。
unpivot_column
非限定列别名。 此列将保存旋转的 column_name 或其 column_alias 的名称。 类型 unpivot_column 不是 STRING。
对于多值 UNPIVOT，如果没有 column_alias，则值是 '_' 分隔的 column_name 的串联形式。
column_name
标识将取消透视的关系中的列。 可以限定名称。 所有 column_name 必须共享最不常见类型。
column_alias
在 unpivot_column 中使用的可选名称。
table_alias
为生成的表指定标签。 如果 table_alias 包含 column_identifier，其数字必须与 UNPIVOT 生成的列数匹配。
示例
CREATE TABLE sales(location string, year int, q1 int, q2 int, q3 int, q4 int);
INSERT INTO sales VALUES ('Toronto', 2020, 100 , 80 , 70, 150),
('San Francisco', 2020, NULL, 20 , 50, 60),
('Toronto' , 2021, 110 , 90 , 80, 170),
('San Francisco', 2021, 70 , 120, 85, 105);
SELECT *
FROM sales UNPIVOT INCLUDE NULLS
(sales FOR quarter IN (q1 AS `Jan-Mar`,
q2 AS `Apr-Jun`,
q3 AS `Jul-Sep`,
sales.q4 AS `Oct-Dec`));
+----------------+-----------+----------+-------------+
| location| year| quarter| sales|
+----------------+-----------+----------+-------------+
| Toronto| 2020| Jan-Mar| 100|
+----------------+-----------+----------+-------------+
| Toronto| 2020| Apr-Jun| 80|
+----------------+-----------+----------+-------------+
| Toronto| 2020| Jul-Sep| 70|
+----------------+-----------+----------+-------------+
| Toronto| 2020| Oct-Dec| 150|
+----------------+-----------+----------+-------------+
| San Francisco| 2020| Jan-Mar| NULL|
+----------------+-----------+----------+-------------+
| San Francisco| 2020| Apr-Jun| 20|
+----------------+-----------+----------+-------------+
| San Francisco| 2020| Jul-Sep| 50|
+----------------+-----------+----------+-------------+
| San Francisco| 2020| Oct-Dec| 60|
+----------------+-----------+----------+-------------+
| Toronto| 2021| Jan-Mar| 110|
+----------------+-----------+----------+-------------+
| Toronto| 2021| Apr-Jun| 90|
+----------------+-----------+----------+-------------+
| Toronto| 2021| Jul-Sep| 80|
+----------------+-----------+----------+-------------+
| Toronto| 2021| Oct-Dec| 170|
+----------------+-----------+----------+-------------+
| San Francisco| 2021| Jan-Mar| 70|
+----------------+-----------+----------+-------------+
| San Francisco| 2021| Apr-Jun| 120|
+----------------+-----------+----------+-------------+
| San Francisco| 2021| Jul-Sep| 85|
+----------------+-----------+----------+-------------+
| San Francisco| 2021| Oct-Dec| 105|
+----------------+-----------+----------+-------------+
CREATE TABLE oncall
(year int, week int, area string, name1 string, email1 string, phone1 bigint, name2 string , email2 string, phone2 bigint);
INSERT INTO oncall VALUES (2022, 1 , 'frontend', 'Freddy', 'fred@alwaysup.org' , 15551234567, 'Fanny' , 'fanny@alwaysup.org' , 15552345678),
(2022, 1 , 'backend' , 'Boris' , 'boris@alwaysup.org', 15553456789, 'Boomer', 'boomer@alwaysup.org', 15554567890),
(2022, 2 , 'frontend', 'Franky', 'frank@alwaysup.org' , 15555678901, 'Fin' , 'fin@alwaysup.org' , 15556789012),
(2022, 2 , 'backend' , 'Bonny' , 'bonny@alwaysup.org', 15557890123, 'Bea' , 'bea@alwaysup.org' , 15558901234);
SELECT *
FROM oncall UNPIVOT ((name, email, phone) FOR precedence IN ((name1, email1, phone1) AS primary,
(name2, email2, phone2) AS secondary));
+---------+--------+----------+------------+-----------+-------------------+
| year| week| area| precedence| name| email|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 1| fronted| primary| Freddy| fred@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 1| fronted| secondary| Fanny| fanny@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 1| backend| primary| Boris| boris@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 1| backend| secondary| Boomer|boomer@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 2| fronted| primary| Franky| frank@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 2| fronted| secondary| Fin| fin@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 2| backend| primary| Bonny| bonny@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+
| 2022| 2| backend| secondary| Bea| bea@alwaysup.org|
+---------+--------+----------+------------+-----------+-------------------+