Window function di SQL
Sebuah window function menghitung di atas sekumpulan baris dan tetap mengembalikan setiap baris. GROUP BY meringkas setiap grup menjadi satu baris; window function membiarkan barisnya apa adanya dan menambahkan kolom di sampingnya, seperti peringkat, total kumulatif, atau nilai bulan lalu.
Tabel contoh
| region | month | revenue |
|---|---|---|
| North | 2026-01 | 120 |
| North | 2026-02 | 150 |
| North | 2026-03 | 150 |
| South | 2026-01 | 90 |
| South | 2026-02 | 80 |
| South | 2026-03 | 110 |
Sintaksnya
function_name(...) OVER (PARTITION BY ... ORDER BY ...)
OVER yang menjadikannya window function. PARTITION BY membagi baris menjadi grup dan memulai perhitungan lagi di setiap grup; kalau dihilangkan, window-nya adalah seluruh tabel. ORDER BY di dalam OVER menentukan urutan fungsi menelusuri baris. Ini tidak mengurutkan output Anda: untuk itu, tambahkan ORDER BY di akhir query.
ROW_NUMBER, RANK, dan DENSE_RANK
SELECT region, month, revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
FROM sales;
Di North, Februari dan Maret seri di 150. ROW_NUMBER tetap memberi mereka 1 dan 2, dan bulan mana yang mendapat 1 ditentukan oleh database. RANK memberi keduanya 1 lalu melompat ke 3 untuk Januari. DENSE_RANK memberi keduanya 1 dan Januari 2. South tidak punya nilai seri, jadi ketiganya mengembalikan 1, 2, dan 3. Pilih fungsinya menurut cara Anda ingin menangani nilai seri, dan tambahkan kolom kedua ke ORDER BY kalau hasilnya harus selalu sama.
Baris teratas di setiap grup
Anda tidak bisa memfilter window function di WHERE, karena WHERE berjalan sebelum window dihitung. Beri nomor pada baris di subquery atau langkah WITH, lalu filter di luarnya:
WITH ranked AS (
SELECT region, month, revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC, month) AS rn
FROM sales
)
SELECT region, month, revenue FROM ranked WHERE rn = 1;
Hasilnya Februari untuk North dan Maret untuk South. Snowflake, BigQuery, dan DuckDB juga menerima QUALIFY rn = 1 di akhir query pertama.
Total kumulatif dan rata-rata bergerak
SUM(revenue) OVER (PARTITION BY region ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
Total kumulatif North adalah 120, 270, 420 dan South 90, 170, 280. Ubah frame menjadi ROWS BETWEEN 2 PRECEDING AND CURRENT ROW dan pakai AVG untuk mendapat rata-rata bergerak tiga bulan. Tulis frame secara lengkap: dengan ORDER BY tanpa frame, kebanyakan database memakai RANGE, yang menjumlahkan baris seri sekaligus dan bisa membuat total kumulatif melonjak.
LAG dan LEAD
LAG(revenue) OVER (PARTITION BY region ORDER BY month) mengembalikan pendapatan bulan sebelumnya di setiap baris, dan LEAD mengembalikan bulan berikutnya. Kurangkan untuk mendapat perubahannya: North dari tidak ada (NULL di Januari) menjadi +30, lalu 0; South menjadi -10, lalu +30. Baris pertama setiap region tidak punya bulan sebelumnya, jadi tentukan apakah NULL atau 0 jawaban yang tepat untuk laporan Anda.
Dukungan database
PostgreSQL, SQL Server, Oracle, MySQL 8.0 ke atas, SQLite 3.25 ke atas, BigQuery, Snowflake, dan DuckDB semuanya mendukung window function. Latihan di pelajaran ini berjalan di DuckDB pada tabel Kestrel, toko online rekaan, dan memeriksa query Anda dari baris yang dikembalikannya.