Contoh PIVOT dan UNPIVOT

PIVOT dan UNPIVOT adalah parameter dalam klausa FROM yang memutar output kueri dari baris ke kolom dan kolom ke baris, masing-masing. Mereka mewakili hasil kueri tabel dalam format yang mudah dibaca. Contoh berikut menggunakan data uji dan kueri untuk menunjukkan cara menggunakannya.

Untuk informasi selengkapnya tentang parameter ini dan parameter lainnya, lihat klausa FROM.

Contoh PIVOT

Siapkan tabel sampel dan data dan gunakan untuk menjalankan contoh query berikutnya.


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);

PIVOT aktif partname dengan AVG agregasi aktif. price


SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
    AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

Hasil query dalam output berikut.


  prop   |  rudder  |  wing
---------+----------+---------
 10.33   | 2.71     | 11.50

Pada contoh sebelumnya, hasilnya diubah menjadi kolom. Contoh berikut menunjukkan GROUP BY kueri yang mengembalikan harga rata-rata dalam baris, bukan di kolom.


SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;

Hasil query dalam output berikut.


 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

PIVOTContoh dengan manufacturer sebagai kolom implisit.


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

Hasil query dalam output berikut.


 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

Kolom tabel masukan yang tidak direferensikan dalam PIVOT definisi ditambahkan secara implisit ke tabel hasil. Ini adalah kasus untuk manufacturer kolom pada contoh sebelumnya. Contoh ini juga menunjukkan bahwa NULL adalah nilai yang valid untuk IN operator.

PIVOTdalam contoh di atas mengembalikan informasi yang sama sebagai query berikut, yang meliputiGROUP BY. Perbedaannya adalah PIVOT mengembalikan nilai 0 untuk kolom 2 dan pabrikansmall parts co. GROUP BYKueri tidak berisi baris yang sesuai. Dalam kebanyakan kasus, PIVOT menyisipkan NULL jika baris tidak memiliki data input untuk kolom tertentu. Namun, agregat hitungan tidak kembali NULL dan 0 merupakan nilai default.


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;

Hasil query dalam output berikut.


 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

Operator PIVOT menerima alias opsional pada ekspresi agregat dan pada setiap nilai untuk operator. IN Gunakan alias untuk menyesuaikan nama kolom. Jika tidak ada alias agregat, hanya alias IN daftar yang digunakan. Jika tidak, alias agregat ditambahkan ke nama kolom dengan garis bawah untuk memisahkan nama.


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

Hasil query dalam output berikut.


 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

Siapkan tabel sampel dan data berikut dan gunakan untuk menjalankan contoh query berikutnya. Data menunjukkan tanggal pemesanan untuk koleksi hotel.


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);

Dalam contoh kueri ini, catatan pemesanan dihitung untuk memberikan total untuk setiap minggu. Tanggal akhir untuk setiap minggu menjadi nama kolom.


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') 
);

Hasil query dalam output berikut.


 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 tidak mendukung CROSSTAB untuk berputar di beberapa kolom. Tetapi Anda dapat mengubah data baris ke kolom, dengan cara yang mirip dengan agregasi dengan PIVOT, dengan kueri seperti berikut ini. Ini menggunakan data sampel pemesanan yang sama dengan contoh sebelumnya.


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;

Contoh kueri menghasilkan tanggal pemesanan yang tercantum di sebelah frasa singkat yang menunjukkan hotel mana yang dipesan.


 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 |

Berikut ini adalah catatan penggunaan untukPIVOT:

PIVOTdapat diterapkan ke tabel, sub-kueri, dan ekspresi tabel umum (CTE). PIVOTtidak dapat diterapkan pada JOIN ekspresi, CTE rekursifPIVOT, atau UNPIVOT ekspresi apa pun. Juga tidak didukung adalah ekspresi SUPER unnested dan tabel bersarang Redshift Spectrum.
PIVOTmendukung fungsiCOUNT,SUM,MIN,MAX, dan AVG agregat.
Ekspresi PIVOT agregat harus berupa panggilan dari fungsi agregat yang didukung. Ekspresi kompleks di atas agregat tidak didukung. Argumen agregat tidak dapat berisi referensi ke tabel selain tabel PIVOT input. Referensi berkorelasi ke kueri induk juga tidak didukung. Argumen agregat mungkin berisi sub-kueri. Ini dapat dikorelasikan secara internal atau pada tabel PIVOT input.
Nilai PIVOT IN daftar tidak dapat berupa referensi kolom atau sub-kueri. Setiap nilai harus jenis yang kompatibel dengan referensi FOR kolom.
Jika nilai IN daftar tidak memiliki alias, PIVOT menghasilkan nama kolom default. Untuk IN nilai konstan seperti 'abc' atau 5 nama kolom default adalah konstanta itu sendiri. Untuk ekspresi kompleks apa pun, nama kolom adalah nama default Amazon Redshift standar seperti. ?column?

Contoh UNPIVOT

Siapkan data sampel dan gunakan untuk menjalankan contoh berikutnya.


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);

UNPIVOTpada kolom input merah, hijau, dan biru.


SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

Hasil query dalam output berikut.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |  23
 blue  |   7
 blue  |  40

Secara default, NULL nilai di kolom input dilewati dan tidak menghasilkan baris hasil.

Contoh berikut menunjukkan UNPIVOT denganINCLUDE NULLS.


SELECT *
FROM (
    SELECT red, green, blue
    FROM count_by_color
) UNPIVOT INCLUDE NULLS (
    cnt FOR color IN (red, green, blue)
);

Berikut ini adalah output yang dihasilkan.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |
 green |  23
 blue  |   7
 blue  |  40
 blue  |

Jika INCLUDING NULLS parameter diatur, nilai NULL input menghasilkan baris hasil.

The following query shows UNPIVOTdengan quality sebagai kolom implisit.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

Hasil query dalam output berikut.


 quality | color | cnt
---------+-------+-----
 high    | red   |  15
 normal  | red   |  35
 low     | red   |  10
 high    | green |  20
 low     | green |  23
 high    | blue  |   7
 normal  | blue  |  40

Kolom tabel input yang tidak direferensikan dalam UNPIVOT definisi ditambahkan secara implisit ke tabel hasil. Dalam contoh, ini adalah kasus untuk quality kolom.

Contoh berikut menunjukkan UNPIVOT dengan alias untuk nilai-nilai dalam IN daftar.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

Hasil query sebelumnya dalam output berikut.


 quality | color | cnt
---------+-------+-----
 high    | r     |  15
 normal  | r     |  35
 low     | r     |  10
 high    | g     |  20
 low     | g     |  23
 high    | b     |   7
 normal  | b     |  40

UNPIVOTOperator menerima alias opsional pada setiap nilai IN daftar. Setiap alias menyediakan kustomisasi data di setiap value kolom.

Berikut ini adalah catatan penggunaan untukUNPIVOT.

UNPIVOTdapat diterapkan ke tabel, sub-kueri, dan ekspresi tabel umum (CTE). UNPIVOTtidak dapat diterapkan pada JOIN ekspresi, CTE rekursifPIVOT, atau UNPIVOT ekspresi apa pun. Juga tidak didukung adalah ekspresi SUPER unnested dan tabel bersarang Redshift Spectrum.
UNPIVOT INDaftar harus berisi hanya referensi kolom tabel masukan. Kolom IN daftar harus memiliki tipe umum yang semuanya kompatibel dengannya. Kolom UNPIVOT nilai memiliki tipe umum ini. Kolom UNPIVOT nama adalah tipeVARCHAR.
Jika nilai IN daftar tidak memiliki alias, UNPIVOT menggunakan nama kolom sebagai nilai default.

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Klausa FROM

JOIN contoh