Kombinasi AVG dan Pivot

Merangkum data menjadi sebuah laporan akan selalu menjadi tantangan bagi pembuatnya. Baik dari sisi konseptor atau programmernya. Data yang ada tidak selalu berada dalam suatu tempat apalagi datanya besar atau juga datanya disimpan dalam bentuk yang berbeda dari yang ditampilkan ke pengguna data tersebut.

Tujuan

Dalam kasus ini kita akan mengambil data dari sebuah table yang merupakan gabungan dari dua hal yang berbeda. Resultset yang dihasilkan akan menampilkan nilai rata-rata(Average) dan datanya juga dibentuk menjadi memanjang(pivot).

Data

Data yang disediakan adalah seperti pada gambar dibawah. Memang contoh datanya agak banyak karena memang diperlukan untuk itu.

Hasil

Dengan data diatas, kita diharapkan bisa merangkai data seperti dibawah. Data yang berbentuk pivot dengan adanya perhitungan average.

Hasil dari ini bisa juga dijadikan CSV. Dari sana dengan mudah kita jadikan data dalam Excel, seperti contoh dibawah.

Solution

Kalau ini dilakukan pada Excel mudah sekali. Dibantu dengan fungsi-fungsi bawaanya dan datanya sendiri sudah ada di-sheet-nya. Sedangkan datanya ini tersimpan disebuah database server. Tentunya cara menyimpan datanya berbeda, karena umumnya data disimpan dalam bentuk row.

Untuk mewujudkan ini, kita perlu melihat bahwa ini adalah dua cara pengambilan data yang berbeda. Fungsi AVG yang ada tidak bisa digunakan untuk meng-average data column-nya; ia bekerja dengan row.

Oleh karena itu sekarang kita coba melihat mengambil data tersebut lalu menyatukannya.

Pivot

Yang pertama kita perlu lakukan adalah membuat data tersebut menjadi pivot. Caranya sederhana, cukup kita geser lewat IF dan disatukan dengan MAX.

AVG

Laku kita perlu data yang di-average-kan. Dengan memanfaatkan fungsi AVG saja kita bisa melakukannya. Setelah itu kita pivot-kan seperti cara diatas.

Kombinasi

Dari kedua query diatas kita sudah dapat membayangkan data yang kita butuhkan. Sekarang kita gabungkan dengan join. Tergantung dari setup MySQL-nya, kita perlu sedikit melakukan konversi untuk nilai dari sesuai dengan spesifikasi penampilan nilai numeric(satu digit dibelakang koma). Itu dilakukan dengan fungsi ROUND saja.

Penutup

Menyediakan data sesuai kebutuhan, kadang kita perlu memecah lalu mengabungkannya kembali. Itu jauh lebih mudah dibandingkan dengan cara lainnya. Yang penguna ingin lihat data yang sudah terangkum dan data yang tersimpan tidak selalu semudah yang pengguna bayangkan. Mengambil, mentransformasi dan merangkum adalah hal 'ajaib' yang harus dilakukan oleh programmernya.

Referensi

• MySQL

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.