Window Function ② — ORDER BY dan Frame (ROWS / RANGE)

Window dengan ORDER BY — menghasilkan running total

Tambahkan ORDER BY di dalam OVER dan target agregasi bergeser menjadi "dari awal window hingga baris saat ini" — nilainya tumbuh baris demi baris, menghasilkan running total (jumlah dari awal sampai baris saat ini).

Bentuknya adalah SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date).

PARTITION BY emp_id membagi window per karyawan, ORDER BY sale_date mengatur penjualan setiap karyawan berdasarkan tanggal, lalu running total dihitung per karyawan.

Diagram di bawah menelusuri bagaimana running total tumbuh untuk 5 penjualan emp_id=29 dalam urutan tanggal.

-- Tanpa ORDER BY: setiap baris mendapatkan total karyawan yang tidak berubah
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id) AS emp_total
FROM sales
ORDER BY emp_id, sale_date;

-- Dengan ORDER BY: running total per karyawan dari awal sampai baris saat ini
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY emp_id, sale_date;

Bayangkan kamu ingin running total penjualan per karyawan, diurutkan berdasarkan tanggal. (Jika query kamu jalan dengan benar, penjelasan akan muncul.)

① Pilih emp_id, sale_date, dan amount dari sales.

② Hitung SUM(amount) pada window yang dipartisi per karyawan dan diurutkan berdasarkan tanggal, dan tambahkan sebagai kolom ke-4 dengan alias running_total.

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

Frame — baris mana yang sebenarnya diagregasi di dalam window

Ketika kamu menambahkan ORDER BY ke window function, rentang baris yang benar-benar diagregasi oleh tiap baris disebut frame.

Jika kamu tidak menulis frame secara eksplisit, defaultnya adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (dari awal window sampai baris saat ini).

Itulah tepatnya mengapa menambahkan ORDER BY di latihan sebelumnya memberi kamu running total — frame default ini yang bekerja.

-- A: frame tidak ditulis (default = RANGE UNBOUNDED PRECEDING ... CURRENT ROW)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS run_default
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

-- B: frame default ditulis eksplisit (hasil sama dengan A)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_explicit
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

Frame dibangun dari 5 kata kunci ini, dipakai untuk mengatakan "dari mana" dan "sampai mana" relatif terhadap baris saat ini.

Mari konfirmasi perilaku running total dengan menulis frame secara eksplisit.

① Pilih emp_id, sale_date, dan amount dari sales.

② Pada window yang dipartisi per karyawan dan diurutkan berdasarkan sale_date, hitung SUM(amount) dengan frame eksplisit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, dan tambahkan sebagai kolom ke-4 dengan alias running_total.

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

ROWS BETWEEN n PRECEDING — moving sum dan moving average

Set frame ke ROWS BETWEEN n PRECEDING AND CURRENT ROW dan agregasi menyempit menjadi "n baris terakhir + baris saat ini".

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mencakup 3 baris terbaru (termasuk baris saat ini), jadi SUM menjadi moving sum dan AVG menjadi moving average.

Ini adalah bentuk roti-dan-mentega untuk memuluskan tren terbaru.

Dekat awal window, frame memiliki lebih sedikit baris untuk diambil (baris 1 memiliki 1 baris, baris 2 memiliki 2 baris, mulai baris 3 dan seterusnya memiliki 3 baris).

Diagram di bawah menelusuri 5 penjualan emp_id=14 baris demi baris, menunjukkan apa yang berakhir di frame pada setiap "baris saat ini".

-- Moving average dari 3 penjualan terbaru (2 baris sebelumnya + saat ini)
SELECT emp_id, sale_date, amount,
  AVG(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg3
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

-- Moving sum dari 2 penjualan terbaru (1 baris sebelumnya + saat ini) memakai bentuk yang sama
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_sum2
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

Bayangkan kamu ingin moving sum per karyawan atas 3 penjualan terbaru (2 terakhir + saat ini), diurutkan berdasarkan tanggal.

① Pilih emp_id, sale_date, dan amount dari sales.

② Pada window yang dipartisi per karyawan dan diurutkan berdasarkan sale_date, hitung SUM(amount) dengan frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, dan tambahkan sebagai kolom ke-4 dengan alias moving_sum3.

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

ROWS vs. RANGE — bagaimana baris yang nilainya sama ditangani

Frame hadir dalam dua mode hitung: ROWS dan RANGE.

ROWS menghitung baris fisik ("2 baris sebelumnya" berarti tepat 2 baris). Bahkan dengan nilai yang sama, setiap baris dihitung terpisah, jadi running total tumbuh baris demi baris.

RANGE mengelompokkan baris dengan nilai ORDER BY yang sama menjadi satu bucket. Baris dengan nilai sama berbagi frame dan berakhir dengan running total yang sama.

Dua diagram di bawah menunjukkan bagaimana ROWS dan RANGE masing-masing menangani 3 baris di mana amount = 100000.

-- ORDER BY amount memiliki 3 baris yang nilainya sama di 100000, jadi ROWS dan RANGE berbeda
SELECT sale_id, amount,
  SUM(amount) OVER (ORDER BY amount ROWS  UNBOUNDED PRECEDING) AS rows_run,
  SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_run
FROM sales
WHERE amount = 100000
ORDER BY sale_id;
-- ROWS menghitung satu baris pada satu waktu; RANGE mengelompokkan 3 baris 100000 yang sama menjadi satu frame

Bayangkan kamu ingin melihat, dalam satu hasil, bagaimana running total berubah antara ROWS dan RANGE ketika beberapa baris berbagi nilai yang sama. Ada 3 penjualan di mana amount = 100000.

① Pilih sale_id dan amount dari sales.

② Pada window yang diurutkan berdasarkan amount, tambahkan SUM(amount) dengan frame ROWS UNBOUNDED PRECEDING dengan alias rows_run, dan SUM(amount) lain dengan frame RANGE UNBOUNDED PRECEDING dengan alias range_run.

③ Saring hanya ke baris di mana amount = 100000.

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