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);
SET PENGELOMPOKAN
Menghitung satu atau lebih kumpulan pengelompokan dalam satu pernyataan. Kumpulan pengelompokan adalah kumpulan klausa GROUP BY tunggal, satu set kolom 0 atau lebih yang dengannya Anda dapat mengelompokkan kumpulan hasil kueri. GROUP BY GROUPING SETS setara dengan menjalankan kueri UNION ALL pada satu set hasil yang dikelompokkan berdasarkan kolom yang berbeda. Misalnya, GROUP BY GROUPING SETS ((a), (b)) setara dengan GROUP BY a UNION ALL GROUP BY b.
Contoh berikut mengembalikan biaya produk tabel pesanan dikelompokkan sesuai dengan 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)
ROLLUP
Mengasumsikan hierarki di mana kolom sebelumnya dianggap sebagai orang tua 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 kumpulan hasil yang dikelompokkan terlebih dahulu oleh a, kemudian oleh b sambil mengasumsikan bahwa b adalah ayat dari a. ROLLUP juga mengembalikan baris dengan seluruh hasil yang ditetapkan tanpa pengelompokan kolom.
GROUP BY ROLLUP ((a), (b)) setara dengan GROUP BY GROUPING SETS ((a, b), (a), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan 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
Kelompokkan 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 dicakup oleh ROLLUP. Misalnya, Anda dapat menggunakan GROUP BY CUBE ((a), (b)) untuk mengembalikan kumpulan hasil yang dikelompokkan terlebih dahulu oleh a, kemudian oleh b sambil mengasumsikan bahwa b adalah subbagian dari a, lalu oleh b saja. CUBE juga mengembalikan baris dengan seluruh hasil yang ditetapkan tanpa pengelompokan kolom.
GROUP BY CUBE ((a), (b)) setara dengan GROUP BY GROUPING SETS ((a, b), (a), (b), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan dikelompokkan pertama berdasarkan kategori dan kemudian produk, dengan produk sebagai subdivisi kategori. Berbeda dengan 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)
Fungsi GROUPING/GROUPING_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 dalam kolom pengelompokan. Nilai-nilai NULL ini hanya berfungsi untuk memenuhi format tupel yang kembali.
Saat 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 set hasil mengembalikan nilai 0 atau 1 bit yang sesuai dengan kolom pengelompokan di posisi itu, dan kemudian mengubah nilai itu menjadi bilangan bulat. Jika nilai dalam posisi itu adalah nilai NULL yang dibuat oleh ekstensi agregasi, GROUPING mengembalikan 1. Ia mengembalikan 0 untuk semua nilai lainnya, termasuk nilai NULL yang disimpan.
Misalnya, PENGELOMPOKAN (kategori, produk) dapat mengembalikan 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.
| kolom kategori | 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 PENGELOMPOKAN 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 GROUPING untuk kumpulan pengelompokannya.
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 sebagian dan CUBE
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 level expr2 dan seterusnya.
Contoh berikut menunjukkan sebagian operasi ROLLUP dan CUBE pada tabel pesanan, mengelompokkan terlebih dahulu berdasarkan apakah suatu produk sudah dimiliki sebelumnya dan kemudian menjalankan ROLLUP dan CUBE pada kategori dan kolom 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 pra-dimiliki tidak termasuk dalam operasi ROLLUP dan CUBE, tidak ada total baris besar yang mencakup semua baris lainnya.
Pengelompokan gabungan
Anda dapat menggabungkan beberapa klausa GROUPING SETS/ROLLUP/CUBE untuk menghitung tingkat subtotal yang berbeda. Pengelompokan gabungan mengembalikan produk Cartesian dari kumpulan pengelompokan yang disediakan.
Sintaks untuk menggabungkan klausa GROUPING SETS/ROLLUP/CUBE adalah sebagai berikut.
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
Perhatikan contoh berikut untuk melihat bagaimana pengelompokan gabungan kecil dapat menghasilkan set 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 operasi GROUPING SETS/ROLLUP/CUBE sebagai expr GROUPING SETS Anda untuk membentuk pengelompokan bersarang. Sub pengelompokan di dalam SET PENGELOMPOKAN 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 (produk, pre_owned) berisi kumpulan pengelompokan (), baris yang mewakili total besar diduplikasi.
Catatan penggunaan
-
Klausa GROUP BY mendukung hingga 64 set pengelompokan. Dalam kasus ROLLUP dan CUBE, atau beberapa kombinasi SET PENGELOMPOKAN, ROLLUP, dan CUBE, batasan ini berlaku untuk jumlah kumpulan pengelompokan yang tersirat. Misalnya, GROUP BY CUBE ((a), (b)) dihitung sebagai 4 set pengelompokan, bukan 2.
-
Anda tidak dapat menggunakan konstanta sebagai pengelompokan kolom saat menggunakan ekstensi agregasi.
-
Anda tidak dapat membuat kumpulan pengelompokan yang berisi kolom duplikat.
