Window Functions — SQL

Window Functions adalah fungsi yang menghitung nilai berdasarkan "jendela" baris — beda dari aggregate function yang me-collapse banyak baris jadi satu. Window

Window Functions adalah fungsi yang menghitung nilai berdasarkan "jendela" baris — beda dari aggregate function yang me-collapse banyak baris jadi satu. Window function menjaga setiap baris tetap ada, tapi menambahkan hasil kalkulasi di sebelahnya.

Aggregate vs Window — Perbedaan Krusial

-- Aggregate: hasil 1 baris per group (me-collapse data)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: hasil semua baris + kolom AVG per department (tidak collapse)
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Di query kedua, kamu dapat setiap karyawan ditambah rata-rata gaji departemennya. Tidak perlu JOIN manual.

Sintaks Dasar

function_name() OVER (
    [PARTITION BY kolom]    -- bagi data jadi kelompok (opsional)
    [ORDER BY kolom]        -- urutkan dalam kelompok (opsional)
    [frame_clause]          -- rentang baris (opsional, advanced)
)

Ranking Functions

Paling sering dipakai untuk leaderboard, top-N per group, deduplication.

SELECT name, department, salary,
       ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

Perbedaan 3 fungsi ranking:

Salary ROW_NUMBER RANK DENSE_RANK
10jt 1 1 1
8jt 2 2 2
8jt 3 2 2
7jt 4 4 3

LAG & LEAD — Bandingkan Baris Tetangga

LAG akses baris sebelumnya, LEAD akses baris berikutnya — tanpa self-join.

-- Growth day-over-day: selisih penjualan hari ini vs kemarin
SELECT date, revenue,
       LAG(revenue, 1) OVER (ORDER BY date) AS revenue_yesterday,
       revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_growth
FROM daily_sales;

Argumen kedua LAG(kolom, 1) = lompat 1 baris ke belakang.

Running Totals dengan Frame Clause

Frame clause mendefinisikan jendela spesifik di dalam partition. Sangat powerful untuk cumulative calculations.

-- Running total pendapatan dari awal tahun
SELECT date, revenue,
       SUM(revenue) OVER (
           ORDER BY date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales;

-- Moving average 7 hari (hari ini + 6 hari sebelumnya)
SELECT date, revenue,
       AVG(revenue) OVER (
           ORDER BY date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_sales;

NTILE — Bagi Data ke N Bucket Sama Besar

Berguna untuk segmentasi (quartile, decile, percentile).

-- Bagi customer ke 4 quartile berdasarkan total spending
SELECT customer_id, total_spent,
       NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_summary;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

Use Case Praktis

1. Top 3 produk per kategori:

WITH ranked AS (
    SELECT name, category_id, sales,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

2. Persentase dari total:

SELECT department, salary,
       salary * 100.0 / SUM(salary) OVER () AS pct_of_total
FROM employees;

3. Deduplication (pertahankan baris terbaru per email):

WITH dedup AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
    FROM users
)
SELECT * FROM dedup WHERE rn = 1;

Dukungan Database

Tips

Yang akan kamu pelajari