Window Function SQLite: OVER, PARTITION BY, dan Frame

Window Function Menambah Kolom Tanpa Meringkas Baris

GROUP BY meringkas banyak baris menjadi satu. Window function melakukan hal yang berbeda: ia menghitung nilai di sekumpulan baris yang berhubungan, tapi tetap menyimpan setiap baris input di output. Kamu mendapat detail per baris dan nilai agregatnya, berdampingan.

Bentuknya selalu sama: sebuah fungsi, lalu OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

Kolom total_all menampilkan total keseluruhan dari semua baris, diulang di setiap baris. Baris aslinya tidak disentuh. Bandingkan dengan SELECT SUM(amount) FROM sales: angkanya sama, tapi hanya satu baris yang dikembalikan. Window function memberimu kedua tampilan sekaligus.

PARTITION BY: Agregasi di Dalam Kelompok

OVER () yang kosong melakukan agregasi atas seluruh tabel. Tambahkan PARTITION BY untuk melakukan agregasi di dalam kelompok, mirip GROUP BY, tapi sekali lagi tanpa meringkas baris.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Setiap baris mendapat total wilayahnya dan porsinya dari total itu. Dengan GROUP BY biasa, kamu akan kehilangan detail per karyawan. Itulah keunggulan utama window function: detail dan agregat dalam satu query.

Peringkat: ROW_NUMBER, RANK, DENSE_RANK

Keluarga fungsi peringkat memberi nomor pada baris sesuai ORDER BY di dalam OVER. Ketiga variannya berbeda dalam cara menangani nilai seri.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Membaca output-nya:

• ROW_NUMBER() selalu unik: nilai seri dipecah secara sembarang. Pakai saat kamu butuh nomor yang stabil dan berbeda untuk setiap baris.
• RANK() memberi peringkat yang sama pada baris yang seri, lalu melompati nomor berikutnya. Dua pemain yang seri di peringkat 1 diikuti peringkat 3.
• DENSE_RANK() juga memberi peringkat seri, tapi tidak melompat. Peringkat berikutnya adalah 2.

Untuk "N teratas per kelompok", gabungkan fungsi peringkat dengan PARTITION BY lalu filter di query luar, karena WHERE tidak bisa merujuk window function secara langsung:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Dua penjual teratas per wilayah.

LAG dan LEAD: Melihat Baris Tetangga

LAG(col) mengembalikan nilai col dari baris sebelumnya di dalam window. LEAD(col) melihat ke depan. Keduanya sempurna untuk pertanyaan tentang perubahan dari waktu ke waktu.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

yesterday pada baris pertama bernilai NULL, karena tidak ada baris sebelumnya. Kamu bisa memberikan nilai default: LAG(celsius, 1, celsius) OVER (ORDER BY day) akan memakai nilai hari ini saat tidak ada baris sebelumnya.

LEAD adalah kebalikannya. Gabungkan keduanya dengan PARTITION BY untuk mendapatkan urutan per kelompok, misalnya membandingkan penjualan bulan ini dengan bulan sebelumnya di setiap wilayah.

Total Berjalan dengan Window Frame

Tambahkan ORDER BY di dalam OVER, dan fungsi agregat seperti SUM, AVG, COUNT mulai menghitung secara kumulatif:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Dua hal yang perlu diperhatikan:

• SUM(amount) OVER (ORDER BY day) adalah total berjalan. Frame default saat kamu menulis ORDER BY tanpa frame eksplisit adalah "dari awal window sampai baris saat ini."
• Kolom kedua memakai frame eksplisit: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Itu sliding window 3 baris, yaitu rata-rata bergerak.

Model mental untuk frame: setiap window function dievaluasi atas sebuah frame baris, yang didefinisikan relatif terhadap baris saat ini. Frame yang umum:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: total berjalan (default implisit).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: window yang mengikuti di belakang.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: seluruh partisi.

ROWS menghitung baris fisik. Ada juga RANGE, yang mengelompokkan berdasarkan nilai, praktis saat ada nilai seri di kolom ORDER BY dan kamu ingin semuanya diperlakukan sebagai satu langkah.

FIRST_VALUE, LAST_VALUE, NTILE

Beberapa window function lain yang layak diketahui:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE dan LAST_VALUE mengembalikan nilai pertama atau terakhir di dalam frame. Dengan LAST_VALUE, perhatikan frame-nya: frame default berakhir di CURRENT ROW, jadi biasanya kamu butuh ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING untuk mendapatkan nilai terakhir partisi yang sebenarnya.
• NTILE(n) membagi baris menjadi n kelompok yang kurang lebih sama besar, berguna untuk kuartil, persentil, dan pembagian ala A/B.

Memberi Nama Window dengan WINDOW

Saat beberapa kolom memakai klausa OVER (...) yang sama, pengulangannya jadi melelahkan. SQLite memungkinkanmu memberi nama sebuah window sekali lalu memakainya berulang kali:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Query yang sama, lebih sedikit kebisingan. Klausa WINDOW ditulis setelah WHERE/GROUP BY/HAVING dan sebelum ORDER BY.

Window Function vs GROUP BY

Keduanya melibatkan agregasi, tapi menjawab pertanyaan yang berbeda:

• GROUP BY meringkas. Satu baris per kelompok. Pakai saat kamu hanya butuh ringkasannya.
• Window function mempertahankan. Setiap baris input tetap ada, dengan kolom tambahan hasil perhitungan di sampingnya.

Jika kamu mendapati dirimu melakukan GROUP BY lalu menggabungkan kembali hasil agregatnya ke tabel asli, itu tanda kuat bahwa window function bisa menyelesaikannya dalam satu query.

Beberapa Jebakan

• WHERE tidak bisa merujuk window function. Filter dijalankan sebelum window dihitung. Bungkus query-nya dalam subquery atau CTE lalu filter di tingkat luar.
• Frame implisit bisa menjebak. SUM(x) OVER (ORDER BY y) adalah total berjalan karena frame default-nya RANGE UNBOUNDED PRECEDING. Jika yang kamu inginkan adalah jumlah seluruh partisi, tulis OVER (PARTITION BY ...) tanpa ORDER BY, atau tentukan frame-nya secara eksplisit.
• LAST_VALUE mengejutkan semua orang di kali pertama. Dengan frame default yang berakhir di baris saat ini, fungsi ini mengembalikan nilai saat ini, bukan nilai terakhir partisi. Timpa frame-nya.
• Window function butuh SQLite 3.25+ (dirilis tahun 2018). Instalasi yang cukup modern sudah memilikinya, tapi beberapa lingkungan tertanam tertinggal.

Berikutnya: Kolom Generated

Window function adalah perhitungan saat query dijalankan. Halaman berikutnya membahas perhitungan saat penyimpanan: kolom generated, yang nilainya didefinisikan oleh sebuah ekspresi dan diperbarui otomatis saat data di baliknya berubah.