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

manufacturer 作為隱含資料欄的 PIVOT 範例。

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 資料欄就是這種情況。此範例也會顯示 NULLIN 運算子的有效值。

PIVOT 在上述範例中會傳回類似下列查詢的資訊,其中包括 GROUP BY。不同之處在於 PIVOT 傳回資料欄 2 的值 0 和製造商 small parts coGROUP 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 輸入值會產生結果列。

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 資料欄就是這種情況。

下列範例顯示 IN 清單中具有值別名的 UNPIVOT

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 會使用資料欄名稱做為預設值。