LAG dan LEAD Function di T-SQL
Soeleman 1 Maret 2017
Dalam beberapa report yang bersifat analitik, biasanya data yang diperlukan memerlukan penanganan khusus. Dari relasi data menjadi flat data. Salah satu-nya, report data yang datanya berhubungan dari row sebelum atau sesudahnya dari table yang sama (Self-Join). Pada umumnya SQL tidak bekerja berdasarkan per-row tapi perhimpunan. Untuk itu pembuat database produk berusaha menjawab tuntutan itu dengan menambahkan fungsi tertentu.
Table
Sebelum memulai, kita perlu mempersiapkan lingkungan untuk tes ini. Kita memerlukan sebuah table EntryLog dengan data agar lebih mudah menjelaskannya.
LAG Function
Bagian keuangan yang kebanyakkan berkutat dengan aplikasi speardsheet seperti Excel. Kebanyakkan berfikir data itu seperti tabular dengan basis row-by-row karena terbiasa dengan itu. Tapi ini menjadi dilema kadang berhadapan dengan data yang diambil dari database, karena cara sql mengambil data berdasarkan resultset.
Seperti menghitung urutan data yang per-row-nya memerlukan data dari row sebelumnya. Dalam aplikasi Excel ini biasa dilakukan dan mudah karena datanya memang sudah ada di-sheet-nya. Terlihat pada gambar dibawah, bagaimana data dari row sebelumnya bisa dijadikan column di-row sekarang.
Mulai dari SQL Server 2012 sudah disediakan fungsi LAG untuk memudahkan kita melakukan ini. Dan sintaknya juga sederhana.
LEAD Function
Ada fungsi LAG yang mengambil data dari row sebelumnya, pastinya ada kebutuhan juga untuk mengambil data sesudah-nya. Seperti pada resultset dibawah.
SQL Server juga menyediakan fungsi LEAD untuk mendapatkan resultset diatas.
LAG + LEAD Function
Dalam beberapa kesempatan kadang juga diperlukan mengkombinasikan dua fungsi tersebut. Resultset dibawah terlihat kombinasi dari row sebelum dan sesudahnya dalam satu row.
Mengkombinasikan kedua fungsi itu sederhana sekali di SQL Server ini. Seperti pada query dibawah.
SQL
Untuk pengguna SQL Server dibawah SQL Server 2012 mau tidak mau harus melakukannya dengan berbagai cara, termasuk dengan plain-sql. Kalau dipikir, mendapatkan data seperti itu terlihat mudah sampai kita coba membuatnya.
LAG
Yang terpikir pertama adalah membuat sebagai sub-query di SELECT. Mungkin saja dilakukan apabila kita hanya mengelolah satu group data saja. Untuk mengemulasi fungsi LAG tidak bisa seperti itu.
Salah satu solusinya dengan membuat dua resultset yang memiliki nomor urut. Nomor urut bisa di bentuk dengan ROW_NUMBER. Menggunakan nomor urut itu kita satukan kedua resultset itu sambil di geser sesuai keperluan. Dalam LAG kita geser kedua resultset dengan nomor urut yang beda minus 1.
LEAD
Untuk LEAD tidak beda jauh, hanya dibedakan nomor urutnya dengan penambahan 1.
LAG + LEAD
Karena LAG dan LEAD caranya sama, maka untuk mengkombinasikan ini maka kita gunakan tiga resultset.
Penutup
Fungsi LAG dan LEAD adalah rutin yang dibakukan dalam 'module' pada internal engine query. Sehingga ada keuntungan dalam memprocess data ketika engine query melakukan pekerjaannya. Lagi pula akan membuat sintak query-nya lebih sederhana.
Tapi bukan berarti itu tidak bisa dilakukan dengan cara sql biasa. Prinsipnya sama, dengan mengeser resultset sesuai kebutuhan query-nya.
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.