Amazon Redshift tidak akan lagi mendukung penggunaan Python UDF setelah 30 Juni 2026. Kami akan mulai menegakkannya secara bertahap. Untuk informasi lebih lanjut tentang detail opsi akhir masa pakai dan migrasi Python, lihat posting blog
Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Ekstensi agregasi
Amazon Redshift mendukung ekstensi agregasi untuk melakukan pekerjaan beberapa operasi GROUP BY dalam satu pernyataan.
Contoh untuk ekstensi agregasi menggunakan orders tabel, yang menyimpan data penjualan untuk perusahaan elektronik. Anda dapat membuat orders dengan yang berikut ini.
CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50);
PENGELOMPOKAN SET
Menghitung satu atau lebih set pengelompokan dalam satu pernyataan. Kumpulan pengelompokan adalah kumpulan klausa GROUP BY tunggal, kumpulan 0 atau lebih kolom yang dengannya Anda dapat mengelompokkan kumpulan hasil kueri. GROUP BY GROUPING SET setara dengan menjalankan kueri UNION ALL pada satu set hasil yang dikelompokkan oleh kolom yang berbeda. Misalnya, GROUP BY GROUPING SET ((a), (b)) setara dengan GROUP BY a UNION ALL GROUP BY b.
Contoh berikut mengembalikan biaya produk tabel pesanan yang dikelompokkan menurut kategori produk dan jenis produk yang dijual.
SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows)
MENGGULUNG
Mengasumsikan hierarki di mana kolom sebelumnya dianggap sebagai induk dari kolom berikutnya. ROLLUP mengelompokkan data berdasarkan kolom yang disediakan, mengembalikan baris subtotal tambahan yang mewakili total di semua tingkat kolom pengelompokan, selain baris yang dikelompokkan. Misalnya, Anda dapat menggunakan GROUP BY ROLLUP ((a), (b)) untuk mengembalikan set hasil yang dikelompokkan pertama oleh a, kemudian dengan b sambil mengasumsikan bahwa b adalah subbagian dari a. ROLLUP juga mengembalikan baris dengan seluruh hasil set tanpa mengelompokkan kolom.
GROUP BY ROLLUP ((a), (b)) setara dengan GROUP BY GROUPING SET ((a, b), (a), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan yang dikelompokkan pertama berdasarkan kategori dan kemudian produk, dengan produk sebagai subdivisi kategori.
SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows)
KUBUS
Mengelompokkan data berdasarkan kolom yang disediakan, mengembalikan baris subtotal tambahan yang mewakili total di semua tingkat kolom pengelompokan, selain baris yang dikelompokkan. CUBE mengembalikan baris yang sama dengan ROLLUP, sambil menambahkan baris subtotal tambahan untuk setiap kombinasi kolom pengelompokan yang tidak tercakup oleh ROLLUP. Misalnya, Anda dapat menggunakan GROUP BY CUBE ((a), (b)) untuk mengembalikan set hasil yang dikelompokkan pertama oleh a, kemudian dengan b sambil mengasumsikan bahwa b adalah subbagian dari a, lalu oleh b saja. CUBE juga mengembalikan baris dengan seluruh hasil set tanpa mengelompokkan kolom.
GROUP BY CUBE ((a), (b)) setara dengan GRUP DENGAN MENGELOMPOKKAN SET ((a, b), (a), (b), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan yang dikelompokkan pertama berdasarkan kategori dan kemudian produk, dengan produk sebagai subdivisi kategori. Tidak seperti contoh sebelumnya untuk ROLLUP, pernyataan mengembalikan hasil untuk setiap kombinasi kolom pengelompokan.
SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows)
GROUPING/GROUPINGFungsi _ID
ROLLUP dan CUBE menambahkan nilai NULL ke set hasil untuk menunjukkan baris subtotal. Misalnya, GROUP BY ROLLUP ((a), (b)) mengembalikan satu atau lebih baris yang memiliki nilai NULL di kolom pengelompokan b untuk menunjukkan bahwa mereka adalah subtotal bidang di kolom pengelompokan a. Nilai NULL ini hanya berfungsi untuk memenuhi format tupel yang dikembalikan.
Ketika Anda menjalankan operasi GROUP BY dengan ROLLUP dan CUBE pada relasi yang menyimpan nilai NULL itu sendiri, ini dapat menghasilkan kumpulan hasil dengan baris yang tampaknya memiliki kolom pengelompokan yang identik. Kembali ke contoh sebelumnya, jika kolom pengelompokan b berisi nilai NULL yang disimpan, GROUP BY ROLLUP ((a), (b)) mengembalikan baris dengan nilai NULL di kolom pengelompokan b yang bukan subtotal.
Untuk membedakan antara nilai NULL yang dibuat oleh ROLLUP dan CUBE, dan nilai NULL yang disimpan dalam tabel itu sendiri, Anda dapat menggunakan fungsi GROUPING, atau alias GROUPING_ID. GROUPING mengambil satu set pengelompokan sebagai argumennya, dan untuk setiap baris dalam kumpulan hasil mengembalikan nilai bit 0 atau 1 yang sesuai dengan kolom pengelompokan di posisi itu, dan kemudian mengubah nilai itu menjadi bilangan bulat. Jika nilai di posisi itu adalah nilai NULL yang dibuat oleh ekstensi agregasi, GROUPING mengembalikan 1. Ini mengembalikan 0 untuk semua nilai lainnya, termasuk nilai NULL yang disimpan.
Misalnya, GRUPING (kategori, produk) dapat mengembalikan nilai-nilai berikut untuk baris tertentu, tergantung pada nilai kolom pengelompokan untuk baris tersebut. Untuk tujuan contoh ini, semua nilai NULL dalam tabel adalah nilai NULL yang dibuat oleh ekstensi agregasi.
| kategori kolom | kolom produk | Nilai bit fungsi PENGELOMPOKAN | Nilai desimal |
|---|---|---|---|
| bukan NULL | bukan NULL | 00 | 0 |
| bukan NULL | NULL | 01 | 1 |
| NULL | bukan NULL | 10 | 2 |
| NULL | NULL | 11 | 3 |
Fungsi GROUPING muncul di bagian daftar SELECT dari kueri dalam format berikut.
SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
Contoh berikut adalah sama dengan contoh sebelumnya untuk CUBE, tetapi dengan penambahan fungsi GRUPING untuk kumpulan pengelompokan.
SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows)
ROLLUP dan CUBE sebagian
Anda dapat menjalankan operasi ROLLUP dan CUBE hanya dengan sebagian dari subtotal.
Sintaks untuk operasi ROLLUP dan CUBE sebagian adalah sebagai berikut.
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
Di sini, klausa GROUP BY hanya membuat baris subtotal pada tingkat expr2 dan seterusnya.
Contoh berikut menunjukkan operasi ROLLUP dan CUBE sebagian pada tabel pesanan, mengelompokkan terlebih dahulu berdasarkan apakah suatu produk sudah dimiliki sebelumnya dan kemudian menjalankan ROLLUP dan CUBE pada kolom kategori dan produk.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows)
Karena kolom bekas tidak disertakan dalam operasi ROLLUP dan CUBE, tidak ada baris total keseluruhan yang mencakup semua baris lainnya.
Pengelompokan gabungan
Anda dapat menggabungkan beberapa SETS/ROLLUP/CUBE klausa GRUPING untuk menghitung tingkat subtotal yang berbeda. Pengelompokan gabungan mengembalikan produk Cartesian dari kumpulan pengelompokan yang disediakan.
Sintaks untuk menggabungkan SETS/ROLLUP/CUBE klausa GROUPING adalah sebagai berikut.
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
Pertimbangkan contoh berikut untuk melihat bagaimana pengelompokan gabungan kecil dapat menghasilkan kumpulan hasil akhir yang besar.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows)
Pengelompokan bersarang
Anda dapat menggunakan SETS/ROLLUP/CUBE operasi GRUPING sebagai GROUPING SETS expr untuk membentuk pengelompokan bersarang. Sub pengelompokan di dalam GROUPING SET bersarang diratakan.
Sintaks untuk pengelompokan bersarang adalah sebagai berikut.
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
Pertimbangkan contoh berikut.
SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows)
Perhatikan bahwa karena ROLLUP (kategori) dan CUBE (product, pre_own) berisi set pengelompokan (), baris yang mewakili total keseluruhan diduplikasi.
Catatan penggunaan
-
Klausa GROUP BY mendukung hingga 64 set pengelompokan. Dalam kasus ROLLUP dan CUBE, atau beberapa kombinasi GROUPING SET, ROLLUP, dan CUBE, batasan ini berlaku untuk jumlah set pengelompokan yang tersirat. Misalnya, GROUP BY CUBE ((a), (b)) dihitung sebagai 4 set pengelompokan, bukan 2.
-
Anda tidak dapat menggunakan konstanta sebagai kolom pengelompokan saat menggunakan ekstensi agregasi.
-
Anda tidak dapat membuat kumpulan pengelompokan yang berisi kolom duplikat.
