Kombinasi Average Total dan Per-Week di Microsoft SQL Server
Soeleman 15 April 2018
Mengabungkan 2 resultset yang berbeda jumlah row-nya menjadi satu row, kadang muncul dari permintaan pengguna.
Ditambah lagi, mendapatkan AVERAGE(rata-rata) per-minggu dalam satu tahun atau range date.
Dalam tulisan ini, kita akan mencoba menyelesaikan tuntutan diatas.
Tujuan
Kasusnya seperti ini, kita menginginkan resultset yang merupakan kombinasi dari dua AVG. Yang pertama adalah AVG per-minggu dan yang satu adalah AVG dari semua row yang ada.
Yang menarik dari kasus ini, resultset total pasti hanya satu row karena memang semua row. Tapi yang per-minggu, pasti bervariasi; tergantung jumlah data yang di-AVG. Dan kita juga perlu mengetahui tanggal per-row-nya itu masuk di-minggu ke berapa.
Table
Dalam tulisan ini kita akan menggunakan table LogDriver dengan data seperti dibawah.
Resultset
Salah satu solusi yang ditawarkan adalah membuat resultset AVG per-minggu-nya menjadi satu row. Karena memang hasil akhir dari resultset-nya hanya di-ingin-kan menjadi satu row seperti pada gambar dibawah.
Solusi dengan T-SQL
Untuk membuat solusi atas kasus ini. Kita akan membagi menjadi beberapa bagian solusi.
Week of Date
Sebelumnya kita perlu membuat datanya dengan tambahan informasi. Dalam konteks ini, kita perlu tahu tanggal yang ada itu berada di-minggu keberapa.
Oleh karena itu kita harus bisa membuat resultset seperti dibawah.
Kebetulan dalam MsSQL sudah ada fungsi yang kita butuhkan. Kita bisa menggunakan DATEPART untuk mendapatkan hasil yang kita inginkan. Tentu ada beberapa opsi yang bisa kita gunakan. Dalam kesempatan ini kita gunakan opsi WEEK.
Average per-Driver
Data yang ada juga memiliki beberapa driver yang akan di-AVERAGE-kan. Oleh karena itu kita akan mengelompokkannya per-driver juga.
Membuat AVERAGE mudah, cukup kita gunakan fungis AVG. Untuk mendapatkan hasil seperti diatas, kita bisa menggunakan query seperti dibawah.
Disini memang kita menggunakan DATEPART, walaupun tidak perlu. Ia ada disana sebagai bagian dari seluruh solusi.
Average per-Week
Dengan adanya data week-no, pekerjaan kita lebih mudah untuk mendapat hasil ini.
Disini kita tetap menggunakan fungsi AVG dengan pengelompokkan Week-no dan Driver.
Hasil yang didapat akan berupa beberapa row sesuai dengan jumlah Week-no-nya.
Sedangkan kita perlu membuat row tersebut dibuat jadi satu column sesuai dengan Driver-nya. Untuk itu kita perlu menggunakan SUFF, FOR XML dan CONCAT, seperti pada tulisan Replika Fungsi GROUP_CONCAT dengan T-SQL.
Prinsipnya resultset yang ada dijadikan satu row dengan membuatnya jadi satu column NVARCHAR.
Kombinasi
Karena kita sudah mendapatkan resultset yang diperlukan. Maka dengan mudah kita kombinasikan kedua resultset menjadi satu row seperti tujuan akhir.
Hasi kombinasi tersebut akan seperti query dibawah.
Penutup
Mengkombinasikan dua resultset yang berbeda jumlah row-nya dapat dilakukan dengan cara meratakan row-nya menjadi satu row. Barulah kita bisa mengkombinasikan keduanya.
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.