Bahasa SQL‎ > ‎

Top N Grouping Dengan Penggunaan Variable

Contents

  1. 1 Pendahuluan
  2. 2 Tahap Pertama : Menentukan Grouping Yang Diinginkan
  3. 3 Tahap Kedua : Variable dan Subquery
  4. 4 Tahap Ketiga : Top N Filter
  5. 5 Penutup
  6. 6 Bacaan Lebih Lanjut

Pendahuluan

Pada beberapa laporan / report summary kita kebutuhan untuk dapat menghasilkan top N dari tiap grouping sangatlah penting dan sering sekali jadi tuntutan manajemen / direksi. 

Sebagai contoh, misalkan pada suatu perusahaan kendaraan multi nasional ingin melihat laporan nilai penjualan 3 teratas dari produk dengan pengelompokan tiap negara pada periode tertentu. 

Bagi Anda yang sudah mengenal MDX tentunya solusi ini sangat gampang, yaitu menggunakan function generate dan topcount seperti terlihat pada contoh artikel pada link ini.

Bagaimana kalau kita menerapkannya pada MySQL ? Dengan penggunaan variable dan subquery artikel berikut akan memberikan solusi tersebut.

Tahap Pertama : Menentukan Grouping Yang Diinginkan

Tentunya pertama kita ingin menentukan grouping dan nilai aggregate yang kita inginkan, setelah mendapatkan query tersebut kita akan menjadikannya sebagai subset yang akan kita query lagi.

Sebagai contoh dari database PHI-Minimart, kita akan melakukan perhitungan jumlah unit produk yang dibeli oleh pengunjung dari tiap cabang dan diurutkan dari nilai terbesar ke terkecil dengan query berikut :

SELECT kode_produk, kode_cabang, SUM(jumlah_pembelian) AS jumlah_beli 
FROM tr_penjualan
GROUP BY kode_cabang, kode_produk
ORDER BY kode_cabang, jumlah_beli DESC

Hasil Eksekusi akan terlihat sebagai berikut... dengan beberapa row dibuang untuk mempersingkat penulisan.

Hasil Eksekusi Query

kode_produk kode_cabang jumlah_beli
PROD-0000018 CABANG-039 38786
PROD-0000029 CABANG-039 38728
PROD-0000020 CABANG-039 38435
PROD-0000010 CABANG-039 38328
PROD-0000001 CABANG-039 38202
PROD-0000041 CABANG-039 38116
...
...
PROD-0000025 CABANG-047 38829
PROD-0000018 CABANG-047 38668
PROD-0000012 CABANG-047 38603
PROD-0000008 CABANG-047 38309
PROD-0000032 CABANG-047 38190
PROD-0000006 CABANG-047 38133
...
...
PROD-0000021 CABANG-065 39037
PROD-0000023 CABANG-065 38913
PROD-0000036 CABANG-065 38483
PROD-0000013 CABANG-065 38365
PROD-0000017 CABANG-065 38311
PROD-0000028 CABANG-065 38188
....
....


Tahap Kedua : Variable dan Subquery

Query yang telah kita putuskan di atas akan kita masukkan sebagai subquery, dan dari hasil tersebut kita mengambil nilai informasi iterasi (penomoran) - seperti yang ditunjukkan pada artikel Penggunaan Variable di MySQL.

Variable yang kita gunakan harus memiliki fungsi berikut  :
  • dapat mengambil nomor baris tiap iterasi row.
  • dapat di-reset berdasarkan perpindahan group (dalam hal ini cabang).

Konstruksi query baru kita adalah sebagai berikut :

SELECT T1.*,
     CASE WHEN @grouping != T1.kode_cabang THEN 
               @nomorbaris := 1 
     ELSE 
               @nomorbaris := @nomorbaris + 1 
     END AS nomorbaris,          
     CASE WHEN @grouping != T1.kode_cabang THEN 
               @grouping := T1.kode_cabang 
     ELSE 
               @grouping 
     END AS Grouping
     FROM
     ( 
        SELECT kode_produk, kode_cabang, SUM(jumlah_pembelian) AS jumlah_beli FROM tr_penjualan
        GROUP BY kode_cabang, kode_produk
        ORDER BY kode_cabang, jumlah_beli DESC)
AS T1,(SELECT @nomorbaris := 0, @grouping := '') T

Hasil Eksekusi akan menampilkan hasil yang sama dengan eksekusi sebelumnya kecuali ada 2 penambahan field baru yaitu nomorbaris dan Grouping. Juga terlihat pada hasil, tiap perpindahan group kode_cabang akan membuat nomorbaris kembali dimulai dari nilai 1.

Hasil Eksekusi

kode_produk kode_cabang     jumlah_beli nomorbaris  Grouping
PROD-0000018 CABANG-039 38786         1       CABANG-039
PROD-0000029 CABANG-039 38728         2       CABANG-039
PROD-0000020 CABANG-039 38435         3       CABANG-039
PROD-0000010 CABANG-039 38328         4       CABANG-039
PROD-0000001 CABANG-039 38202         5       CABANG-039
...
...
PROD-0000025 CABANG-047 38829         1       CABANG-047
PROD-0000018 CABANG-047 38668         2       CABANG-047
PROD-0000012 CABANG-047 38603         3       CABANG-047
PROD-0000008 CABANG-047 38309         4       CABANG-047
PROD-0000032 CABANG-047 38190         5       CABANG-047
...
...
PROD-0000021 CABANG-065 39037         1       CABANG-065
PROD-0000023 CABANG-065 38913         2       CABANG-065
PROD-0000036 CABANG-065 38483         3       CABANG-065
PROD-0000013 CABANG-065 38365         4       CABANG-065
PROD-0000017 CABANG-065 38311         5       CABANG-065
...
...


Tahap Ketiga : Top N Filter

Setelah mendapatkan konstruksi yang mendapatkan referensi nomorbaris, sekarang tinggal masalah gampang, yaitu jadikan subquery di atas sebagai subquery baru dan lakukan filter top n yang kita inginkan berdasarkan referensi nomor baris tersebut. 

Sebagai contoh, kita ingin mendapatkan top 3 nilai penjualan tertinggi produk dari tiap cabang, maka tambahkan statement "where @nomorbaris < 4" dari subquery yang kita bentuk dari query di tahap kedua. 

Query lengkapnya sekarang terlihat sebagai berikut :

SELECT T2.kode_cabang, T2.kode_produk, T2.jumlah_beli, T2.nomorbaris FROM ( 
    SELECT T1.*,
     CASE WHEN @grouping != T1.kode_cabang THEN 
               @nomorbaris := 1 
     ELSE
               @nomorbaris := @nomorbaris + 1 
     END AS nomorbaris,           
     CASE WHEN @grouping != T1.kode_cabang THEN 
               @grouping := T1.kode_cabang 
     ELSE 
               @grouping 
     END AS Grouping
     FROM
     ( 
        SELECT kode_produk, kode_cabang, SUM(jumlah_pembelian) AS jumlah_beli FROM tr_penjualan
        GROUP BY kode_cabang, kode_produk
        ORDER BY kode_cabang, jumlah_beli DESC)
    AS T1,(SELECT @nomorbaris := 0, @grouping := '') T
) AS T2 
WHERE T2.nomorbaris < 4

Hasil eksekusi dari query tersebut adalah sebagai berikut dan merupakan solusi dari masalah kita :

Hasil Eksekusi

kode_cabang kode_produk jumlah_beli nomorbaris
CABANG-039 PROD-0000018 38786 1
CABANG-039 PROD-0000029 38728 2
CABANG-039 PROD-0000020 38435 3
CABANG-047 PROD-0000025 38829 1
CABANG-047 PROD-0000018 38668 2
CABANG-047 PROD-0000012 38603 3
CABANG-065 PROD-0000021 39037 1
CABANG-065 PROD-0000023 38913 2
CABANG-065 PROD-0000036 38483 3


Penutup

Demikian tips SQL untuk mendapatkan top n dari grouping tertentu. Dengan semakin berkembangnya laporan analisis yang cukup rumit, kita semakin dituntut untuk mengetahui advanced features yang mengolah set, seperti penggunaan variable yang ditunjukkan pada artikel ini.

Untuk Anda yang ingin lebih serius menangani persoalan iteratif - yang biasanya berujung ke navigasi cell - mungkin tertarik untuk mempelajari dunia OLAP (Online Analytical Processing) lebih jauh. 

Bacaan Lebih Lanjut

Comments