Window function

GROUP BY meringkas baris. Window function melakukan aritmetika yang sama dan menyimpan setiap baris, menambahkan hasilnya sebagai kolom lain. Satu perbedaan itulah sebabnya banyak query yang butuh subquery dalam bentuk GROUP BY tidak butuh apa pun dalam bentuk window.

Kedua mesin mendukungnya. MySQL sejak 8.0 — di 5.7 semua ini tidak ada.

SELECT id, customer_id, placed_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS recency
FROM "order";

Baca OVER (...) sebagai “melihat himpunan baris ini, dalam urutan ini”:

• PARTITION BY — mulai ulang perhitungan per grup. Seperti GROUP BY, tapi tanpa meringkas.
• ORDER BY — urutan di dalam partisi. Untuk peringkat dia menentukan peringkatnya; untuk agregat dia menentukan arti “sejauh ini”.

Hilangkan PARTITION BY dan seluruh hasilnya jadi satu partisi.

Peringkat, dan tiga fungsi yang sering tertukar

Diberi skor 90, 90, 80:

Pilih dengan sadar. Papan peringkat di mana dua orang seri di posisi pertama dan orang berikutnya “ketiga” menginginkan RANK. Daftar bernomor menginginkan ROW_NUMBER. Dan karena pemecahan seri ROW_NUMBER itu sembarang, tambahkan kolom unik ke ORDER BY-nya kalau hasilnya harus stabil — aturan yang sama dengan pagination di materi 1.

N teratas per grup

Ini query yang membuat window function pantas dipelajari.

-- Order terbaru per customer
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, id DESC) AS rn
  FROM "order" o
) ranked
WHERE rn = 1;

Subquery-nya bukan pilihan, dan alasannya adalah urutan klausa dari materi 3: window function dihitung setelah WHERE, pada tahap yang sama dengan SELECT. Jadi WHERE rn = 1 tidak bisa melihat rn, dan WHERE ROW_NUMBER() OVER (...) = 1 adalah error di kedua mesin. Bungkus dengan subquery atau CTE.

Khusus untuk N = 1, PostgreSQL punya DISTINCT ON yang lebih singkat dari materi 1, dan kedua mesin punya bentuk LATERAL dari materi 5. Mana yang tercepat tergantung N dan index-mu; bentuk window adalah yang paling bisa digeneralisasi.

Total berjalan dan rata-rata bergerak

SELECT placed_at, shipping_cents,
       SUM(shipping_cents) OVER (ORDER BY placed_at, id)                       AS running,
       AVG(shipping_cents) OVER (ORDER BY placed_at, id
                                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)     AS avg_7
FROM "order";

Agregat apa pun — SUM, AVG, COUNT, MIN, MAX — menjadi window function dengan menambahkan OVER.

Frame: ROWS versus RANGE, dan bawaannya

Frame adalah baris mana di dalam partisi yang sebenarnya dilihat fungsinya. Ini bagian yang menghasilkan angka salah tanpa bersuara.

Ketika kamu menulis ORDER BY tanpa klausa frame, bawaannya adalah:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE membandingkan berdasar nilai, jadi “baris saat ini” mencakup setiap baris sebaya dengan nilai ORDER BY yang sama. ROWS menghitung baris fisik dan berhenti di baris saat ini.

Dengan tiga order yang berbagi satu timestamp, SUM(...) OVER (ORDER BY placed_at) memberi ketiganya total berjalan yang sama — jumlah yang menyertakan semua sebayanya. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW memberi tiga nilai yang menaik. Keduanya bukan bug; keduanya menjawab pertanyaan berbeda. Sebutkan mana yang kamu maksud.

Korban klasiknya adalah LAST_VALUE:

-- Hampir tidak pernah yang dimaksud penulisnya: dengan frame bawaan, frame-nya
-- berakhir di baris saat ini, jadi "nilai terakhir" adalah nilai baris itu sendiri.
SELECT id, LAST_VALUE(status) OVER (PARTITION BY customer_id ORDER BY placed_at) FROM "order";

-- Yang dia maksud
SELECT id, LAST_VALUE(status) OVER (
         PARTITION BY customer_id ORDER BY placed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) FROM "order";

FIRST_VALUE kebetulan benar dengan frame bawaan, dan justru itulah sebabnya versi LAST_VALUE lolos ke produksi: separuh pasangannya bekerja.

LAG dan LEAD: baris sebelum dan sesudah

SELECT placed_at,
       LAG(placed_at)  OVER (PARTITION BY customer_id ORDER BY placed_at) AS prev_order,
       LEAD(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_order,
       placed_at - LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS gap
FROM "order";

Baris pertama setiap partisi tidak punya baris sebelumnya, jadi LAG mengembalikan NULL. Kedua fungsi menerima nilai bawaan: LAG(placed_at, 1, placed_at) memakai nilai saat ini alih-alih NULL. Inilah cara kamu menghitung selang, selisih, dan “apakah statusnya berubah” tanpa self join.

NTILE dan window bernama

NTILE(n) membelah partisi menjadi n keranjang yang kira-kira sama — kuartil, desil:

SELECT id, cents, NTILE(4) OVER (ORDER BY cents) AS quartile FROM product;

Mengulang OVER (...) yang panjang itu kebisingan. Namai sekali — kedua mesin mendukung klausa WINDOW:

SELECT id,
       ROW_NUMBER() OVER w AS rn,
       SUM(shipping_cents) OVER w AS running
FROM "order"
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at, id);

Di mana kedua mesin berbeda

-- PostgreSQL
COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id)

-- MySQL, arti yang sama
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id)

Bentuk CASE jalan di keduanya, jadi utamakan dia di apa pun yang harus portabel.

Window function tidak menyaring

Pantas dinyatakan dua kali karena ini error paling umum: window function tidak boleh muncul di WHERE, GROUP BY, atau HAVING. Dia dihitung setelah semuanya. Apa pun yang tampak seperti menyaring berdasar hasil window butuh subquery atau CTE yang membungkusnya — seperti contoh N-teratas di atas.

Kamu boleh memakainya di ORDER BY, karena itu berjalan terakhir.

Yang perlu dibawa pulang

• GROUP BY meringkas; window function menyimpan barisnya.
• ROW_NUMBER / RANK / DENSE_RANK hanya berbeda pada nilai kembar, dan cuma satu yang benar untuk kasusmu.
• Window function berjalan setelah WHERE. Menyaringnya butuh subquery.
• Frame bawaannya RANGE ... CURRENT ROW, yang menyertakan semua sebaya. Tulis ROWS kalau maksudmu baris.
• LAST_VALUE butuh frame UNBOUNDED FOLLOWING eksplisit supaya berguna.
• MySQL butuh 8.0, tidak punya FILTER, dan tidak punya fungsi persentil.