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);
price で AVG を集計した partname の PIVOT。
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 列が、これに当てはまります。またこの例からは、NULLが IN 演算子で有効な値であることもわかります。
上記の例の PIVOT では、GROUP BYを含む下記のクエリと同様の情報を返します。相違点は、列 2 と manufacturer small parts co に対し、PIVOT が値 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 は、複数の列を組み合わせたクロス集計をサポートしていません。ただし、次のようなクエリを使用して、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) です。JOIN式、再帰的 CTE、PIVOT、およびUNPIVOT式のいずれにもPIVOTを適用することはできません。さらに、ネストされていない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);
red、green、blue の入力列で 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 列が、これに当てはまります。
次に、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) です。JOIN式、再帰的 CTE、PIVOT、およびUNPIVOT式のいずれにもUNPIVOTを適用することはできません。さらに、ネストされていないSUPER式、ならびに Redshift Spectrum のネストされたテーブルもサポートされません。UNPIVOT INリストには、入力テーブルの列への参照のみを含める必要があります。INリスト列には、その全体との互換性がある共通の型が必要です。UNPIVOT値列には、下記の共通の型があります。UNPIVOT名前列の型はVARCHARです。INリストの値がエイリアスを持たない場合、UNPIVOTでは、デフォルト値として列名が使用されます。
