Window Functions

Agregasi dengan GROUP BY memiliki sifat yang mahal: sebelas baris detail_pesanan menjadi dua baris hasil, dan informasi per baris hilang begitu agregasi berjalan. Padahal banyak pertanyaan analitik justru membutuhkan keduanya sekaligus — harga tiap produk berdampingan dengan rata-rata kategorinya, tiap bulan berdampingan dengan akumulasi pendapatannya. Window function menjembatani kebutuhan itu: fungsi agregasi dijalankan di atas “jendela” sekumpulan baris tanpa meruntuhkan satu baris pun. Keterampilan ini sangat sering dipakai data engineer, dari deduplikasi data hingga laporan running total; seluruh contoh diuji nyata di SQLite pada database toko online dari Chapter Common Table Expressions.

OVER dan PARTITION BY #

Setiap window function ditandai klausul OVER (...). Tanpa PARTITION BY, fungsi menghitung atas seluruh hasil query; dengan PARTITION BY kategori, baris terbagi menjadi jendela per kategori. Contoh berikut membandingkan harga tiap produk dengan rata-rata kategorinya:

SELECT nama, kategori, harga,
       AVG(harga) OVER (PARTITION BY kategori) AS rata_kategori
FROM produk
ORDER BY kategori, harga DESC;

Output yang Dihasilkan:

nama | kategori | harga | rata_kategori
Buku Sains Data | buku | 145000 | 127500.0
Novel Laut Bercerita | buku | 110000 | 127500.0
Laptop Pro 14 | elektronik | 12500000 | 5583333.333333333
Smartphone X1 | elektronik | 3500000 | 5583333.333333333
TWS Earbuds | elektronik | 750000 | 5583333.333333333
Sneakers Klasik | fashion | 900000 | 510000.0
Dress Summer | fashion | 380000 | 510000.0
Kemeja Batik | fashion | 250000 | 510000.0
Kemeja Flanel | pakaian | 180000 | 137500.0
Kaos Polos Katun | pakaian | 95000 | 137500.0

Mari kita telaah klausa-klausa query di atas:

1. AVG(harga) OVER (PARTITION BY kategori): AVG dihitung untuk jendela baris sekategori lalu ditempelkan pada tiap baris — hasil tetap sepuluh baris, sama dengan jumlah baris produk.
2. PARTITION BY kategori: pembatas jendela. Kemeja Flanel dibandingkan dengan rata-rata pakaian (137500.0), bukan rata-rata seluruh produk.
3. ORDER BY di luar OVER: hanya mengatur urutan tampilan hasil, tidak memengaruhi perhitungan window.

Singkatnya: GROUP BY meruntuhkan baris menjadi satu per kelompok, sedangkan PARTITION BY membiarkan baris utuh dan menambahkan kolom agregasi.

ROW_NUMBER, RANK, dan DENSE_RANK #

Tiga fungsi penomoran ini sering tertukar. Contoh berikut menghitung jumlah pesanan per pelanggan lalu menomorinya dari yang paling sering berbelanja:

WITH jumlah_pesanan AS (
    SELECT p.nama, COUNT(*) AS total
    FROM pelanggan p
    JOIN pesanan ps ON ps.pelanggan_id = p.id
    GROUP BY p.nama
)
SELECT nama, total,
       ROW_NUMBER() OVER (ORDER BY total DESC, nama) AS nomor_urut,
       RANK()       OVER (ORDER BY total DESC) AS peringkat,
       DENSE_RANK() OVER (ORDER BY total DESC) AS peringkat_padat
FROM jumlah_pesanan
ORDER BY total DESC, nama;

Output yang Dihasilkan:

nama | total | nomor_urut | peringkat | peringkat_padat
Budi Santoso | 3 | 1 | 1 | 1
Dewi Lestari | 2 | 2 | 2 | 2
Siti Rahayu | 2 | 3 | 2 | 2
Andi Wijaya | 1 | 4 | 4 | 3
Rina Kartika | 1 | 5 | 4 | 3

Dua kelompok pelanggan seri — Dewi dan Siti dengan dua pesanan, Andi dan Rina dengan satu pesanan — dan di sinilah ketiga fungsi berbeda perlakuan:

Kolom nama pada ROW_NUMBER() berperan sebagai pemecah seri agar urutan Dewi–Siti dan Andi–Rina deterministik. Pelanggan tanpa pesanan — Farhan, Gita, dan Hendra — tidak muncul karena JOIN tipe dalam hanya mempertahankan baris yang cocok. Pilih ROW_NUMBER() untuk nomor unik, RANK() untuk peringkat yang melompat, dan DENSE_RANK() untuk peringkat rapat seperti peringkat kelas.

LAG dan LEAD: Membandingkan Baris Berurutan #

LAG() mengambil nilai baris sebelumnya dan LEAD() baris berikutnya, sesuai urutan di dalam OVER — fondasi analisis time series. Contoh berikut menghitung pendapatan bulanan beserta selisihnya terhadap bulan sebelumnya:

WITH pendapatan_bulanan AS (
    SELECT substr(ps.tanggal, 1, 7) AS bulan,
           SUM(d.subtotal) AS total
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    WHERE ps.status = 'selesai'
    GROUP BY bulan
)
SELECT bulan, total,
       LAG(total) OVER (ORDER BY bulan) AS bulan_sebelumnya,
       total - LAG(total) OVER (ORDER BY bulan) AS perubahan
FROM pendapatan_bulanan;

Output yang Dihasilkan:

bulan | total | bulan_sebelumnya | perubahan
2024-06 | 6280000 | NULL | NULL
2024-07 | 1150000 | 6280000 | -5130000

Dua hal perlu dicermati. Pertama, substr(ps.tanggal, 1, 7) memotong tanggal menjadi YYYY-MM agar pesanan bisa dikelompokkan per bulan — pola umum untuk tanggal teks ISO. Kedua, baris pertama tidak punya bulan sebelumnya sehingga LAG() mengembalikan NULL, dan NULL menular ke kolom perubahan karena aritmetika dengan NULL menghasilkan NULL.

Running Total dengan SUM OVER #

Menambahkan ORDER BY di dalam OVER mengubah SUM menjadi akumulasi berjalan: tiap baris menerima jumlah dari baris pertama hingga baris itu. Klausul ROWS BETWEEN menyatakan rentang frame secara eksplisit:

WITH pendapatan_bulanan AS (
    SELECT substr(ps.tanggal, 1, 7) AS bulan,
           SUM(d.subtotal) AS total
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    WHERE ps.status = 'selesai'
    GROUP BY bulan
)
SELECT bulan, total,
       SUM(total) OVER (
           ORDER BY bulan
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS total_berjalan
FROM pendapatan_bulanan;

Output yang Dihasilkan:

bulan | total | total_berjalan
2024-06 | 6280000 | 6280000
2024-07 | 1150000 | 7430000

Frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW berarti “akumulasi dari baris pertama sampai baris saat ini” — pendapatan Juli (1150000) ditambahkan di atas akumulasi Juni (6280000) menjadi 7430000.

Studi Kasus: Deduplikasi Baris Terbaru per Pelanggan #

Tabel hasil ingestion sering memuat beberapa baris per entitas. Laporan “status pesanan terakhir tiap pelanggan” menuntut kita menyisakan tepat satu baris terbaru per pelanggan, dan kombinasi ROW_NUMBER() + CTE adalah pola baku untuk masalah ini:

WITH pesanan_bernomor AS (
    SELECT ps.id, p.nama, ps.tanggal, ps.status,
           ROW_NUMBER() OVER (
               PARTITION BY ps.pelanggan_id
               ORDER BY ps.tanggal DESC
           ) AS nomor
    FROM pesanan ps
    JOIN pelanggan p ON p.id = ps.pelanggan_id
)
SELECT id, nama, tanggal, status
FROM pesanan_bernomor
WHERE nomor = 1
ORDER BY nama;

Output yang Dihasilkan:

id | nama | tanggal | status
7 | Andi Wijaya | 2024-07-05 | selesai
9 | Budi Santoso | 2024-07-20 | selesai
8 | Dewi Lestari | 2024-07-12 | diproses
5 | Rina Kartika | 2024-06-20 | dibatalkan
6 | Siti Rahayu | 2024-07-01 | diproses

Mari kita telaah cara kerja pola deduplikasi ini:

1. PARTITION BY ps.pelanggan_id: setiap pelanggan mendapat jendela sendiri, sehingga penomoran dimulai dari 1 untuk tiap pelanggan.
2. ORDER BY ps.tanggal DESC: pesanan terbaru di tiap jendela menerima nomor 1 — dari tiga pesanan Budi Santoso, pesanan 9 tertanggal paling baru.
3. WHERE nomor = 1: query luar menyisakan satu baris per pelanggan; lima pelanggan yang punya pesanan menghasilkan lima baris hasil.

Window function melengkapi CTE: menghitung tanpa meruntuhkan baris — peringkat, perbandingan antarbaris, hingga akumulasi berjalan. Kebutuhan terakhir section ini: data nyata yang mengandung NULL dan label yang dibentuk dari kondisi. Chapter berikutnya, Conditional Expressions, membahas CASE WHEN, COALESCE, dan NULLIF untuk menanganinya.