Statemen "WITH ROLLUP" pada Query GROUP di MySQL
Soeleman 23 Maret 2017
Database adalah tempat kita menyimpan data. Data yang disimpan itu berbentuk tabular data yang tersusun sesuai kebutuhan. Untuk memproses data sampai siap ditampilkan data tersebut bukanlah tempat yang efisien untuk itu. Lalu bagaimana bila kita diminta membuat resultset seperti itu?
Table
Sekarang kita siapkan table pendukung untuk tulisan ini.
Table Sales
Table Sales ini berisi data sebanyak empat bulan, yang tiap bulannya jumlah dan nilainya.
Target Resultset
Hasil yang diharapkan akan seperti ini. Memang tidak seperti biasa, ini lebih mirip report yang sudah hampir jadi.
Pada link yang berwarna orange, menunjukkan hubungan penambahan dari nilai per-bulan dengan akumulasi nilai perbulan. Sedangkan yang berwarna merah menunjukkan bagaimana nilai per-bulan di akumulasi. Kita lihat bulan januari nilainya 6000 dan nilai itu akan ditambahkan pada bulan febuari yang bernilai 5000, sehingga ia akan bernilai 11000. Nilai ini pun akan ditambahkan pada bulan maret dan seterusnya.
Tidak lupa ada tambahan total dari seluruh bulan, lihat kotak warna biru.
WITH RollUp
MySQL memiliki fitur WITH ROLLUP. Fitur ini bisa menyelipkan data per-group. Jadi dia bisa menyisipkan row per-group-nya pada hasil resultset. Dan caranya mudah sekali, cukup menambahkan WITH ROLLUP pada akhir sintak GROUP BY.
Hasil resultset-nya cukup menarik. Yang berwarna merah itu adalah nilai per-bulan-nya. Sedangkan yang berwarna biru adalah total seluruh bulan.
Plain SQL
Dengan WITH ROLLUP memang lebih singkat tapi kadang tidak banyak orang mudah mengerti itu. Hanya dengan tambahan itu pada GROUP BY akan menghasilkan resultset yang berbeda.
Untuk beberapa kasus kadang kita perlu juga membuatnya secara plain-sql. Caranya mudah saja, pada dasarnya ini adalah kumpulan beberapa resultset. Kira-kira akan seperti dibawah emulasinya.
Union RollUp
Kembali kepada tujuan resultset yang kita inginkan. Disini kita akan tetap menggunakan WITH ROLLUP dan dalam kasus ini kita juga harus melakukan sesuatu terhadap incremental nilai per-bulan-nya.
View
Membuat view ini bukan keharusan. Ini hanya membuat solusi query kita terlihat lebih sederhana saja. Bisa saja kita buat menjadi sub-query.
Query ini akan memberikan resultset dasar kita agar dapat melengkapi tujuan akhirnya.
SQL
Dan caranya sederhana, cukup kita gabungkan dua resultset. Hasil resultset yang kita dapat melalui WITH ROLLUP di tambahkan resultset yang incremental per-bulan.
Pada resultset kedua, kita limit dengan jumlah total record yang ada di table sales. Disini kita menggunakan tanggal(SaleOn), TotalRec dan _Prog agar susunannya seperti yang diharapkan.
Penutup
Beberapa fitur yang ada didalam database kadang terkesan ajaib kalau kita melihat hasilnya. Ini memang menyingkatkan query-nya, sayangnya kadang ini membuat orang jadi kurang teliti bilamana menemui masalah dengan query-nya.
WITH ROLLUP fitur yang seperti agak berlebihan kalau untuk sebuah resultset. Pada dasarnya dalam sql kita bekerja dengan resultset yang berarti data yang berbentuk tabular. Dengan fitur ini kita seperti menyediakan data yang hampir jadi ke pengguna data itu. Yang seharusnya ada semacam layer yang mengolah data ini ke UI baik bentuk report atau form. Tapi memang kenyataannya kita tidak bisa se-ideal itu, karena kebanyakkan pragmatis kadang lebih diterima.
Referensi
Perhatian! Code yang ditampilkan dalam tulisan ini merupakan ilustrasi dari yang ingin dipaparkan dan bukan production ready code. Sudah banyak kejadian karena asal meng-copy-and-paste tanpa mengerti code yang diambil itu ke dalam production. Selain itu perlu ada tambahan code dan test sebelum siap untuk digunakan secara utuh.