KAL
Menu

Grouping set

Menghasilkan subtotal dan total dengan ROLLUP, CUBE, dan GROUPING SETS.

Daftar isi

Topik ini membahas grouping set, kemampuan PostgreSQL menghitung beberapa tingkat pengelompokan dalam satu query, sehingga subtotal dan total tidak perlu disatukan secara manual. Tiga bentuknya dikenal, yaitu GROUPING SETS yang eksplisit, ROLLUP untuk hierarki, dan CUBE untuk seluruh kombinasi.

Blok penyiapan dataset#

bash 
# data penjualan musiman untuk latihan grouping set
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori, penjualan CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
CREATE TABLE penjualan (
    id       SERIAL PRIMARY KEY,
    wilayah  TEXT NOT NULL,
    produk   TEXT NOT NULL,
    jumlah   INTEGER NOT NULL
);
INSERT INTO penjualan (wilayah, produk, jumlah) VALUES
    ('Timur', 'Label Buku', 40),
    ('Timur', 'Kartu Anggota', 25),
    ('Barat', 'Label Buku', 30),
    ('Barat', 'Kartu Anggota', 35);
SQL

Tabel penjualan kecil ini dipakai karena subtotalnya mudah dihitung ulang dengan mata, yaitu total keseluruhan 130.

GROUPING SETS yang eksplisit#

GROUPING SETS mendaftar setiap kombinasi kelompok yang diinginkan, dan server menjalankan seluruhnya dalam satu query dengan hasil yang disatukan. Baris subtotal membiarkan kolom kelompoknya kosong, tanda yang dibaca melalui fungsi GROUPING.

bash 
# subtotal per wilayah, per produk, dan total
psql -U postgres -d perpustakaan -c "SELECT wilayah, produk, SUM(jumlah) AS total FROM penjualan GROUP BY GROUPING SETS ((wilayah), (produk), ()) ORDER BY wilayah NULLS LAST, produk NULLS LAST;"
text Keluaran 
 wilayah |    produk     | total
---------+---------------+-------
 Barat   |               |    65
 Timur   |               |    65
         | Kartu Anggota |    60
         | Label Buku    |    70
         |               |   130
(5 rows)

Empat baris hasil terdiri atas dua subtotal wilayah, dua subtotal produk, dan satu baris total yang kedua kolomnya kosong. Tanpa grouping set, tiga tingkat ini ditulis tiga query yang disatukan manual, dan keselarasannya menjadi tanggung jawab penulisnya.

ROLLUP untuk hierarki#

ROLLUP menghitung setiap awalan hierarki sampai total, sehingga ROLLUP (wilayah, produk) menghasilkan per pasangan, subtotal per wilayah, dan total keseluruhan. Bentuk ini cocok untuk struktur yang benar-benar berjenjang.

bash 
# detail, subtotal wilayah, dan total dalam satu query
psql -U postgres -d perpustakaan -c "SELECT wilayah, produk, SUM(jumlah) AS total FROM penjualan GROUP BY ROLLUP (wilayah, produk) ORDER BY wilayah NULLS LAST, produk NULLS LAST;"
text Keluaran 
 wilayah |    produk     | total
---------+---------------+-------
 Barat   | Kartu Anggota |    35
 Barat   | Label Buku    |    30
 Barat   |               |    65
 Timur   | Kartu Anggota |    25
 Timur   | Label Buku    |    40
 Timur   |               |    65
         |               |   130
(7 rows)

Baris dengan wilayah terisi dan produk kosong adalah subtotal wilayahnya, sedangkan baris dengan keduanya kosong adalah total. Urutan kolom pada ROLLUP menentukan hierarkinya, sehingga menukarnya menghasilkan subtotal yang berbeda makna.

CUBE untuk seluruh kombinasi#

CUBE menghasilkan grouping set untuk seluruh kombinasi kolomnya, termasuk per kolom tunggal dan total. Dua kolom menghasilkan empat tingkat, sedangkan empat kolom menghasilkan enam belas tingkat, sehingga pertumbuhannya eksponensial dan bentuk ini dipakai pada dimensi yang memang sedikit.

bash 
# seluruh tingkat kombinasi wilayah dan produk
psql -U postgres -d perpustakaan -c "SELECT wilayah, produk, SUM(jumlah) AS total, GROUPING(wilayah, produk) AS tingkat FROM penjualan GROUP BY CUBE (wilayah, produk) ORDER BY tingkat, wilayah NULLS LAST, produk NULLS LAST;"
text Keluaran 
 wilayah |    produk     | total | tingkat
---------+---------------+-------+---------
 Barat   | Kartu Anggota |    35 |       0
 Barat   | Label Buku    |    30 |       0
 Timur   | Kartu Anggota |    25 |       0
 Timur   | Label Buku    |    40 |       0
 Barat   |               |    65 |       1
 Timur   |               |    65 |       1
         | Kartu Anggota |    60 |       2
         | Label Buku    |    70 |       2
         |               |   130 |       3
(9 rows)

Fungsi GROUPING mengembalikan bitmask yang menandai kolom mana yang kosong karena subtotal, dan nilainya menjadi cara baku memilah baris detail, subtotal, dan total pada tampilan akhir. Tanpa fungsi ini, baris kosong dari data sungguhan tidak dapat dibedakan dari baris subtotal.

Memilah baris detail dan subtotal pada tampilan#

Keluaran grouping set menyatukan detail dan subtotal dalam satu daftar, sehingga tampilan akhir lazimnya menandai jenis setiap baris. Ekspresi CASE atas fungsi GROUPING memberi label yang jelas, dan kolom label itu pula yang menjadi sumber pengurutan.

bash 
# baris berlabel detail, subtotal, dan total
psql -U postgres -d perpustakaan -c "SELECT CASE WHEN GROUPING(wilayah) = 1 AND GROUPING(produk) = 1 THEN 'total' WHEN GROUPING(produk) = 1 THEN 'subtotal ' || wilayah ELSE wilayah || ' / ' || produk END AS baris, SUM(jumlah) AS total FROM penjualan GROUP BY ROLLUP (wilayah, produk) ORDER BY GROUPING(wilayah, produk), baris;"
text Keluaran 
         baris         | total
-----------------------+-------
 Barat / Kartu Anggota |    35
 Barat / Label Buku    |    30
 Timur / Kartu Anggota |    25
 Timur / Label Buku    |    40
 subtotal Barat        |    65
 subtotal Timur        |    65
 total                 |   130
(7 rows)

Keluaran enam baris terbaca sebagai laporan utuh, yaitu empat baris detail, satu subtotal Timur, dan satu total. Pola pelabelan ini dipindahkan ke view atau ke lapisan tampilan begitu laporannya dipakai rutin, sebagaimana dibahas pada modul view, fungsi, trigger.

Kesalahan umum#

Kesalahan pertama adalah membaca baris kosong sebagai data sungguhan, padahal baris itu subtotal, dan pemilahan melalui GROUPING dilakukan sebelum data dikonsumsi. Kesalahan kedua adalah memakai CUBE pada dimensi yang banyak, sehingga jumlah tingkatnya bertambah banyak secara cepat, dan ROLLUP atau GROUPING SETS eksplisit menjadi pilihan yang lebih terarah.

Kesalahan ketiga adalah mengurutkan hasil grouping set tanpa memikirkan posisi subtotal, sehingga baris total bercampur baris detail secara acak, dan ORDER BY dengan NULLS LAST berpasangan GROUPING menjaga tampilannya teratur. Kesalahan keempat adalah menghitung ulang subtotal di aplikasi, padahal server menghasilkannya sekaligus dengan biaya satu kali pembacaan data.

Ringkasan#

  • GROUPING SETS mendaftar kombinasi kelompok yang diinginkan, termasuk set kosong untuk baris total.
  • ROLLUP menghasilkan detail dan seluruh subtotal hierarki, dengan urutan kolomnya sebagai tingkatannya.
  • CUBE menghasilkan seluruh kombinasi kolom, dan jumlah tingkatnya tumbuh eksponensial terhadap jumlah dimensi.
  • Fungsi GROUPING membedakan baris subtotal dari data sungguhan, dan ORDER BY NULLS LAST menjaga tampilannya.

Referensi#