Gunakan PIVOT dan UNPIVOT untuk mengonversi antara baris dan kolom-MaxCompute-Alibaba Cloud

PIVOT mengubah tabel sempit (misalnya, product, season, sales_amount) menjadi tabel lebar (misalnya, product, Q1, Q2, Q3, Q4) dengan mengagregasi baris ke dalam kolom. UNPIVOT melakukan sebaliknya—mengubah kolom lebar kembali menjadi baris sehingga mempermudah penyaringan dan analisis data secara seragam.

Kedua kata kunci tersebut ditempatkan dalam klausa FROM dan dapat dikombinasikan dengan JOIN serta klausa lainnya.

PIVOT berada dalam rilis canary dan mungkin belum tersedia untuk semua pengguna.

PIVOT

Sintaks

SELECT ...
FROM ...
PIVOT (
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ...
    FOR (<column> [, <column>] ...)
    IN (
        (<value> [, <value>] ...) AS <new column>
        [, (<value> [, <value>] ...) AS <new column>]
        ...
       )
    )
[...]

Parameter

Parameter	Wajib	Deskripsi
`aggregate function`	Ya	Fungsi agregat yang digunakan untuk konversi baris ke kolom. Untuk daftar fungsi yang didukung, lihat Aggregate functions.
`alias`	Tidak	Alias untuk fungsi agregat. Merupakan bagian dari nama kolom yang dihasilkan.
`column`	Ya	Kolom dalam tabel sumber yang nilainya ingin Anda agregasikan.
`value`	Ya	Nilai baris yang digunakan sebagai dasar pivot (berfungsi sebagai pemilih kolom).
`new column`	Tidak	Nama kolom hasil setelah konversi.

Aturan penamaan kolom

Alias yang Anda definisikan dalam PIVOT menentukan nama kolom output:

Tanpa alias — PIVOT (agg1 for axis1 in ('1', '2', '3')): nama kolom adalah nilai-nilai itu sendiri — '1', '2', '3'.
Satu alias agregat — PIVOT (agg1 AS a for axis1 in ('1', '2', '3')): kolom diberi nama value_aggregateAlias — '1'_a, '2'_a, '3'_a.
Beberapa alias agregat — PIVOT (agg1 AS a, agg2 AS b for axis1 in ('1', '2', '3')): kolom mengikuti pola yang sama untuk setiap agregat — '1'_a, '2'_a, '1'_b, '2'_b.
Nilai ekspresi (sebagian atau seluruhnya) — MaxCompute secara otomatis menghasilkan alias untuk ekspresi yang tidak memiliki alias eksplisit, serta untuk fungsi agregat yang tidak memiliki alias. Kueri ditulis ulang secara internal dengan alias yang dihasilkan, dan kolom diberi nama expressionAlias_aggregateAlias.

Batasan

Fungsi agregat tidak dapat disarangkan di dalam fungsi lain.
Fungsi agregat dapat mencakup fungsi skalar dan kolom yang dikelompokkan, tetapi tidak boleh mengandung fungsi jendela atau fungsi agregat lain.
Kolom yang dirujuk di dalam fungsi agregat harus berasal dari tabel sumber yang dipivot.
alias hanya dapat muncul sebagai bagian dari nama kolom yang dihasilkan—tidak boleh berupa ekspresi.
Ekspresi value hanya dapat mereferensi kolom tabel sumber dan dapat mengandung fungsi skalar, tetapi tidak boleh mengandung fungsi agregat atau fungsi jendela.

Cara kerja PIVOT

PIVOT setara dengan GROUP BY dengan fungsi agregat dan klausa FILTER. Pernyataan berikut menghasilkan output yang sama:

-- Menggunakan PIVOT
SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2,
      ...)
)

-- Setara tanpa PIVOT
SELECT
  k1, ..., kN,
  agg1 AS label1_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label1_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...,
  agg1 AS label2_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label2_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...
FROM source_table
GROUP BY k1, ..., kN

k1, ..., kN adalah kolom dalam tabel sumber yang tidak tercantum dalam fungsi agregat atau klausa FOR.

Contoh

Contoh berikut menggunakan tabel penjualan buah dengan data triwulanan:

-- Buat dan isi tabel
CREATE TABLE mf_cop_sales (
  tran_id   BIGINT,
  productID STRING,
  tran_amt  DECIMAL,
  season    STRING
);

INSERT INTO TABLE mf_cop_sales VALUES
  (1,  'apple',  100, 'Q1'),
  (2,  'orange', 200, 'Q1'),
  (3,  'banana', 300, 'Q1'),
  (4,  'apple',  400, 'Q2'),
  (5,  'orange', 500, 'Q2'),
  (6,  'banana', 600, 'Q2'),
  (7,  'apple',  700, 'Q3'),
  (8,  'orange', 800, 'Q3'),
  (9,  'banana', 700, 'Q3'),
  (10, 'apple',  500, 'Q4'),
  (11, 'orange', 400, 'Q4'),
  (12, 'banana', 200, 'Q4');

Total penjualan per triwulan

Pivot kolom season menjadi empat kolom bernama—satu untuk tiap triwulan:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt)
  FOR season IN ('Q1' AS spring, 'Q2' AS summer, 'Q3' AS autumn, 'Q4' AS winter)
);

Hasil:

+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Total penjualan per produk dengan alias agregat

Menambahkan alias ke fungsi agregat (AS sumbypro) mengubah nama kolom output menjadi 'value'_sumbypro:

SELECT *
FROM (SELECT productid, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt) AS sumbypro
  FOR productid IN ('apple', 'orange', 'banana')
);

Hasil:

+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Penjualan maksimum di Q4

Gunakan MAX untuk menemukan jumlah transaksi tunggal tertinggi di Q4:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (MAX(tran_amt) FOR season IN ('Q4'));

Hasil:

+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOT

Sintaks

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

Parameter

Parameter	Wajib	Deskripsi
`new column of value`	Ya	Nama kolom baru yang menerima nilai dari kolom sumber yang di-unpivot.
`new column of name`	Ya	Nama kolom baru yang menerima nama kolom asli (atau alias-nya).
`column`	Ya	Kolom sumber yang di-unpivot. Namanya mengisi `new column of name`; nilainya mengisi `new column of value`.
`column value`	Tidak	Alias untuk kolom sumber. Menggantikan nama kolom dalam output `new column of name`.

Batasan

Jumlah parameter new column of value harus sama dengan jumlah grup kolom dalam IN (...).
Jumlah parameter new column of name harus sama dengan jumlah grup alias dalam IN (...).
new column of value dan new column of name harus merupakan nama kolom unik dan tidak boleh mengandung ekspresi.
column value dapat berupa konstanta atau ekspresi, tetapi ekspresi tersebut tidak boleh mereferensi nama kolom—harus dievaluasi sebagai konstanta.
Tiap grup kolom dalam IN (...) dapat berisi hingga 100 kolom. Lebih dari 100 kolom menyebabkan pembengkakan data.
Tambahkan EXCLUDE NULLS setelah kata kunci UNPIVOT untuk mengecualikan baris yang berisi nilai null pada kolom yang di-unpivot.

Aturan auto-alias

Jika Anda menghilangkan klausa AS (...) untuk grup kolom, MaxCompute akan menghasilkan alias secara otomatis:

UNPIVOT (measure1 FOR axis IN (c1, c2, c3)) ditulis ulang secara internal sebagai UNPIVOT (measure1 FOR axis IN (c1 AS c1, c2 AS c2, c3 AS c3)).
Dalam kasus lain, MaxCompute menghasilkan alias string untuk kolom yang tidak ditentukan.
Jika Anda hanya menentukan alias untuk sebagian kolom, alias yang ditentukan secara manual harus bertipe STRING agar kompatibel dengan alias yang dihasilkan otomatis. Jika ada alias yang bukan bertipe STRING, tentukan alias untuk semua kolom.

Cara kerja UNPIVOT

UNPIVOT setara dengan CROSS JOIN dengan ekspresi CASE WHEN:

-- Menggunakan UNPIVOT
SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

-- Setara tanpa UNPIVOT
SELECT
    k1, ..., kN,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c11
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c21
      ...
      ELSE NULL
    END AS measure1,
    ...,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c1M
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c2M
      ELSE NULL
    END AS measureM,
    axis1, ..., axisN
FROM source_table
JOIN (VALUES (value11, ..., value1N), (value21, ..., value2N), ...)
  AS generated_table_name(axis1, ..., axisN)

Contoh

Contoh berikut menggunakan tabel penjualan toko dengan data per produk dan per tahun di empat toko:

-- Buat dan isi tabel
CREATE TABLE mf_shops (
  item_id BIGINT,
  year    STRING,
  shop1   DECIMAL,
  shop2   DECIMAL,
  shop3   DECIMAL,
  shop4   DECIMAL
);

WITH shops_table AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
INSERT OVERWRITE TABLE mf_shops SELECT * FROM shops_table;

Konsolidasi kolom toko menjadi baris

Ubah keempat kolom toko menjadi baris, dengan kolom sales untuk jumlah penjualan dan kolom shop untuk nama tokonya:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (shop1, shop2, shop3, shop4));

Hasil:

+------------+------------+------------+-------+
| item_id    | year       | sales      | shop  |
+------------+------------+------------+-------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+-------+

Gunakan alias kustom untuk nama toko

Ganti nama kolom sumber dalam output dengan label string kustom:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (
  shop1 AS 'shop_name_1',
  shop2 AS 'shop_name_2',
  shop3 AS 'shop_name_3',
  shop4 AS 'shop_name_4'
));

Hasil:

+------------+------------+------------+-------------+
| item_id    | year       | sales      | shop        |
+------------+------------+------------+-------------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+-------------+

Kelompokkan kolom sebelum unpivoting

Pasangkan toko menjadi grup (timur dan barat) dan uraikan tiap pasangan menjadi dua kolom nilai (sales1, sales2):

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR shop IN (
    (shop1, shop2) AS 'east_shop',
    (shop3, shop4) AS 'west_shop'
  )
);

Hasil:

+------------+------------+------------+------------+-----------+
| item_id    | year       | sales1     | sales2     | shop      |
+------------+------------+------------+------------+-----------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+-----------+

Gunakan beberapa kolom nama

Tentukan beberapa kolom FOR untuk menangkap dimensi label tambahan (dalam hal ini, nama toko dan lokasi geografis):

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Hasil:

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

Kecualikan baris null dengan EXCLUDE NULLS

Saat baris sumber memiliki NULL di kolom mana pun yang di-unpivot, gunakan EXCLUDE NULLS untuk menghapusnya dari output. Pada contoh berikut, item_id=3 memiliki nilai toko semuanya null dan dikecualikan:

WITH shops AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100),
    (3, 2020, NULL, NULL, NULL, NULL)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
SELECT * FROM shops
UNPIVOT EXCLUDE NULLS (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Hasil (baris item_id=3 tidak muncul):

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+