PIVOT 和 UNPIVOT 示例 - Amazon Redshift

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 返回值 0GROUP BY 查询不包含相应的行。在大多数情况下,如果一行没有针对给定列的输入数据,则 PIVOT 会插入 NULL。但是,计数聚合不会返回 NULL0 是默认值。

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、PIVOTUNPIVOT 表达式。此外,也不支持 SUPER 取消嵌套的表达式和 Redshift Spectrum 嵌套表。

  • PIVOT 支持 COUNTSUMMINMAXAVG 聚合函数。

  • 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 NULLSUNPIVOT

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 输入值将生成结果行。

带有 qualityThe 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、PIVOTUNPIVOT 表达式。此外,也不支持 SUPER 取消嵌套的表达式和 Redshift Spectrum 嵌套表。

  • UNPIVOT IN 列表必须仅包含输入表列引用。IN 列表列必须具有它们都与之兼容的常见类型。UNPIVOT 值列具有这一常见类型。UNPIVOT 名称列属于类型 VARCHAR

  • 如果 IN 列表值没有别名,UNPIVOT 则使用列名作为默认值。