Memahami Fungsi LAG() di SQL: Panduan Komprehensif

Jawaban Singkat: Apa itu Fungsi LAG()?

Fungsi LAG() adalah salah satu window function di SQL yang memungkinkan Anda membuat kolom baru yang mengakses baris sebelumnya dari kolom lain. Namanya diambil dari fakta bahwa setiap baris di kolom baru yang Anda buat akan melakukan "lag" untuk mengambil nilai dari baris yang mendahului pada kolom lain yang Anda tentukan.

Mari kita lihat sintaks dasar dalam praktik. Misalkan kita memiliki tabel dua kolom sederhana berisi harga saham harian seperti berikut:

Contoh data harga saham. Gambar oleh Penulis.

Kita dapat menggunakan kueri berikut untuk membuat kolom baru yang mengambil harga hari sebelumnya di setiap baris dengan kueri berikut:

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before
FROM stock_price;

Dan kita akan mendapatkan hasil berikut:

Contoh cepat penggunaan fungsi LAG(). Gambar oleh Penulis.

Perhatikan bahwa kita memperkenalkan satu nilai [null] karena tidak ada nilai hari sebelumnya untuk baris pertama.

Sintaks Dasar Fungsi LAG()

Fungsi LAG() ditulis sebagai bagian dari klausa SELECT. Dalam sintaks paling dasar, fungsi ini dapat ditulis sebagai berikut:

LAG(column1) OVER(ORDER BY column2)

Berikut fungsi LAG() yang sama diterapkan dalam kueri mandiri:

SELECT 
   column1, 
   column2, 
   LAG(column1) OVER (ORDER BY column2) AS previous_value 
FROM 
   table_name;

Seperti yang Anda lihat, sintaks dasar terdiri dari beberapa bagian. Mari kita uraikan bersama:

• column1: Ini adalah kolom dari mana nilai baris sebelumnya akan diambil.
• OVER(): OVER() adalah kata kunci wajib untuk setiap window function. Klausa ini mendefinisikan bingkai tempat window function dijalankan. Pada contoh di atas, window function akan berjalan di atas column2 yang diurutkan.
• ORDER BY: ORDER BY tidak wajib, tetapi sangat direkomendasikan saat digunakan dengan fungsi LAG(); biasanya fungsi ini tidak masuk akal tanpanya. 
• column2: Kolom ini menentukan urutan yang akan diikuti fungsi LAG(). Lebih dari satu kolom dapat digunakan sebagai dasar pengurutan.

Mengapa Menggunakan Fungsi LAG()

Anda mungkin bertanya-tanya apa hebatnya fungsi LAG(). Jawabannya adalah kolom lagging yang baru dapat digunakan untuk membandingkan nilai dari dua baris yang berbeda.

Inilah alasan fungsi LAG() umum digunakan pada data runtun waktu. Misalnya, dalam dataset demo kita, kita dapat dengan mudah menghitung perubahan harga saham harian dengan kueri berikut:

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before,
	price - LAG(price) OVER(ORDER BY date) AS daily_change
FROM stock_price; 

Menghitung perubahan harian dengan LAG(). Gambar oleh Penulis.

Kita juga bisa naik tingkat ke perhitungan yang lebih canggih dan mempertimbangkan perubahan persentase harian.

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before,
	price - LAG(price) OVER(ORDER BY date) AS daily_change,
	((price - LAG(price) OVER(ORDER BY date))*100 / 
		(LAG(price) OVER(ORDER BY date))) AS daily_perc_change
FROM stock_price; 

Menghitung perubahan persentase harian dengan LAG(). Gambar oleh Penulis.

Penggunaan Lanjutan Fungsi LAG()

Sekarang, setelah kita memahami penggunaan dasar fungsi LAG(), mari tingkatkan langkah demi langkah dan lihat apa lagi yang bisa kita lakukan dengannya.

Di sini kita akan beralih ke dataset demo lain yang mencatat pendapatan bulanan untuk tiga perusahaan imajiner: Welsh LLC, Jones Group, dan Green-Keebler, dari awal 2022 hingga pertengahan 2024. Berikut struktur datanya:

Dataset pendapatan demo. Gambar oleh Penulis.

Mengurutkan berdasarkan beberapa kolom

Dalam dataset baru kita, kolom lagging harus diurutkan berdasarkan dua kolom: year dan month. Seperti disebutkan sebelumnya, ini dapat dilakukan dengan memberikan kedua kolom tersebut ke klausa ORDER BY.

Dalam kueri berikut, kita membuat kolom lagging dan kolom selisih pendapatan month-on-month (MoM), diurutkan berdasarkan year dan month. Kita juga memfilter kueri dengan klausa WHERE untuk fokus pada satu perusahaan terlebih dahulu.

SELECT *,
	LAG(revenue) OVER(ORDER BY year, month) AS one_month_before,
	revenue - LAG(revenue) OVER(ORDER BY year, month) AS mom_difference
FROM revenues
WHERE company = 'Welch LLC'; 

Mengurutkan berdasarkan tahun dan bulan untuk LAG(). Gambar oleh Penulis.

Mempartisi bingkai LAG()

Misalkan kita ingin menghitung dua kolom yang sama untuk ketiga perusahaan dalam dataset kita. Jika kita menghitungnya dengan cara yang sama seperti penggunaan fungsi LAG() sejauh ini, kolom lagging akan berjalan melintasi ketiga perusahaan, dan kolom perbedaan akan bercampur antara pendapatan semuanya, dan itu bukan yang kita inginkan.

Yang kita inginkan adalah mendapatkan pendapatan bulan sebelumnya, dan menghitung perbedaan MoM untuk setiap perusahaan secara terpisah, lalu memulai ulang untuk perusahaan berikutnya.

Untuk melakukannya, kita memperkenalkan klausa baru dalam sintaks fungsi LAG(). Klausa tersebut adalah PARTITION BY, dan dapat ditambahkan ke sintaks dasar kita sebagai berikut:

LAG(column1) OVER(PARTITION BY column3 ORDER BY column2)

Kolom yang perlu kita partisi dalam contoh ini adalah company. Jadi, kita akan memodifikasi kueri sebelumnya dengan menambahkan klausa PARTITION BY dan menghapus pernyataan WHERE.

SELECT *,
	LAG(revenue) OVER(PARTITION BY company ORDER BY year, month) AS one_month_before,
	revenue - LAG(revenue) OVER(PARTITION BY company ORDER BY year, month) AS mom_difference
FROM revenues;

Pada hasilnya, kita akan melihat bahwa kolom lagging dan MoM kini berjalan di atas pendapatan bulanan perusahaan pertama saja, lalu memulai kembali untuk yang berikutnya. Kita dapat melihat ini pada tangkapan layar di bawah, yang menampilkan bulan-bulan terakhir Green-Keebler dan bulan-bulan pertama Jones Group.

Menggunakan PARTITION BY dengan LAG(). Gambar oleh Penulis.

Menyesuaikan offset

Bagaimana jika kita tidak perlu mengambil nilai dari baris sebelumnya, tetapi dari enam atau dua belas baris di atas? Dengan kata lain, bagaimana jika kita perlu menghitung perbedaan year-on-year (YoY) alih-alih MoM?

Dalam kasus ini, kita menambahkan parameter baru ke sintaks fungsi LAG(). Parameter ini disebut offset, dan menentukan berapa banyak baris di atas baris saat ini dari mana fungsi LAG() mengambil nilai. Posisinya dalam sintaks ditunjukkan di bawah:

LAG(column1, offset) OVER(PARTITION BY column3 ORDER BY column2)

Secara default, dan sesuai penggunaan kita sejauh ini, nilai offset sama dengan satu. Namun, dengan menyebutkan offset secara eksplisit dalam ekspresi LAG(), kita dapat mengubah parameter default ini. 

Kembali ke contoh kita, untuk mendapatkan perubahan pendapatan YoY, kita perlu mengambil pendapatan untuk bulan yang sama pada tahun sebelumnya. Kita dapat melakukannya dengan kueri berikut, di mana kita menetapkan 12 sebagai offset: 

SELECT *,
	LAG(revenue, 12) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues;

Dan hasilnya akan seperti ini:

Perbedaan year-on-year dengan LAG(). Gambar oleh Penulis.

Menangani NULL

Anda mungkin memperhatikan bahwa fungsi LAG() mengembalikan NULL pada baris-baris saat periode sebelumnya tidak tersedia, seperti pada baris tahun 2022 pada kueri sebelumnya.

Ini adalah perilaku default fungsi LAG(), tetapi dapat diubah dengan menyebutkan secara eksplisit parameter baru bernama "default". Parameter ini dapat menerima nilai numerik integer atau float apa pun. Dalam sintaks fungsi, parameter ini diposisikan sebagai berikut:

LAG(column1, offset, default) OVER(PARTITION BY column3 ORDER BY column2)

Kasus penggunaan umum parameter "default" adalah ketika nilai pada data runtun waktu sebenarnya dimulai dari nol. 

Dalam contoh kita, kita bisa mengasumsikan bahwa ketiga perusahaan didirikan pada Januari 2022 (tanggal paling awal di dataset), sehingga kita dapat menganggap pendapatan sebelum pendirian sebagai nol. Dengan melakukan ini, kita akan menghitung perubahan pendapatan dengan lebih akurat, karena pendapatan apa pun yang diperoleh pada bulan-bulan pertama akan menjadi perubahan positif.

Dalam kueri kita, kita akan menetapkan nol sebagai parameter "default" pada kedua ekspresi LAG() sebagai berikut:

SELECT *,
	LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues;

Dan hasilnya akan menghasilkan nol pada kolom lagging, serta pendapatan bersih dari nol pada kolom perubahan pendapatan YoY:

Mengganti NULL dengan nol di LAG(). Gambar oleh Penulis.

Perhatikan bahwa agar dapat menyebutkan nilai untuk parameter "default" secara eksplisit, menjadi wajib untuk menyebutkan nilai offset secara eksplisit juga, karena angka pertama yang diberikan setelah nama kolom di dalam fungsi LAG() akan dianggap sebagai offset.

Jika Anda perlu mengubah "default" tetapi tidak mengubah offset, tetapkan parameter offset sebagai satu, dan fungsi akan berperilaku seperti biasanya.

Mengurutkan Setelah Fungsi LAG()

Perlu diketahui bahwa urutan yang digunakan fungsi LAG() tidak harus sama dengan urutan tampilan hasil. Anda selalu dapat mengubah urutan tersebut dengan menggunakan klausa ORDER BY secara normal di kueri Anda.

Dalam contoh kita, kita dapat menyusun ulang hasil untuk menampilkan bulan yang sama pada tahun yang sama untuk ketiga perusahaan sebelum beralih ke bulan berikutnya dalam tahun tersebut, dengan mengurutkan kueri berdasarkan tahun dan bulan pada klausa ORDER BY luar:

SELECT *,
	LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues
ORDER BY year, month;

Dan kita akan mendapatkan yang kita butuhkan:

Mengurutkan kueri setelah LAG(). Gambar oleh Penulis.

Kesalahan Umum dan Praktik Terbaik

Mari kita lihat masalah umum, jika Anda memerlukan bantuan pemecahan masalah. 

Pengurutan yang salah

• Jebakan: Tidak menyebutkan klausa ORDER BY dalam pernyataan LAG() dapat menyebabkan hasil yang tidak tepat. Meskipun urutan asli tabel sumber tampak sesuai untuk fungsi tersebut, jangan pernah bergantung pada urutan asli karena dapat berubah seiring waktu.
• Praktik Terbaik: Selalu gunakan klausa ORDER BY dalam pernyataan LAG(), dan pastikan Anda mengurutkan berdasarkan kolom yang benar.

Partisi yang salah

• Jebakan: Bingkai LAG() yang keliru karena mengabaikan penggunaan klausa PARTITION BY atau menggunakannya pada kolom yang salah. 
• Praktik Terbaik: Periksa kembali partisi tempat fungsi LAG() Anda dijalankan.

Offset yang salah

• Jebakan: Nilai lagging yang tidak tepat karena offset yang salah.
• Praktik Terbaik: Periksa kembali nilai offset yang Anda perlukan, dan ingat bahwa nilai offset default mungkin bukan yang Anda butuhkan dalam beberapa kasus.

NULL yang tidak tepat

• Jebakan: Membiarkan nilai NULL pada keluaran fungsi LAG() saat nilai lain lebih tepat, karena tidak menyatakan parameter "default".
• Praktik Terbaik: Selalu pertimbangkan apa arti nilai sebelum awal rentang waktu dataset Anda. Dalam beberapa kasus, lebih tepat menggunakan nol daripada null, seperti yang kita lihat dalam contoh.

Menyatakan default tanpa menyatakan offset

• Jebakan: Menyatakan parameter "default" tanpa menyatakan offset berarti nilai "default" akan menjadi nilai offset.
• Praktik Terbaik: Jika Anda menyebutkan parameter "default" secara eksplisit, jangan lupa untuk menyatakan offset juga.

Menggunakan alias alih-alih pernyataan fungsi

• Jebakan: Jika Anda menggunakan pernyataan LAG() yang sama pada lebih dari satu kolom, Anda tetap harus menulis pernyataan LAG() lengkap pada kolom kedua, bukan aliasnya. Menggunakan alias dari kolom LAG() pertama akan menimbulkan error.
• Praktik Terbaik: Selalu tulis pernyataan LAG() secara lengkap dalam pernyataan SELECT.

Mengabaikan indeks

• Jebakan: Fungsi LAG(), seperti semua window function, dapat mahal secara komputasi pada dataset besar. Karena itu, mengabaikan pengindeksan kolom yang digunakan dalam klausa PARTITION BY dan ORDER BY dapat menyebabkan kinerja buruk.
• Praktik Terbaik: Pastikan kolom yang digunakan dalam klausa PARTITION BY dan ORDER BY diindeks, jika memungkinkan, untuk meningkatkan performa kueri.

Mengabaikan komentar

• Jebakan: Tanpa komentar dan dokumentasi, LAG() dan window function lainnya dapat menjadi berantakan dan tidak mudah dibaca atau dipahami, terutama saat lebih dari satu fungsi digunakan.
• Praktik Terbaik: Kapan pun Anda menggunakan LAG() dan window function lainnya, pastikan untuk menambahkan komentar dan mendokumentasikan apa yang ingin dicapai kueri. Ini membantu orang lain dan diri Anda sendiri memahami tujuan dan logika penggunaan LAG() saat kueri ditinjau kembali.

Kesimpulan dan Sumber Tambahan

Dalam tutorial ini, kita telah melihat apa itu fungsi LAG() dan bagaimana fungsi ini dapat menjadi alat yang kuat untuk melakukan analitik runtun waktu. Selain itu, kita menelusuri argumennya dan klausa yang terkait. Lain kali Anda bekerja dengan data yang terkait waktu, atau data berurutan apa pun, di SQL, pertimbangkan penggunaan fungsi LAG() dan apa yang memungkinkannya untuk Anda lakukan. Dalam konteks lain, fungsi LAG() berguna untuk menemukan autokorelasi, menghaluskan data, atau memeriksa interval tidak beraturan sebagai bagian dari pembersihan data. 

Jika Anda tertarik dengan apa yang dapat dilakukan satu window function, Anda dapat mempelajari seluruh keluarganya dan meningkatkan keterampilan analisis di SQL dengan kursus interaktif PostgreSQL Summary Stats and Window Functions kami yang komprehensif. Dan jika Anda menyukai artikel ini, Anda mungkin juga akan menikmati mengikuti Associate Data Analyst in SQL Career Track dan memperoleh Sertifikasi SQL Associate di akhir!