PIVOT 和 UNPIVOT 示例

PIVOT 和 UNPIVOT 是 FROM 子句中的参数，它们分别将查询输出从行轮换到列，以及从列轮换到行。它们以便于阅读的格式呈现表格查询结果。以下示例使用测试数据和查询来说明如何使用它们。

有关这些参数及其他参数的更多信息，请参阅 FROM 子句。

PIVOT 示例

设置示例表和数据并使用它们运行后续示例查询。


CREATE TABLE part (
    partname varchar,
    manufacturer varchar,
    quality int,
    price decimal(12, 2)
);

INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);

INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);

INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);

partname 上的 PIVOT，在 price 上有一个 AVG 聚合。


SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
    AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

查询将生成以下输出。


  prop   |  rudder  |  wing
---------+----------+---------
 10.33   | 2.71     | 11.50

在前面的示例中，结果转换为列。以下示例显示了一个按行而不是按列返回平均价格的 GROUP BY 查询。


SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;

查询将生成以下输出。


 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

一个 PIVOT 示例，将 manufacturer 作为隐式列。


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) FOR quality IN (1, 2, NULL)
);

查询将生成以下输出。


 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

PIVOT 定义中未引用的输入表列被隐式添加到结果表中。就是上一个示例中 manufacturer 列这种情况。示例还显示，对于 IN 运算符，NULL 为有效值。

上述示例中的 PIVOT 返回与以下查询类似的信息，其中包含 GROUP BY。区别在于 PIVOT 为列 2 和制造商 small parts co 返回值 0。GROUP BY 查询不包含相应的行。在大多数情况下，如果一行没有针对给定列的输入数据，则 PIVOT 会插入 NULL。但是，计数聚合不会返回 NULL，0 是默认值。


SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;

查询将生成以下输出。


 manufacturer        | quality | count
---------------------+---------+-------
 big parts co        |         |     1
 big parts co        |       2 |     1
 big parts co        |       1 |     1
 local parts co      |       2 |     1
 local parts co      |       1 |     1
 local parts co      |         |     1
 small parts co      |       1 |     1
 small parts co      |         |     2

PIVOT 运算符接受聚合表达式和 IN 运算符的每个值上的可选别名。使用别名自定义列名。如果没有聚合别名，则仅使用 IN 列表别名。否则，将聚合别名附加到列名，并使用下划线将其与列名分开。


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);

查询将生成以下输出。


 manufacturer      | high_count  | low_count | na_count
-------------------+-------------+-----------+----------
 local parts co    |           1 |         1 |        1
 big parts co      |           1 |         1 |        1
 small parts co    |           1 |         0 |        2

设置以下示例表和数据，并使用它们运行后续示例查询。该数据表示一系列酒店的预订日期。


CREATE TABLE bookings (
    booking_id int,
    hotel_code char(8),
    booking_date date,
    price decimal(12, 2)
);

INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);

INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);

INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);

INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);

INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);

INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);

INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);

INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);

INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);

在此示例查询中，对预订记录进行统计以得出每周的总数。每周的结束日期成为列名。


SELECT * FROM
    (SELECT
       booking_id,
       (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
       hotel_code AS "hotel code"
FROM bookings
) PIVOT (
    count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') 
);

查询将生成以下输出。


 hotel code | 2023-02-04  | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
 FOREST_L   |           3 |          2 |        1
 DESERT_S   |           4 |          3 |        2
 OCEAN_WV   |           3 |          3 |        3
 CITY_BLD   |           3 |          1 |        2

Amazon Redshift 不支持 CROSSTAB 对多列进行透视。但您可以将行数据更改为列，与使用 PIVOT 进行聚合类似，使用如下所示的查询。这使用与上一个示例相同的预订示例数据。


SELECT 
  booking_date,
  MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
  MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
  MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END)  AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;

示例查询会导致在表示已预订了哪些酒店的短语旁边列出预订日期。


 booking_date  | forest_l         | desert_s         | ocean_wv
---------------+------------------+------------------+--------------------
 2023-02-01    | forest is booked | desert is booked |  ocean is booked
 2023-02-02    | forest is booked | desert is booked |  ocean is booked
 2023-02-04    | forest is booked | desert is booked |  ocean is booked
 2023-02-05    |                  | desert is booked |        
 2023-02-06    |                  | desert is booked |

以下是 PIVOT 的使用说明：

PIVOT 可以应用于表、子查询和公用表表达式（CTE）。PIVOT 不可应用于任何 JOIN 表达式、递归 CTE、PIVOT 或 UNPIVOT 表达式。此外，也不支持 SUPER 取消嵌套的表达式和 Redshift Spectrum 嵌套表。
PIVOT 支持 COUNT、SUM、MIN、MAX 和 AVG 聚合函数。
PIVOT 聚合表达式必须是对受支持的聚合函数的调用。不支持聚合顶部的复杂表达式。聚合参数仅可包含对 PIVOT 输入表的引用。此外，也不支持对父查询的关联引用。聚合参数可能包含子查询。它们可以在内部关联或在 PIVOT 输入表上关联。
PIVOT IN 列表值不得为列引用或子查询。每个值必须与 FOR 列引用类型兼容。
如果 IN 列表值没有别名，PIVOT 会生成默认的列名。对于常量 IN 值（例如“abc”或 5），默认列名是常量本身。对于任何复杂表达式，列名都是标准的 Amazon Redshift 默认名称，例如 ?column?。

UNPIVOT 示例

设置示例数据并使用它来运行后续示例。


CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);

INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);

红、绿和蓝输入列上的 UNPIVOT。


SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

查询将生成以下输出。


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |  23
 blue  |   7
 blue  |  40

默认情况下，将跳过输入列中的 NULL 值，且不会产生结果行。

以下示例显示了带有 INCLUDE NULLS 的 UNPIVOT。


SELECT *
FROM (
    SELECT red, green, blue
    FROM count_by_color
) UNPIVOT INCLUDE NULLS (
    cnt FOR color IN (red, green, blue)
);

以下是结果输出。


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |
 green |  23
 blue  |   7
 blue  |  40
 blue  |

如果已设置 INCLUDING NULLS 参数，NULL 输入值将生成结果行。

带有 quality 的 The following query shows UNPIVOT 作为隐式列。


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

查询将生成以下输出。


 quality | color | cnt
---------+-------+-----
 high    | red   |  15
 normal  | red   |  35
 low     | red   |  10
 high    | green |  20
 low     | green |  23
 high    | blue  |   7
 normal  | blue  |  40

UNPIVOT 定义中未引用的输入表列被隐式添加到结果表中。在该示例中，quality 列就是这种情况。

以下示例显示了带有 UNPIVOT 列表中值别名的 IN。


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

上一查询将产生以下输出。


 quality | color | cnt
---------+-------+-----
 high    | r     |  15
 normal  | r     |  35
 low     | r     |  10
 high    | g     |  20
 low     | g     |  23
 high    | b     |   7
 normal  | b     |  40

UNPIVOT 运算符接受每个 IN 列表值上的可选别名。每个别名会提供每个 value 列中的数据自定义。

以下是 UNPIVOT 的使用说明。

UNPIVOT 可以应用于表、子查询和公用表表达式（CTE）。UNPIVOT 不可应用于任何 JOIN 表达式、递归 CTE、PIVOT 或 UNPIVOT 表达式。此外，也不支持 SUPER 取消嵌套的表达式和 Redshift Spectrum 嵌套表。
UNPIVOT IN 列表必须仅包含输入表列引用。IN 列表列必须具有它们都与之兼容的常见类型。UNPIVOT 值列具有这一常见类型。UNPIVOT 名称列属于类型 VARCHAR。
如果 IN 列表值没有别名，UNPIVOT 则使用列名作为默认值。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

FROM 子句

JOIN 示例