Pertanyaan paling natural atas deret data bulanan bukan “berapa totalnya” melainkan “naik atau turun dari bulan lalu?” Menjawabnya butuh dua nilai sekaligus: baris sekarang dan baris sebelumnya. GROUP BY tidak bisa, ROW_NUMBER tidak bisa — keduanya tidak pernah membaca nilai baris lain. Fungsi offset hadir tepat untuk itu: LAG mengambil nilai dari baris sebelum baris saat ini, LEAD dari baris sesudahnya. Chapter ini memakai keduanya untuk membangun analisis pertumbuhan penjualan bulanan yang sesungguhnya, sekaligus membongkar dua jebakan klasiknya: NULL di tepi partisi dan bulan yang bolong di tengah deret.

Tabel Latihan #

Domainnya masih penjualan_bulanan — 16 baris penjualan bulanan per produk Toko Maju Jaya sepanjang semester I 2026. Dua kekhasan datanya kini berperan sebagai bahan uji: Gula Pasir tidak punya baris Januari dan Teh Melati tidak punya baris Mei, sehingga kita bisa melihat apa yang terjadi bila deret bulanan tidak rapat:

sql buat-tabel.sql 
-- tabel latihan: penjualan bulanan per produk Toko Maju Jaya, semester I 2026
CREATE TABLE penjualan_bulanan (
    id      integer PRIMARY KEY,
    produk  text NOT NULL,
    bulan   date NOT NULL,
    kanal   text NOT NULL,
    jumlah  integer NOT NULL
);

INSERT INTO penjualan_bulanan (id, produk, bulan, kanal, jumlah) VALUES
    ( 1, 'Kopi Gayo',  DATE '2026-01-01', 'toko',   120),
    ( 2, 'Teh Melati', DATE '2026-01-01', 'toko',    80),
    ( 3, 'Kopi Gayo',  DATE '2026-02-01', 'online', 135),
    ( 4, 'Teh Melati', DATE '2026-02-01', 'toko',    95),
    ( 5, 'Gula Pasir', DATE '2026-02-01', 'toko',    60),
    ( 6, 'Gula Pasir', DATE '2026-03-01', 'toko',    70),
    ( 7, 'Kopi Gayo',  DATE '2026-03-01', 'toko',   128),
    ( 8, 'Teh Melati', DATE '2026-03-01', 'toko',    80),
    ( 9, 'Gula Pasir', DATE '2026-04-01', 'toko',    70),
    (10, 'Kopi Gayo',  DATE '2026-04-01', 'online', 150),
    (11, 'Teh Melati', DATE '2026-04-01', 'toko',   110),
    (12, 'Kopi Gayo',  DATE '2026-05-01', 'online', 150),
    (13, 'Gula Pasir', DATE '2026-05-01', 'toko',    55),
    (14, 'Kopi Gayo',  DATE '2026-06-01', 'online', 160),
    (15, 'Teh Melati', DATE '2026-06-01', 'online', 130),
    (16, 'Gula Pasir', DATE '2026-06-01', 'toko',    75);

SELECT id, produk, bulan, kanal, jumlah
FROM penjualan_bulanan
ORDER BY bulan, produk;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 16
 id |   produk   |   bulan    | kanal  | jumlah 
----+------------+------------+--------+--------
  1 | Kopi Gayo  | 2026-01-01 | toko   |    120
  2 | Teh Melati | 2026-01-01 | toko   |     80
  5 | Gula Pasir | 2026-02-01 | toko   |     60
  3 | Kopi Gayo  | 2026-02-01 | online |    135
  4 | Teh Melati | 2026-02-01 | toko   |     95
  6 | Gula Pasir | 2026-03-01 | toko   |     70
  7 | Kopi Gayo  | 2026-03-01 | toko   |    128
  8 | Teh Melati | 2026-03-01 | toko   |     80
  9 | Gula Pasir | 2026-04-01 | toko   |     70
 10 | Kopi Gayo  | 2026-04-01 | online |    150
 11 | Teh Melati | 2026-04-01 | toko   |    110
 13 | Gula Pasir | 2026-05-01 | toko   |     55
 12 | Kopi Gayo  | 2026-05-01 | online |    150
 16 | Gula Pasir | 2026-06-01 | toko   |     75
 14 | Kopi Gayo  | 2026-06-01 | online |    160
 15 | Teh Melati | 2026-06-01 | online |    130
(16 rows)

Dasar LAG dan LEAD: Membaca Tetangga Baris #

LAG(kolom) membaca nilai kolom dari baris tepat sebelum baris saat ini; LEAD(kolom) membaca dari baris tepat sesudahnya. Keduanya membutuhkan ORDER BY di dalam OVER — tanpa urutan, konsep “baris sebelumnya” tidak bermakna. Dengan PARTITION BY produk, setiap produk menjadi deretnya sendiri:

sql dasar-lag-lead.sql 
-- LAG membaca baris sebelumnya, LEAD membaca baris berikutnya dalam partisi
SELECT produk,
       bulan,
       jumlah,
       lag(jumlah)  OVER (PARTITION BY produk ORDER BY bulan) AS sebelumnya,
       lead(jumlah) OVER (PARTITION BY produk ORDER BY bulan) AS berikutnya
FROM penjualan_bulanan
ORDER BY produk, bulan;

Output yang Dihasilkan:

text 
   produk   |   bulan    | jumlah | sebelumnya | berikutnya 
------------+------------+--------+------------+------------
 Gula Pasir | 2026-02-01 |     60 |            |         70
 Gula Pasir | 2026-03-01 |     70 |         60 |         70
 Gula Pasir | 2026-04-01 |     70 |         70 |         55
 Gula Pasir | 2026-05-01 |     55 |         70 |         75
 Gula Pasir | 2026-06-01 |     75 |         55 |           
 Kopi Gayo  | 2026-01-01 |    120 |            |        135
 Kopi Gayo  | 2026-02-01 |    135 |        120 |        128
 Kopi Gayo  | 2026-03-01 |    128 |        135 |        150
 Kopi Gayo  | 2026-04-01 |    150 |        128 |        150
 Kopi Gayo  | 2026-05-01 |    150 |        150 |        160
 Kopi Gayo  | 2026-06-01 |    160 |        150 |           
 Teh Melati | 2026-01-01 |     80 |            |         95
 Teh Melati | 2026-02-01 |     95 |         80 |         80
 Teh Melati | 2026-03-01 |     80 |         95 |        110
 Teh Melati | 2026-04-01 |    110 |         80 |        130
 Teh Melati | 2026-06-01 |    130 |        110 |           
(16 rows)

Perhatikan tiga perilaku penting dari hasilnya. Pertama, sebelumnya pada baris pertama setiap partisi selalu NULL — Februari Gula Pasir tidak punya baris Januari untuk dibaca, Januari Kopi Gayo dan Januari Teh Melati juga tidak punya pendahulu. Kedua, berikutnya pada baris terakhir partisi juga NULL — Juni milik ketiga produk adalah tepi belakang masing-masing deret. Ketiga, LAG dan LEAD tidak pernah melintasi batas partisi: sebelumnya untuk Januari Teh Melati bukan Juni Gula Pasir dari baris di atasnya, melainkan NULL, karena partisi Teh Melati baru dimulai di baris itu. Setiap partisi benar-benar berdiri sendiri.

LAG Tanpa OVER: Error yang Jelas #

LAG dan LEAD adalah window function sejati — keduanya tidak ada maknanya tanpa klausa OVER. Berbeda dari fungsi agregat seperti sum yang juga berlaku sebagai fungsi biasa, lag tanpa OVER langsung ditolak parser:

sql lag-tanpa-over-error.sql 
-- LAG tanpa OVER bukan window function: error yang diharapkan
SELECT produk, bulan, lag(jumlah)
FROM penjualan_bulanan
ORDER BY produk, bulan;

Output yang Dihasilkan:

text 
psql:lag-tanpa-over-error.sql:4: ERROR:  window function lag requires an OVER clause
LINE 1: SELECT produk, bulan, lag(jumlah)
                              ^

Pesan window function lag requires an OVER clause tidak menyisakan ruang tafsir: OVER adalah bagian tak terpisahkan dari fungsi ini. Yang bisa Anda lupakan tanpa peringatan adalah ORDER BY di dalam OVER — lag(jumlah) OVER (PARTITION BY produk) sah secara sintaks, tetapi urutan baris di dalam partisi tidak terdefinisi sehingga “baris sebelumnya” bisa berupa baris mana pun. Tidak ada error, tidak ada warning — hanya angka yang salah diam-diam.

Argumen Offset dan Nilai Default #

LAG menerima hingga tiga argumen: kolom yang dibaca, offset (berapa baris ke belakang, default 1), dan nilai default pengganti NULL bila offset menjulur melewati tepi partisi. Contoh berikut memakai ketiganya sekaligus — dan sekalian memperkenalkan klausa WINDOW yang memberi nama pada spesifikasi jendela agar tidak menulis OVER (...) panjang tiga kali:

sql nilai-default.sql 
-- argumen tambahan LAG: offset (berapa baris ke belakang) dan nilai default
SELECT bulan,
       produk,
       jumlah,
       lag(jumlah)       OVER w AS lag_1,
       lag(jumlah, 2)    OVER w AS lag_2,
       lag(jumlah, 1, 0) OVER w AS lag_default_0
FROM penjualan_bulanan
WHERE produk = 'Gula Pasir'
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY bulan;

Output yang Dihasilkan:

text 
   bulan    |   produk   | jumlah | lag_1 | lag_2 | lag_default_0 
------------+------------+--------+-------+-------+---------------
 2026-02-01 | Gula Pasir |     60 |       |       |             0
 2026-03-01 | Gula Pasir |     70 |    60 |       |            60
 2026-04-01 | Gula Pasir |     70 |    70 |    60 |            70
 2026-05-01 | Gula Pasir |     55 |    70 |    70 |            70
 2026-06-01 | Gula Pasir |     75 |    55 |    70 |            55
(5 rows)

Mari kita bedah tiga kolomnya pada deret Gula Pasir (60, 70, 70, 55, 75):

  1. lag(jumlah) OVER w: bentuk baku — offset 1. Februari mendapat NULL karena tidak ada baris sebelumnya di partisi itu.
  2. lag(jumlah, 2) OVER w: membaca dua baris ke belakang — April (70) membaca Februari (60). Dua baris pertama mendapat NULL karena offsetnya menjulur melewati awal partisi.
  3. lag(jumlah, 1, 0) OVER w: nilai default 0 menggantikan NULL — Februari sekarang membaca 0 alih-alih NULL. LEAD punya bentuk argumen yang sama persis: lead(jumlah, 1, 0).

Klausa WINDOW w AS (...) mendefinisikan sekali dan dipakai berulang oleh ketiga kolom — nilainya identik dengan menulis OVER (PARTITION BY produk ORDER BY bulan) di setiap kolom. Nama w hanya berlaku dalam satu query.

Nilai default terlihat praktis, tetapi gunakan dengan sadar. NULL berperilaku jujur: operasi aritmetika apa pun yang menyentuh NULL menghasilkan NULL, sehingga baris tanpa pendahulu otomatis terkecuali dari perhitungan. Angka 0 berperilaku seperti data sungguhan: jumlah - lag(jumlah, 1, 0) pada Februari menghasilkan 60 - 0 = 60, seolah-olah penjualan “tumbuh” dari nol, dan bila angka 0 itu dipakai sebagai pembagi, query Anda berhenti dengan division by zero. Bila NULL di tepi partisi mengganggu tampilan, coalesce(lag(jumlah) OVER w, 0) di lapisan presentasi lebih aman daripada default di dalam LAG — pembaca query tetap melihat bahwa sumber datanya memang tidak ada.

Analisis Pertumbuhan Bulanan #

Sekarang pola lengkapnya: pertumbuhan persen dihitung dari nilai sekarang dikurangi nilai sebelumnya, dibagi nilai sebelumnya. Karena jumlah bertipe integer, pengurangan dua integer menghasilkan integer yang kalau dibagi integer lagi akan dibulatkan ke bawah — cast ::numeric sebelum membagi menjaga pecahannya. Kolom bonus jeda_hari menghitung selisih tanggal antar baris sebagai detektor bulan yang bolong:

sql pertumbuhan.sql 
-- analisis pertumbuhan bulanan: (sekarang - sebelumnya) / sebelumnya
SELECT produk,
       bulan,
       jumlah,
       lag(jumlah) OVER w AS sebelumnya,
       round((jumlah - lag(jumlah) OVER w)::numeric
             / lag(jumlah) OVER w * 100, 1) AS pertumbuhan_persen,
       bulan - lag(bulan) OVER w AS jeda_hari
FROM penjualan_bulanan
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY produk, bulan;

Output yang Dihasilkan:

text 
   produk   |   bulan    | jumlah | sebelumnya | pertumbuhan_persen | jeda_hari 
------------+------------+--------+------------+--------------------+-----------
 Gula Pasir | 2026-02-01 |     60 |            |                    |          
 Gula Pasir | 2026-03-01 |     70 |         60 |               16.7 |        28
 Gula Pasir | 2026-04-01 |     70 |         70 |                0.0 |        31
 Gula Pasir | 2026-05-01 |     55 |         70 |              -21.4 |        30
 Gula Pasir | 2026-06-01 |     75 |         55 |               36.4 |        31
 Kopi Gayo  | 2026-01-01 |    120 |            |                    |          
 Kopi Gayo  | 2026-02-01 |    135 |        120 |               12.5 |        31
 Kopi Gayo  | 2026-03-01 |    128 |        135 |               -5.2 |        28
 Kopi Gayo  | 2026-04-01 |    150 |        128 |               17.2 |        31
 Kopi Gayo  | 2026-05-01 |    150 |        150 |                0.0 |        30
 Kopi Gayo  | 2026-06-01 |    160 |        150 |                6.7 |        31
 Teh Melati | 2026-01-01 |     80 |            |                    |          
 Teh Melati | 2026-02-01 |     95 |         80 |               18.8 |        31
 Teh Melati | 2026-03-01 |     80 |         95 |              -15.8 |        28
 Teh Melati | 2026-04-01 |    110 |         80 |               37.5 |        31
 Teh Melati | 2026-06-01 |    130 |        110 |               18.2 |        61
(16 rows)

Deret Kopi Gayo terbaca mulus: +12.5%, -5.2%, +17.2%, 0.0%, +6.7% — dua bulan seri 150 menghasilkan pertumbuhan nol persen, dan baris pertama tiap partisi otomatis NULL karena pembaginya NULL. Tetapi lihat baris terakhir Teh Melati: jeda_hari bernilai 61, bukan 28–31 seperti baris lainnya. Bulan Mei Teh Melati memang tidak ada di tabel, sehingga LAG pada bulan Juni membaca baris April — angka 18.2% itu adalah pertumbuhan dua bulan (April ke Juni), bukan bulan-ke-bulan. Inilah batas fundamental fungsi offset: LAG dan LEAD berjalan di atas baris, bukan di atas kalender — mereka tidak tahu bahwa sebuah bulan terlewat. Bila deret wajib rapat, genapi dulu tanggalnya (misalnya lewat generate_series) sebelum menghitung pertumbuhan; kolom pemantau seperti jeda_hari setidaknya memberi tahu Anda kapan hal itu terjadi.

FIRST_VALUE dan LAST_VALUE: Nilai dari Tepi Jendela #

Dua saudara LAG/LEAD yang layak dikenal: FIRST_VALUE(kolom) membaca nilai baris pertama partisi, LAST_VALUE(kolom) baris terakhirnya. Pada bagian ini ada satu kejutan yang wajib didemokan — bandingkan dua kolom last_value berikut:

sql first-dan-last-value.sql 
-- FIRST_VALUE dan LAST_VALUE: nilai dari tepi jendela
SELECT bulan,
       jumlah,
       first_value(jumlah) OVER w AS nilai_pertama,
       last_value(jumlah)  OVER w AS terakhir_default,
       last_value(jumlah)  OVER (PARTITION BY produk ORDER BY bulan
                                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS terakhir_partisi
FROM penjualan_bulanan
WHERE produk = 'Kopi Gayo'
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY bulan;

Output yang Dihasilkan:

text 
   bulan    | jumlah | nilai_pertama | terakhir_default | terakhir_partisi 
------------+--------+---------------+------------------+------------------
 2026-01-01 |    120 |           120 |              120 |              160
 2026-02-01 |    135 |           120 |              135 |              160
 2026-03-01 |    128 |           120 |              128 |              160
 2026-04-01 |    150 |           120 |              150 |              160
 2026-05-01 |    150 |           120 |              150 |              160
 2026-06-01 |    160 |           120 |              160 |              160
(6 rows)

Kolom nilai_pertama bekerja sesuai harapan: 120 di semua baris, karena frame default membentang dari awal partisi sampai baris saat ini. Kolom terakhir_default justru menyalin nilai baris saat ini — 120, 135, 128, 150, 150, 160 — bukan nilai baris terakhir partisi. Penyebabnya adalah window frame default yang sudah kita singgung di chapter OVER dan PARTITION BY : saat ORDER BY hadir, frame berhenti di baris saat ini (CURRENT ROW), dan LAST_VALUE membaca tepi belakang frame itu. Kolom terakhir_partisi memperbaikinya dengan frame eksplisit ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING yang membentang sampai akhir partisi — kini semua baris membaca 160. Chapter Window Frame membedah kendali frame ini sampai tuntas.

Dengan LAG dan LEAD, jendela Anda kini membaca nilai lintas baris: pendahulu, penerus, dan tepi partisi. Analisis pertumbuhan yang kita bangun sebenarnya baru memakai separuh kekuatan frame — semua perhitungan kita melihat ke belakang sampai baris saat ini. Bagaimana bila jendelanya perlu dibatasi, misalnya rata-rata bergerak tiga bulan terakhir? Itu pekerjaan window frame, dan chapter Running Total membuka pembahasannya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel.sql

Unduh berkas
sql 
-- tabel latihan: penjualan bulanan per produk Toko Maju Jaya, semester I 2026
CREATE TABLE penjualan_bulanan (
    id      integer PRIMARY KEY,
    produk  text NOT NULL,
    bulan   date NOT NULL,
    kanal   text NOT NULL,
    jumlah  integer NOT NULL
);

INSERT INTO penjualan_bulanan (id, produk, bulan, kanal, jumlah) VALUES
    ( 1, 'Kopi Gayo',  DATE '2026-01-01', 'toko',   120),
    ( 2, 'Teh Melati', DATE '2026-01-01', 'toko',    80),
    ( 3, 'Kopi Gayo',  DATE '2026-02-01', 'online', 135),
    ( 4, 'Teh Melati', DATE '2026-02-01', 'toko',    95),
    ( 5, 'Gula Pasir', DATE '2026-02-01', 'toko',    60),
    ( 6, 'Gula Pasir', DATE '2026-03-01', 'toko',    70),
    ( 7, 'Kopi Gayo',  DATE '2026-03-01', 'toko',   128),
    ( 8, 'Teh Melati', DATE '2026-03-01', 'toko',    80),
    ( 9, 'Gula Pasir', DATE '2026-04-01', 'toko',    70),
    (10, 'Kopi Gayo',  DATE '2026-04-01', 'online', 150),
    (11, 'Teh Melati', DATE '2026-04-01', 'toko',   110),
    (12, 'Kopi Gayo',  DATE '2026-05-01', 'online', 150),
    (13, 'Gula Pasir', DATE '2026-05-01', 'toko',    55),
    (14, 'Kopi Gayo',  DATE '2026-06-01', 'online', 160),
    (15, 'Teh Melati', DATE '2026-06-01', 'online', 130),
    (16, 'Gula Pasir', DATE '2026-06-01', 'toko',    75);

SELECT id, produk, bulan, kanal, jumlah
FROM penjualan_bulanan
ORDER BY bulan, produk;

dasar-lag-lead.sql

Unduh berkas
sql 
-- LAG membaca baris sebelumnya, LEAD membaca baris berikutnya dalam partisi
SELECT produk,
       bulan,
       jumlah,
       lag(jumlah)  OVER (PARTITION BY produk ORDER BY bulan) AS sebelumnya,
       lead(jumlah) OVER (PARTITION BY produk ORDER BY bulan) AS berikutnya
FROM penjualan_bulanan
ORDER BY produk, bulan;

first-dan-last-value.sql

Unduh berkas
sql 
-- FIRST_VALUE dan LAST_VALUE: nilai dari tepi jendela
SELECT bulan,
       jumlah,
       first_value(jumlah) OVER w AS nilai_pertama,
       last_value(jumlah)  OVER w AS terakhir_default,
       last_value(jumlah)  OVER (PARTITION BY produk ORDER BY bulan
                                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS terakhir_partisi
FROM penjualan_bulanan
WHERE produk = 'Kopi Gayo'
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY bulan;

lag-tanpa-over-error.sql

Unduh berkas
sql 
-- LAG tanpa OVER bukan window function: error yang diharapkan
SELECT produk, bulan, lag(jumlah)
FROM penjualan_bulanan
ORDER BY produk, bulan;

nilai-default.sql

Unduh berkas
sql 
-- argumen tambahan LAG: offset (berapa baris ke belakang) dan nilai default
SELECT bulan,
       produk,
       jumlah,
       lag(jumlah)       OVER w AS lag_1,
       lag(jumlah, 2)    OVER w AS lag_2,
       lag(jumlah, 1, 0) OVER w AS lag_default_0
FROM penjualan_bulanan
WHERE produk = 'Gula Pasir'
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY bulan;

pertumbuhan.sql

Unduh berkas
sql 
-- analisis pertumbuhan bulanan: (sekarang - sebelumnya) / sebelumnya
SELECT produk,
       bulan,
       jumlah,
       lag(jumlah) OVER w AS sebelumnya,
       round((jumlah - lag(jumlah) OVER w)::numeric
             / lag(jumlah) OVER w * 100, 1) AS pertumbuhan_persen,
       bulan - lag(bulan) OVER w AS jeda_hari
FROM penjualan_bulanan
WINDOW w AS (PARTITION BY produk ORDER BY bulan)
ORDER BY produk, bulan;