透視和不透視表示例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

透視和不透視表示例

PIVOT 範例

設置示例表和數據,並使用它們運行後續示例查詢。

CREATE TABLE part ( partname varchar, manufacturer varchar, quality int, price decimal(12, 2) ); INSERT INTO part VALUES ('P1', 'M1', 2, 10.00); INSERT INTO part VALUES ('P1', 'M2', NULL, 9.00); INSERT INTO part VALUES ('P1', 'M3', 1, 12.00); INSERT INTO part VALUES ('P2', 'M1', 1, 2.50); INSERT INTO part VALUES ('P2', 'M2', 2, 3.75); INSERT INTO part VALUES ('P2', 'M3', NULL, 1.90); INSERT INTO part VALUES ('P3', 'M1', NULL, 7.50); INSERT INTO part VALUES ('P3', 'M2', 1, 15.20); INSERT INTO part VALUES ('P3', 'M3', NULL, 11.80);

樞紐分析至partname使用AVG彙總price

SELECT * FROM (SELECT partname, price FROM part) PIVOT ( AVG(price) FOR partname IN ('P1', 'P2', 'P3') );

查詢會產生下列輸出。

p1 | p2 | p3 -------+------+------- 10.33 | 2.71 | 11.50

前面的結果PIVOT範例等效於下列GROUP BY查詢。但是,結果行會轉換為列名和列。

SELECT partname, avg(price) FROM (SELECT partname, price FROM part) WHERE partname IN ('P1', 'P2', 'P3') GROUP BY partname;

查詢會產生下列輸出。

partname | avg ----------+------- P1 | 10.33 P2 | 2.71 P3 | 11.50

一個PIVOT的範例manufacturer作為隱式列。

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

查詢會產生下列輸出。

manufacturer | 1 | 2 | null --------------+---+---+------ M1 | 1 | 1 | 1 M2 | 1 | 1 | 1 M3 | 1 | 0 | 2

輸入表列中未引用PIVOT定義隱式添加到結果表中。這種情況是manufacturer欄位。這個例子還顯示NULL是一個有效的值,用於IN操作。

PIVOT返回與以下查詢相似的信息,其中包括GROUP BY。唯一的區別是PIVOT返回值0對於列2和製造商M3。所以此GROUP 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;

查詢會產生下列輸出。

manufacturer | quality | count --------------+---------+------- M1 | 1 | 1 M1 | 2 | 1 M1 | | 1 M2 | 1 | 1 M2 | 2 | 1 M2 | | 1 M3 | 1 | 1 M3 | | 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 --------------+------------+-----------+---------- M1 | 1 | 1 | 1 M2 | 1 | 1 | 1 M3 | 1 | 0 | 2

下列為PIVOT

  • PIVOT可應用於表、子查詢和公用表表達式 (CTE)。PIVOT不能應用於任何JOIN表達式, 遞歸 CTE,PIVOT, 或UNPIVOT表達式。也不支持SUPER未嵌套表達式和 Redshift 頻譜嵌套表。

  • PIVOT目前支援COUNTSUMMINMAX,和AVG彙整函數。

  • 所以此PIVOT聚合表達式必須是受支持的集合函數的調用。不支持聚合頂部的複雜表達式。聚合參數不能包含對PIVOT輸入表。也不支持對父查詢的關聯引用。聚合參數可能包含子查詢,這些子查詢可以在內部或PIVOT輸入表。

  • 所以此PIVOT IN列表值不能是列引用或子查詢。每個數值必須與FOR資料欄引用。

  • 如果IN列表值沒有別名,PIVOT將生成默認列名。常數IN值,如 'abc' 或 5,默認列名是常量本身。對於任何複雜的表達式,列名稱都是標準的 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值被跳過,並且不會產生結果行。

下列範例顯示UNPIVOT取代為INCLUDE NULLS

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輸入值生成結果行。

The following query shows UNPIVOT取代為quality作為隱式列。

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 頻譜嵌套表。

  • 所以此UNPIVOT IN列表必須僅包含輸入表列引用。所以此IN列表列必須具有所有與其兼容的通用類型。所以此UNPIVOT值列具有此常見類型。所以此UNPIVOT名稱列的類型為VARCHAR

  • 如果IN列表值沒有別名,UNPIVOT使用列名作為默認值。