Menulis kueri yang menentukan beberapa pengelompokan dengan set pengelompokan
Anda menggunakan klausa GROUP BY dalam pernyataan SELECT di Transact-SQL untuk mengatur baris dalam grup, biasanya untuk mendukung agregasi. Namun, jika Anda perlu mengelompokkan berdasarkan atribut yang berbeda pada saat yang sama, misalnya untuk melaporkan pada tingkat yang berbeda, Anda biasanya memerlukan beberapa kueri yang dikombinasikan dengan UNION ALL. Sebagai gantinya, jika Anda perlu menghasilkan agregat dari beberapa pengelompokan dalam kueri yang sama, Anda dapat menggunakan subklausul GROUPING SET dari klausul GROUP BY di T-SQL. GROUPING SETS menyediakan alternatif untuk menggunakan UNION ALL guna menggabungkan hasil dari beberapa kueri individual, masing-masing dengan klausul GROUP BY sendiri.
Menggunakan subklausul GROUPING SETS
Untuk menggunakan GROUPING SETS, Anda menentukan kombinasi atribut yang dikelompokkan, seperti dalam contoh sintaks berikut:
SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY
GROUPING SETS(
(<column_name>),--one or more columns
(<column_name>),--one or more columns
() -- empty parentheses if aggregating all rows
);
Misalnya, misalkan Anda ingin menggunakan GROUPING SETS untuk mengagregasi pada kolom Category dan Cust dari tabel Sales.CategorySales, di samping notasi tanda kurung kosong untuk mengagregasikan semua baris:
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY
GROUPING SETS((Category),(Cust),())
ORDER BY Category, Cust;
Hasilnya akan terlihat seperti ini:
| Golongan | Cust | TotalQty |
|---|---|---|
| NULL | NULL | 999 |
| NULL | 1 | 80 |
| NULL | 2 | 12 |
| NULL | 3 | 154 |
| NULL | 4 | 241 |
| NULL | 5 | 512 |
| Minuman | NULL | 513 |
| Bahan Penyedap | NULL | 114 |
| Makanan Manis | NULL | 372 |
Perhatikan kehadiran NULL dalam hasilnya. NULL dapat dikembalikan karena NULL disimpan dalam sumber yang mendasarinya, atau karena itu adalah tempat penampung dalam baris yang dihasilkan sebagai hasil agregat. Misalnya, dalam hasil sebelumnya, baris pertama menampilkan NULL, NULL, 999. Ini mewakili baris total yang besar. NULL dalam kolom Category dan Cust adalah tempat penampung karena baik Category maupun Cust tidak mengambil bagian dalam agregasi.
Tip
Jika Anda ingin tahu apakah NULL menandai tempat penampung atau berasal dari data yang mendasarinya, Anda dapat menggunakan GROUPING_ID. Kunjungi halaman referensi untuk GROUPING_ID untuk informasi selengkapnya.
Menggunakan subklausul CUBE dan ROLLUP
Seperti GROUPING SETS, subklausul CUBE dan ROLLUP juga memungkinkan beberapa pengelompokan untuk mengaggregasi data. Namun, CUBE dan ROLLUP tidak memerlukan Anda untuk menentukan setiap set atribut ke grup. Sebaliknya, diberikan satu set kolom, CUBE akan menentukan semua kemungkinan kombinasi dan pengelompokan output. ROLLUP membuat kombinasi, dengan asumsi kolom input mewakili hierarki. Oleh karena itu, CUBE dan ROLLUP dapat dianggap sebagai pintasan ke GROUPING SET.
Untuk menggunakan CUBE, tambahkan kata kunci CUBE ke klausul GROUP BY dan berikan daftar kolom untuk grup. Misalnya, untuk mengelompokkan pada semua kombinasi kolom Category dan Cust, Anda akan menggunakan sintaksis berikut dalam kueri Anda:
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust);
Ini menghasilkan pengelompokan untuk kombinasi berikut: (Category, Cust), (Cust, Category), (Cust), (Category) dan agregat pada semua kosong ():
| Golongan | Cust | TotalQty |
|---|---|---|
| Minuman | 1 | 36 |
| Bahan Penyedap | 1 | 44 |
| NULL | 1 | 80 |
| Minuman | 2 | 5 |
| Makanan Manis | 2 | 7 |
| NULL | 2 | 12 |
| Minuman | 3 | 105 |
| Bahan Penyedap | 3 | 4 |
| Makanan Manis | 3 | 45 |
| NULL | 3 | 154 |
| ... | ||
| NULL | NULL | 999 |
| Minuman | NULL | 513 |
| Bahan Penyedap | NULL | 114 |
| Makanan Manis | NULL | 372 |
Untuk menggunakan ROLLUP, Anda akan menambahkan kata kunci ROLLUP ke klausul GROUP BY dan memberikan daftar kolom untuk grup. Misalnya, untuk mengelompokkan pada kombinasi kolom Category, Subcategory, dan Produk, Anda akan menggunakan sintaksis berikut dalam kueri Anda:
SELECT Category, Subcategory, Product, SUM(Qty) AS TotalQty
FROM Sales.ProductSales
GROUP BY ROLLUP(Category,Subcategory, Product);
Ini akan menghasilkan pengelompokan untuk kombinasi berikut: (Kategori, Subcategory, Produk), (Category, Subcategory), (Kategori), dan agregat pada semua kosong (). Urutan di mana kolom disediakan berpengaruh: ROLLUP mengasumsikan bahwa kolom terdaftar dalam urutan yang mengekspresikan hierarki. Ini menyediakan subtotal untuk setiap pengelompokan, bersama dengan total besar untuk semua pengelompokan di akhir.