Window Function ③ — Ranking, Fungsi Analitik, dan Urutan Eksekusi

Ranking — ROW_NUMBER / RANK / DENSE_RANK

Fungsi ranking khusus adalah ROW_NUMBER() / RANK() / DENSE_RANK().

Ketiganya memberi nomor pada baris dalam urutan yang diberikan oleh OVER (ORDER BY ...), tetapi mereka menangani nilai sama (equal values) secara berbeda.

SQL di bawah — dan diagram yang memvisualkan hasilnya — menelusuri kasus konkret dengan total yang nilainya sama baris demi baris.

-- Hitung total penjualan tiap karyawan, lalu peringkatkan dalam urutan total menurun dengan 3 cara
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- Kamu bisa pertama-tama agregasi dengan GROUP BY dan kemudian peringkatkan agregat tersebut

Bayangkan kamu ingin ranking karyawan berdasarkan total penjualan. Ada nilai yang sama, jadi kamu ingin melihat ketiganya berdampingan: penomoran berurutan, peringkat berikutnya melompat, dan tidak melompat. (Jika query kamu jalan dengan benar, penjelasan akan muncul.)

① GROUP BY tabel sales berdasarkan emp_id dan pilih SUM(amount) dengan alias total.

② Pada window yang diurutkan berdasarkan total menurun, tambahkan ROW_NUMBER() dengan alias rn, RANK() dengan alias rk, dan DENSE_RANK() dengan alias dr.

③ Penilai memeriksa urutan baris, jadi akhiri dengan ORDER BY total DESC, emp_id (menurun berdasarkan total, naik berdasarkan emp_id untuk nilai sama).

LAG / LEAD — tarik nilai dari baris sebelumnya / berikutnya

LAG(column) menarik nilai dari baris sebelumnya ke baris saat ini, dan LEAD(column) menarik dari baris berikutnya (lag = tertinggal, lead = mendahului).

OVER (PARTITION BY ... ORDER BY ...) menentukan apa arti "sebelumnya" dan "berikutnya" dengan menetapkan urutan.

Pakai untuk selisih periode-ke-periode (vs. penjualan sebelumnya), delta vs. bulan lalu, atau perbandingan dengan acara terjadwal berikutnya.

LAG(amount) mengembalikan NULL pada baris pertama tiap partisi (tidak ada baris sebelumnya); demikian pula LEAD adalah NULL pada baris terakhir.

Argumen kedua opsional mengontrol offset ("berapa baris ke belakang") dan yang ketiga menetapkan nilai default ketika baris target tidak ada — misal, LAG(amount, 1, 0).

Kurangkan dengan amount - LAG(amount) OVER (...) untuk mendapatkan selisih vs. baris sebelumnya secara langsung.

-- Tampilkan penjualan sebelumnya (LAG) dan selisih vs. penjualan sebelumnya
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- Pakai LEAD jika kamu ingin menampilkan penjualan berikutnya di samping baris saat ini
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

Bayangkan kamu ingin daftar penjualan per karyawan diurutkan berdasarkan tanggal, dengan penjualan sebelumnya dan selisih vs. penjualan sebelumnya di samping setiap baris.

① Pilih emp_id, sale_date, dan amount dari sales.

② Pada window yang dipartisi per karyawan dan diurutkan berdasarkan sale_date, tambahkan amount baris sebelumnya dengan alias prev_amount.

③ Juga tambahkan amount dikurangi amount sebelumnya dengan alias diff_prev.

④ Penilai memeriksa urutan baris, jadi akhiri dengan ORDER BY emp_id, sale_date.

NTILE — bagi baris yang diurutkan menjadi n grup berukuran merata

NTILE(n) membagi baris yang diurutkan oleh OVER (ORDER BY ...) menjadi n grup berukuran semerata mungkin, memberi label setiap baris dengan nomor grup 1 sampai n.

Pakai untuk kuartil, kuintil, "top 25%", dan bucketing serupa.

Ketika jumlah baris tidak habis dibagi n, grup-grup awal mendapat tambahan satu baris masing-masing (misal, membagi 5 baris menjadi 4 grup menghasilkan 2 / 1 / 1 / 1).

Gabungkan dengan PARTITION BY untuk hal seperti "bagi penjualan setiap karyawan menjadi bucket atas / bawah dalam karyawan tersebut."

Diagram di bawah menunjukkan apa yang terjadi ketika 5 penjualan emp_id=11 diurutkan berdasarkan amount menurun dan dibagi dengan NTILE(4).

-- Per karyawan, bagi penjualan karyawan tersebut menjadi 4 grup berdasarkan amount menurun
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- Bagi semua penjualan menjadi 4 bucket berdasarkan amount (top 25% memiliki quartile = 1)
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

Bayangkan kamu ingin daftar penjualan yang dibagi menjadi 4 grup per karyawan (berdasarkan amount menurun), ditampilkan dalam urutan kuartil dari grup atas ke bawah.

① Pilih emp_id, sale_date, dan amount dari sales.

② Pada window yang dipartisi per karyawan dan diurutkan berdasarkan amount menurun, hitung NTILE(4) dan tambahkan sebagai kolom ke-4 dengan alias quartile.

③ Penilai memeriksa urutan baris, jadi akhiri dengan `ORDER BY quartile, emp_id, sale_id` (ORDER BY bisa merujuk alias quartile yang dihitung di SELECT).

Bayangkan kamu ingin menyaring hanya ke top 25% (quartile = 1). Coba tambahkan WHERE quartile = 1 ke query Latihan 3.

① Pakai SELECT yang sama dari Latihan 3 dan tambahkan WHERE quartile = 1 setelah FROM sales.

② Menjalankan ini mengembalikan error (error berarti kamu sudah benar).

③ Bagian berikutnya, "Urutan eksekusi SQL", menjelaskan kenapa. quartile bekerja di ORDER BY tetapi tidak bekerja di WHERE — simpan teka-teki itu di pikiran saat kita lanjut.

Urutan eksekusi SQL — mengapa kamu tidak bisa menaruh window function di WHERE

Kamu mungkin bertanya, "tidak bisa kah saya menyaring berdasarkan peringkat yang baru saja saya hitung, seperti WHERE rk = 1?"

Kamu akan mendapat error. Alasannya adalah urutan eksekusi logis SQL.

Urutan kamu menulis klausa (SELECT → FROM → WHERE ...) bukan urutan ketika mereka dievaluasi.

Evaluasi kira-kira berjalan: FROM / JOIN (susun tabel) → WHERE (saring baris) → GROUP BY (kelompokkan) → HAVING (saring grup) → SELECT (hitung kolom — di sinilah window function jalan) → ORDER BY (urutkan) → LIMIT (potong).

Karena WHERE jalan sebelum SELECT, ia tidak bisa melihat peringkat yang akan dihitung SELECT.

Untuk filter pada peringkat, dorong query yang menghitung window function ke dalam subquery (atau CTE) dan terapkan WHERE di luar.

SELECT bagian dalam selesai menghitung window function, dan query luar lalu bisa memperlakukan hasil itu sebagai kolom biasa.

-- Ini error: WHERE jalan sebelum SELECT, jadi rk belum ada
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- Benar: hitung peringkat di subquery dalam, filter di luar
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

Bayangkan kamu ingin hanya top 3 karyawan berdasarkan total penjualan. Karena kamu menyaring pada peringkat, kamu butuh subquery untuk menyiasati urutan eksekusi.

① Di subquery dalam, GROUP BY sales berdasarkan emp_id dan pilih SUM(amount) dengan alias total, plus RANK() OVER (ORDER BY SUM(amount) DESC) dengan alias rk. Beri alias subquery ranked.

② Saring query luar dengan WHERE rk <= 3.

③ Penilai memeriksa urutan baris, jadi akhiri dengan ORDER BY rk, emp_id (naik berdasarkan rk, lalu naik berdasarkan emp_id untuk nilai sama).