LAG dan LEAD SQL Server: Bandingkan Data Antar Baris

Foto oleh RDNE Stock project via Pexels

Menganalisis Tren Data dengan LAG dan LEAD di SQL Server

Dalam analisis data, seringkali kita dihadapkan pada kebutuhan untuk membandingkan nilai suatu baris dengan baris sebelumnya atau baris berikutnya dalam sebuah dataset. Bayangkan skenario di mana Anda perlu menghitung perubahan harga saham dari hari ke hari, selisih penjualan bulanan, atau bahkan melihat status terakhir dari suatu proses berdasarkan urutan waktu. Melakukan perbandingan semacam ini dengan teknik JOIN atau subquery tradisional bisa menjadi rumit dan kurang efisien.

Di SQL Server, ada dua fungsi Window yang sangat powerful untuk memecahkan masalah ini: LAG dan LEAD. Kedua fungsi ini memungkinkan kita mengakses data dari baris yang mendahului atau mengikuti baris saat ini dalam sebuah set hasil query, tanpa perlu melakukan self-join yang kompleks.

Memahami Fungsi LAG: Mengintip Baris Sebelumnya

Fungsi LAG digunakan untuk mengakses nilai dari baris yang berada pada offset (jarak) tertentu sebelum baris saat ini dalam sebuah partisi. Ini sangat berguna ketika Anda ingin melihat data historis atau membandingkan suatu metrik dengan nilai sebelumnya.

Sintaksis Dasar LAG

Sintaksis LAG adalah sebagai berikut:

Sintaksis dasar fungsi LAG

LAG(expression, [offset, default_value]) OVER (PARTITION BY expression, ... ORDER BY expression [ASC | DESC], ...)

expression: Kolom atau ekspresi yang ingin Anda ambil nilainya.
offset (opsional): Berapa banyak baris ke belakang (sebelum baris saat ini) Anda ingin melihat. Defaultnya adalah 1 (satu baris sebelumnya).
default_value (opsional): Nilai yang akan dikembalikan jika tidak ada baris yang sesuai pada offset yang ditentukan (misalnya, pada baris pertama partisi). Jika tidak ditentukan, hasilnya adalah NULL.
OVER (PARTITION BY ... ORDER BY ...): Klausa Window yang menentukan kelompok baris (partisi) tempat fungsi akan beroperasi, dan urutan baris dalam setiap partisi. Klausa ORDER BY adalah WAJIB agar LAG dapat menentukan "baris sebelumnya" dengan benar.

Memahami Fungsi LEAD: Mengintip Baris Berikutnya

Sebaliknya, fungsi LEAD memungkinkan kita untuk mengakses nilai dari baris yang berada pada offset tertentu setelah baris saat ini dalam sebuah partisi. Ini berguna untuk memprediksi, melihat data mendatang, atau menyiapkan perhitungan yang melibatkan nilai berikutnya.

Sintaksis Dasar LEAD

Sintaksis LEAD adalah serupa:

Sintaksis dasar fungsi LEAD

LEAD(expression, [offset, default_value]) OVER (PARTITION BY expression, ... ORDER BY expression [ASC | DESC], ...)

expression: Kolom atau ekspresi yang ingin Anda ambil nilainya.
offset (opsional): Berapa banyak baris ke depan (setelah baris saat ini) Anda ingin melihat. Defaultnya adalah 1 (satu baris berikutnya).
default_value (opsional): Nilai yang akan dikembalikan jika tidak ada baris yang sesuai pada offset yang ditentukan (misalnya, pada baris terakhir partisi). Jika tidak ditentukan, hasilnya adalah NULL.
OVER (PARTITION BY ... ORDER BY ...): Sama seperti LAG, klausa ini menentukan partisi dan urutan baris. ORDER BY juga WAJIB di sini.

Contoh Implementasi Praktis: Analisis Penjualan Harian

Mari kita ilustrasikan penggunaan LAG dan LEAD dengan skenario analisis penjualan. Kita akan membuat tabel sederhana untuk mencatat penjualan harian.

Persiapan Data

Pertama, buat tabel dan masukkan beberapa data penjualan:

Membuat tabel dan memasukkan data penjualan harian

IF OBJECT_ID('PenjualanHarian', 'U') IS NOT NULL DROP TABLE PenjualanHarian;
CREATE TABLE PenjualanHarian (
    Tanggal DATE PRIMARY KEY,
    Penjualan INT
);

INSERT INTO PenjualanHarian (Tanggal, Penjualan) VALUES
('2023-01-01', 100),
('2023-01-02', 120),
('2023-01-03', 110),
('2023-01-04', 150),
('2023-01-05', 130);

Untuk melihat isi data penjualan kita, jalankan query berikut:

Melihat data penjualan harian yang sudah ada

SELECT Tanggal, Penjualan FROM PenjualanHarian ORDER BY Tanggal;

Hasil dari query di atas akan menampilkan data penjualan harian seperti ini:

Tanggal      Penjualan
----------- ----------
2023-01-01  100
2023-01-02  120
2023-01-03  110
2023-01-04  150
2023-01-05  130

Menggunakan LAG untuk Membandingkan Penjualan Hari Sebelumnya

Misalkan kita ingin melihat berapa penjualan pada hari sebelumnya untuk setiap tanggal, dan menghitung selisihnya. Ini adalah kasus klasik untuk LAG:

Menggunakan LAG untuk membandingkan penjualan dengan hari sebelumnya

SELECT
    Tanggal,
    Penjualan,
    LAG(Penjualan, 1) OVER (ORDER BY Tanggal) AS PenjualanHariSebelumnya,
    Penjualan - LAG(Penjualan, 1) OVER (ORDER BY Tanggal) AS SelisihPenjualan
FROM PenjualanHarian
ORDER BY Tanggal;

Dari hasil di atas, kita bisa melihat PenjualanHariSebelumnya dan SelisihPenjualan. Perhatikan bahwa baris pertama tanggal 2023-01-01 memiliki NULL untuk PenjualanHariSebelumnya, karena tidak ada data sebelum itu. Kita bisa menggantinya dengan 0 atau nilai lain menggunakan parameter default_value:

Tanggal      Penjualan PenjualanHariSebelumnya SelisihPenjualan
----------- ---------- ----------------------- ----------------
2023-01-01  100        NULL                    NULL
2023-01-02  120        100                     20
2023-01-03  110        120                     -10
2023-01-04  150        110                     40
2023-01-05  130        150                     -20

Sekarang, mari kita gunakan default_value:

Menggunakan LAG dengan default_value untuk menangani NULL

SELECT
    Tanggal,
    Penjualan,
    LAG(Penjualan, 1, 0) OVER (ORDER BY Tanggal) AS PenjualanHariSebelumnya,
    Penjualan - LAG(Penjualan, 1, 0) OVER (ORDER BY Tanggal) AS SelisihPenjualan
FROM PenjualanHarian
ORDER BY Tanggal;

Outputnya akan menunjukkan 0 untuk penjualan hari sebelumnya di baris pertama, membuat perhitungan selisih menjadi lebih bersih:

Tanggal      Penjualan PenjualanHariSebelumnya SelisihPenjualan
----------- ---------- ----------------------- ----------------
2023-01-01  100        0                       100
2023-01-02  120        100                     20
2023-01-03  110        120                     -10
2023-01-04  150        110                     40
2023-01-05  130        150                     -20

Menggunakan LEAD untuk Membandingkan Penjualan Hari Berikutnya

Jika kita ingin melihat berapa penjualan pada hari berikutnya dan menghitung selisihnya (perubahan yang akan datang), kita bisa menggunakan LEAD:

Menggunakan LEAD untuk membandingkan penjualan dengan hari berikutnya

SELECT
    Tanggal,
    Penjualan,
    LEAD(Penjualan, 1) OVER (ORDER BY Tanggal) AS PenjualanHariBerikutnya,
    Penjualan - LEAD(Penjualan, 1) OVER (ORDER BY Tanggal) AS SelisihPenjualan
FROM PenjualanHarian
ORDER BY Tanggal;

Output yang dihasilkan akan mirip dengan LAG, tetapi melihat ke depan. Baris terakhir akan memiliki NULL untuk PenjualanHariBerikutnya, karena tidak ada data setelah itu:

Tanggal      Penjualan PenjualanHariBerikutnya SelisihPenjualan
----------- ---------- ----------------------- ----------------
2023-01-01  100        120                     -20
2023-01-02  120        110                     10
2023-01-03  110        150                     -40
2023-01-04  150        130                     20
2023-01-05  130        NULL                    NULL

Membandingkan Data dalam Kelompok dengan PARTITION BY

Fungsi LAG dan LEAD menjadi lebih fleksibel ketika digabungkan dengan klausa PARTITION BY dalam OVER. Ini memungkinkan kita untuk menerapkan fungsi perbandingan antar baris secara independen untuk setiap grup data. Seperti Window Function lainnya yang sudah kita bahas, seperti ROW_NUMBER atau RANK, LAG dan LEAD juga beroperasi dalam sebuah 'window' yang ditentukan oleh PARTITION BY dan ORDER BY.

Misalnya, kita punya data penjualan dari beberapa produk berbeda. Kita ingin membandingkan penjualan produk A di suatu tanggal dengan penjualan produk A di tanggal sebelumnya, dan seterusnya untuk produk B, tanpa mencampuradukkan perbandingan antar produk.

Contoh Data Multi-Produk

Untuk mengilustrasikannya, mari kita buat ulang tabel dan tambahkan kolom NamaProduk serta beberapa data tambahan:

Membuat ulang tabel dengan kolom NamaProduk dan data multi-produk

IF OBJECT_ID('PenjualanMultiProduk', 'U') IS NOT NULL DROP TABLE PenjualanMultiProduk;
CREATE TABLE PenjualanMultiProduk (
    NamaProduk VARCHAR(50),
    Tanggal DATE,
    Penjualan INT,
    PRIMARY KEY (NamaProduk, Tanggal)
);

INSERT INTO PenjualanMultiProduk (NamaProduk, Tanggal, Penjualan) VALUES
('Laptop', '2023-01-01', 300),
('Laptop', '2023-01-02', 320),
('Laptop', '2023-01-03', 310),
('Keyboard', '2023-01-01', 50),
('Keyboard', '2023-01-02', 70),
('Keyboard', '2023-01-03', 60),
('Mouse', '2023-01-01', 20),
('Mouse', '2023-01-02', 25),
('Mouse', '2023-01-03', 30);

Berikut adalah data penjualan multi-produk kita:

Melihat data penjualan multi-produk

SELECT NamaProduk, Tanggal, Penjualan FROM PenjualanMultiProduk ORDER BY NamaProduk, Tanggal;

Data yang akan tampil adalah sebagai berikut:

NamaProduk  Tanggal      Penjualan
----------- ---------- ----------
Keyboard    2023-01-01  50
Keyboard    2023-01-02  70
Keyboard    2023-01-03  60
Laptop      2023-01-01  300
Laptop      2023-01-02  320
Laptop      2023-01-03  310
Mouse       2023-01-01  20
Mouse       2023-01-02  25
Mouse       2023-01-03  30

Menggunakan LAG dengan PARTITION BY

Untuk menghitung penjualan hari sebelumnya per produk, kita akan menggunakan PARTITION BY NamaProduk:

Menggunakan LAG dengan PARTITION BY untuk perbandingan per produk

SELECT
    NamaProduk,
    Tanggal,
    Penjualan,
    LAG(Penjualan, 1, 0) OVER (PARTITION BY NamaProduk ORDER BY Tanggal) AS PenjualanProdukSebelumnya,
    Penjualan - LAG(Penjualan, 1, 0) OVER (PARTITION BY NamaProduk ORDER BY Tanggal) AS Selisih
FROM PenjualanMultiProduk
ORDER BY NamaProduk, Tanggal;

Output dari query di atas akan menunjukkan bagaimana perhitungan PenjualanProdukSebelumnya direset untuk setiap NamaProduk baru. Misalnya, pada baris pertama "Laptop", PenjualanProdukSebelumnya adalah 0, meskipun ada penjualan "Keyboard" sebelumnya. Ini menunjukkan bahwa LAG bekerja secara independen dalam setiap partisi yang ditentukan.

NamaProduk  Tanggal      Penjualan PenjualanProdukSebelumnya Selisih
----------- ---------- ---------- ------------------------- --------
Keyboard    2023-01-01  50         0                         50
Keyboard    2023-01-02  70         50                        20
Keyboard    2023-01-03  60         70                        -10
Laptop      2023-01-01  300        0                         300
Laptop      2023-01-02  320        300                       20
Laptop      2023-01-03  310        320                       -10
Mouse       2023-01-01  20         0                         20
Mouse       2023-01-02  25         20                        5
Mouse       2023-01-03  30         25                        5

Trade-off dan Kapan Sebaiknya Menggunakan LAG/LEAD

LAG dan LEAD adalah alat yang sangat efisien untuk tugas-tugas perbandingan antar baris, terutama untuk data deret waktu atau urutan yang jelas.

Keunggulan:
- Sintaksis Ringkas: Jauh lebih mudah dibaca dan ditulis daripada self-join atau subquery yang kompleks untuk perbandingan 1-ke-1 (sebelum/sesudah).
- Performa: Umumnya lebih efisien karena SQL Server dapat mengoptimalkan pemrosesan Window Function dalam satu pass data, dibandingkan dengan beberapa pass yang mungkin dibutuhkan oleh JOIN atau subquery.
- Fleksibilitas Offset: Mudah disesuaikan untuk melihat baris ke-N sebelumnya atau berikutnya.
Keterbatasan/Pertimbangan:
- Memerlukan ORDER BY: Tanpa ORDER BY, konsep "baris sebelumnya" atau "baris berikutnya" tidak dapat ditentukan, sehingga fungsi tidak akan berjalan.
- Memori dan CPU: Untuk dataset yang sangat besar dan partisi yang besar, Window Function dapat memerlukan memori dan CPU yang signifikan untuk menyusun data dalam window dan melakukan kalkulasi. Ini perlu diperhatikan dalam skenario performa tinggi.
- Bukan Pengganti JOIN Universal: LAG dan LEAD ideal untuk perbandingan antar baris dalam set data yang sudah terurut. Untuk perbandingan data yang lebih kompleks atau penggabungan data dari tabel berbeda yang tidak memiliki hubungan urutan yang jelas, JOIN tetap menjadi pilihan utama.

Kesalahan Umum dan Cara Menghindarinya

Meskipun powerful, ada beberapa jebakan yang sering ditemui developer saat menggunakan LAG dan LEAD:

Lupa Klausa ORDER BY: Ini adalah kesalahan paling umum. Tanpa ORDER BY dalam klausa OVER, SQL Server tidak tahu bagaimana mendefinisikan "sebelum" atau "sesudah". Query akan menghasilkan error.
Salah Menentukan PARTITION BY: Jika Anda membandingkan data yang seharusnya berdiri sendiri (misalnya, penjualan per produk), tetapi Anda tidak menggunakan PARTITION BY, maka LAG/LEAD akan membandingkan baris di seluruh dataset, menghasilkan perbandingan yang salah. Pastikan partisi Anda merefleksikan kelompok logis data.
Mengabaikan default_value untuk NULL: Ketika LAG atau LEAD tidak dapat menemukan baris pada offset yang ditentukan (misalnya, baris pertama/terakhir dalam partisi), ia akan mengembalikan NULL. Jika perhitungan Anda tidak siap menangani NULL, hasilnya bisa menjadi tidak akurat. Selalu pertimbangkan untuk menggunakan default_value atau fungsi COALESCE/ISNULL.
Membandingkan Tipe Data yang Tidak Kompatibel: Pastikan ekspresi yang Anda bandingkan memiliki tipe data yang kompatibel untuk operasi matematika jika Anda ingin menghitung selisih atau rasio.

Ringkasan

Fungsi LAG dan LEAD di SQL Server adalah alat yang esensial bagi setiap developer atau analis data yang bekerja dengan data berurutan. Mereka menyederhanakan tugas membandingkan data antar baris yang sebelumnya memerlukan query kompleks dengan JOIN atau subquery. Dengan memahami sintaksis, parameter (terutama PARTITION BY dan ORDER BY), serta kapan dan bagaimana menggunakannya, Anda dapat melakukan analisis tren dan deret waktu yang jauh lebih efisien dan akurat. Selalu ingat untuk mendefinisikan urutan dengan ORDER BY dan mengelompokkan data dengan PARTITION BY sesuai kebutuhan analisis Anda untuk hasil yang optimal.

Seri Belajar SQL Server:

← Sebelumnya: Window Function ROW_NUMBER RANK dan DENSE_RANK

→ Selanjutnya: PIVOT dan UNPIVOT di SQL Server

LAG dan LEAD SQL Server: Bandingkan Data Antar Baris