透視和不透視表示例

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如果某行沒有給定列的輸入數據。但是，計數聚合不會返回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;

查詢會產生下列輸出。

 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目前支援COUNT、SUM、MIN、MAX，和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使用列名作為默認值。