Window Function ① — OVER dan PARTITION BY

Data yang dipakai pada artikel ini — sales dan employee

Window function menghitung hal seperti total per grup, peringkat, dan running total baris demi baris, lalu menambahkan satu kolom ke hasil kamu.

Sementara GROUP BY meruntuhkan banyak baris menjadi satu baris ringkasan, window function membiarkan setiap baris detail tetap pada tempatnya dan hanya menambahkan nilai yang dihitung di sampingnya.

Sebelum masuk ke latihan, lihat dulu definisi kolom dan data contoh dari dua tabel yang akan kita pakai — sales dan employee.

① Jalankan PRAGMA table_info(sales); dan PRAGMA table_info(employee); untuk mengecek definisi kolom kedua tabel.

② Jalankan SELECT * FROM sales LIMIT 5; dan SELECT * FROM employee LIMIT 5; untuk melihat 5 baris pertama masing-masing. Perhatikan bahwa kolom dept_id di employee bernilai NULL untuk beberapa karyawan.

OVER () — hitung di seluruh baris dan tambahkan kolom

Mari mulai dengan bentuk paling sederhana: aggregate(column) OVER ().

Dengan tanda kurung kosong, window mencakup seluruh result set (setiap baris yang terpilih).

Menambahkan SUM(amount) OVER () sebagai kolom ke-4 menghasilkan kolom baru grand_total di mana setiap baris memiliki nilai yang sama (total keseluruhan).

Jumlah baris tidak berubah — kamu hanya mendapat satu kolom tambahan.

-- OVER () dengan tanda kurung kosong mencakup seluruh result set
-- Semua 5 baris detail tetap ada, dan grand_total membawa nilai yang sama di setiap baris
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

Bayangkan kamu ingin daftar penjualan individual, dengan total keseluruhan dan porsi tiap penjualan terhadap total tersebut ditampilkan di sebelahnya. (Jika query kamu jalan dengan benar, penjelasan akan muncul.)

① Pilih sale_id, emp_id, dan amount dari sales.

② Tambahkan SUM(amount) OVER () sebagai kolom ke-4 dengan alias grand_total. Setiap baris akan membawa total keseluruhan yang sama.

③ Tambahkan *amount 100.0 / SUM(amount) OVER ()** sebagai kolom ke-5 dengan alias pct_of_total — porsi tiap baris terhadap total keseluruhan.

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

Membandingkan dengan GROUP BY — runtuh atau pertahankan baris

Menjajarkan GROUP BY dan window function membuat perbedaannya jelas.

Dengan GROUP BY emp_id + SUM(amount), kamu meruntuhkan baris setiap karyawan menjadi satu baris yang hanya mengembalikan total.

Dengan SUM(amount) OVER (), setiap baris detail tetap, dan kamu hanya mendapat satu kolom tambahan dengan nilai agregat.

Pilih salah satu berdasarkan kebutuhan kamu: hanya totalnya, atau total dan detail dalam satu tabel.

-- GROUP BY: runtuh menjadi 1 baris per karyawan — detail hilang
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- hasil: 1 baris (emp_id=2, cnt=5, total=2150000)

-- Window function: pertahankan semua 5 baris detail, dengan total keseluruhan di sampingnya
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- hasil: 5 baris, grand_total = 2150000 di setiap baris

Tulis komputasi "berikan saya totalnya" yang sama dengan dua cara — sekali dengan GROUP BY dan sekali dengan OVER () — lalu bandingkan hasilnya berdampingan.

① Mulai dengan versi GROUP BY: saring sales ke emp_id = 2, lalu pilih emp_id, COUNT(*) AS cnt, dan SUM(amount) AS total, dan agregasikan dengan GROUP BY emp_id. Lihat bagaimana ia runtuh menjadi 1 baris.

② Lalu tulis versi window function: pertahankan baris detail emp_id = 2 dan tambahkan SUM(amount) OVER () AS grand_total. Pilih sale_id, emp_id, amount, grand_total. Kamu akan mendapat 5 baris, masing-masing dengan total yang sama di sampingnya.

③ Jalankan kedua query di konsol yang sama dan bandingkan "1 baris" vs. "5 baris" dengan matamu sendiri. Penilai melihat output window function, jadi akhiri ② dengan ORDER BY sale_id agar urutan tetap.

PARTITION BY — bagi window menjadi grup

Sementara OVER () memperlakukan seluruh hasil sebagai satu window (rentang baris untuk diagregasi), OVER (PARTITION BY column) membagi window berdasarkan nilai pada kolom tersebut.

Dengan PARTITION BY emp_id, window dibagi per karyawan, dan setiap baris mendapatkan total karyawannya sendiri di sampingnya.

Pada diagram di bawah, keempat baris untuk emp_id=1 mendapatkan tambahan "total emp_id=1 = 970.000" yang sama, dan kelima baris untuk emp_id=2 mendapatkan "total emp_id=2 = 2.150.000".

-- Tambahkan total, rata-rata, dan jumlah per karyawan ke setiap baris detail
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- Persentase terhadap total karyawan: berapa porsi penjualan ini dari total karyawan tersebut?
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

Bayangkan kamu ingin menampilkan setiap penjualan di samping total, rata-rata, dan jumlah penjualan karyawan tersebut.

① Pilih sale_id, emp_id, dan amount dari sales.

② Gunakan window yang dipartisi per karyawan, tambahkan tiga kolom dengan alias emp_total (sum), emp_avg (average), dan emp_count (count).

③ Penilai memeriksa urutan baris, jadi akhiri dengan ORDER BY emp_id, sale_id (naik berdasarkan emp_id, lalu naik berdasarkan sale_id dalam tiap karyawan).

Gabungkan dengan JOIN untuk mendapatkan agregat per departemen

Window function bekerja pada hasil JOIN juga.

JOIN-kan sales dengan employee untuk menarik dept_id (ID departemen), lalu PARTITION BY e.dept_id memungkinkan kamu menempelkan total penjualan untuk departemen karyawan tersebut ke setiap baris penjualan.

Kolom PARTITION BY bisa berasal dari tabel lain via JOIN.

Window function dievaluasi setelah FROM / JOIN / WHERE, setelah kumpulan baris siap — jadi alurnya adalah "JOIN untuk menyusun baris yang kamu butuhkan → distribusikan agregat window ke seluruh kumpulan baris tersebut".

-- JOIN sales dengan employee dan tambahkan total dan jumlah per departemen ke setiap detail
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

Bayangkan kamu ingin laporan yang mencantumkan setiap penjualan dengan nama dan departemen karyawan, plus total penjualan untuk departemen tersebut di sampingnya.

① INNER JOIN sales (alias s) dengan employee (alias e) pada s.emp_id = e.emp_id.

② Pilih s.sale_id, e.name, e.dept_id, dan s.amount.

③ Dengan window yang dipartisi berdasarkan departemen (PARTITION BY e.dept_id), tambahkan dept_total sebagai kolom ke-5 yang menampilkan total penjualan departemen.

④ Penilai memeriksa urutan baris, jadi akhiri dengan ORDER BY e.dept_id, s.sale_id (naik berdasarkan dept_id, lalu naik berdasarkan sale_id dalam tiap departemen).