PIVOT

描述

通过将列组旋转成行并折叠列出的列来转换 table_reference 的行：第一个新列保存原始列组名称（或别名）作为值，此列后接一组列，这些列包含每个列组的值。

语法

table_reference PIVOT ( { aggregate_expression [ [ AS ] agg_column_alias ] } [, ...]
    FOR column_list IN ( expression_list ) )

column_list
 { column_name |
   ( column_name [, ...] ) }

expression_list
 { expression [ AS ] [ column_alias ] |
   { ( expression [, ...] ) [ AS ] [ column_alias] } [, ...] ) }

参数

• table_reference

  标识 PIVOT 操作的主题。

• aggregate_expression

  任何类型的表达式，其中的所有列引用 table_reference 都是聚合函数的参数。

• agg_column_alias

  聚合结果的可选别名。 如果未指定别名，PIVOT 会根据 aggregate_expression 生成别名。

• column_list

  要轮换的列集。column_name 为来自 table_reference 的列。

• column_name

  标识将取消透视的关系中的列。 可以限定名称。 所有 column_name 必须共享最不常见类型。

• expression_list

  将 column_list 中的值映射到列别名。

  • expression

    一种文本表达式，它与相应的 column_name 都使用一个最不常见的类型。

    每个元组中表达式的数量必须与 column_list 中 column_names 的数量匹配。

  • column_alias

    一个可选别名，用于指定生成的列的名称。 如果未指定别名，PIVOT 会根据 expression 生成别名。

示例

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

+---------+-----------+--------+---------+-----------+-----------+
|     year|     region|      q1|       q2|         q3|         q4|
+---------+-----------+--------+---------+-----------+-----------+
|     2018|       east|     100|       20|         40|         40|
+---------+-----------+--- ----+---------+-----------+-----------+
|     2019|       east|     120|      110|         80|         60|
+---------+-----------+--- ----+---------+-----------+-----------+
|     2018|       west|     105|       25|         45|         45|
+---------+-----------+--- ----+---------+-----------+-----------+
|     2019|       west|     125|      115|         85|         65|
+---------+-----------+--- ----+---------+-----------+-----------+

SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
    FROM sales
    PIVOT (sum(sales) AS sales
      FOR (quarter, region)
      IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
          (3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));


+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|     year| q1_east| q1_west| q2_east| q2_west| q3_east| q3_west| q4_east| q4_west|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|     2018|     100|     105|      20|      25|      40|      45|      40|      45|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|     2019|     120|     125|     110|     115|      80|      85|      60|      65|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+

SELECT year, q1, q2, q3, q4
  FROM (SELECT year, quarter, sales FROM sales) AS s
  PIVOT (sum(sales) AS sales
    FOR quarter
    IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));

+---------+--------+--------+--------+--------+
|     year|      q1|      q2|      q3|      q4|
+---------+--------+--------+--------+--------+
|     2018|     205|      45|      85|      85|
+---------+--------+--------+--------+--------+
|     2019|     245|     225|     165|     125|
+---------+--------+--------+--------+--------+

SELECT year, q1_total, q1_avg, q2_total, q2_avg, q3_total, q3_avg, q4_total, q4_avg
    FROM (SELECT year, quarter, sales FROM sales) AS s
    PIVOT (sum(sales) AS total, avg(sales) AS avg
      FOR quarter
      IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));

+---------+---------+--------+---------+--------+---------+--------+---------+--------+
|     year| q1_total|  q1_avg| q2_total|  q2_avg| q3_total|  q3_avg| q4_total|  q4_avg|
+---------+---------+--------+---------+--------+---------+--------+---------+--------+
|     2018|      205|   102.5|       45|    22.5|       85|    42.5|       85|    42.5|
+---------+---------+--------+---------+--------+---------+--------+---------+--------+
|     2019|      245|   122.5|      225|   122.5|      165|    82.5|      125|    62.5|
+---------+---------+--------+---------+--------+---------+--------+---------+--------+

相关文档

SELECT