Postgres: Grouping Sets function

Mencari Kombinasi Hasil Seperti Pivot Table

Photo by Saksham Gangwar on Unsplash

Bayangkan anda ingin melakukan analysis menu makanan dalam kedai makan anda. Konsep kedai anda mudah sahaja: ada Menu Utama dan ada Menu Sampingan. Contohnya seperti di bawah:

Dan anda ingin menjalankan analysis :

1. Analisa Jenis 1: Menu Utama dan Menu Sampingan

2. Analisa Jenis 2: Menu Utama sahaja

3. Analisa Jenis 3: Menu Sampingan sahaja

4. Analisa Jenis 4: Jumlah Semua

Jika menggunakan Excel (ya, saya masih menggunakan Excel). Dengan menggunakan Pivot Table anda mungkin boleh mendapatkan hasil analysis seperti berikut:

Tetapi analisa ini hanya boleh mendapatan analisa Jenis 1 : Menu Utama dan Menu Sampingan, Jenis 2: Menu Utama dan Jenis 4: Semua Menu.

Untuk mendapatkan analisa Jenis 3: Lauk Sampingan, anda perlu membina satu lagi Pivot Table

Dua kali kerja kan?

Bagaimana mahu dapakan hasil seperti di bawah dengan menggunakan SQL Query?

Mari kita mulakan!

Kita create table

CREATE TABLE orders (
    OrderID INT PRIMARY KEY,
    MenuUtama VARCHAR(100),
    MenuSampingan VARCHAR(100),
    JumlahPesanan INT
);

Mari masukkan data

INSERT INTO orders (OrderID, MenuUtama, MenuSampingan, JumlahPesanan) VALUES
(	1111,'Nasi Lemak','Sambal Sotong',20	),
(	1234,'Nasi Lemak','Sambal Udang',25	),
(	2347,'Nasi Lemak','Sambal Sotong',20	),
(	9987,'Bihun Goreng','Telur Goreng',10	),
(	8893,'Nasi Kerabu','Gulai Ikan',15	),
(	2222,'Nasi Kerabu','Ayam Panggang',20	),
(	3333,'Nasi Lemak','Gulai Ikan',15	),
(	4445,'Nasi Kerabu','Gulai Ikan',15	);

Kita boleh dapatkan hasil ini dengan menggunakan kombinasi beberapa SQL dan menggunakan UNION ALL

Kombinasi 1: Menu Utama dan Menu Sampingan

select MenuUtama, MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuUtama, MenuSampingan

Kombinasi 2: Menu Utama sahaja

select MenuUtama, '' as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuUtama

Kombinasi 3: Menu Sampingan sahaja

select '' as MenuUtama, MenuSampingan as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuSampingan

Kombinasi 4: Semua Menu

select '' as MenuUtama, '' as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders

Gambungkan dengan menggunakan UNION ALL

select MenuUtama as MenuUtama, MenuSampingan as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuUtama, MenuSampingan
UNION ALL
select MenuUtama as MenuUtama, '' as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuUtama
UNION ALL
select '' as MenuUtama, MenuSampingan as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders
group by MenuSampingan
UNION ALL
select '' as MenuUtama, '' as MenuSampingan, SUM(JumlahPesanan) as JumlahPesanan
from orders

Hasil Query

Di sini ada sedikit kelemahan:

1. SQL Query jadi panjang

2. Table akan discan beberapa kali

Ada cara yang lebih mudah?

Photo by Takehiro Tomiyama on Unsplash

Ada.

Dengan menggunakkan GROUPING SETS

Begini SQLnya

SELECT MenuUtama,
       MenuSampingan,
       SUM(JumlahPesanan)
FROM orders
GROUP BY
    GROUPING SETS ( (MenuUtama, MenuSampingan),
                    (MenuUtama),
                    (MenuSampingan),
                    ()
    );

Hasil Query

Mudah dan table hanya akan discan sekali sahaja.