Belajar SQL PostgreSQL - Window Functions (Analitis Data Lanjutan)

#Pendahuluan

Selamat datang di episode 9 series Belajar SQL PostgreSQL! Kita sudah belajar agregasi dengan GROUP BY di episode 5, yang menggabungkan banyak baris menjadi satu ringkasan. Tapi bagaimana kalau kita ingin menghitung sesuatu per kelompok sambil tetap mempertahankan setiap baris aslinya? Misalnya: beri peringkat pada setiap produk di dalam kategorinya, atau hitung selisih penjualan antar bulan tanpa kehilangan detail per bulan. Jawabannya adalah window functions.

Di episode ini, kita akan membahas perbedaan konseptual agregasi GROUP BY dengan window functions, anatomi clause OVER() dengan PARTITION BY dan window framing, kategori fungsi ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), serta kategori fungsi value (LAG, LEAD, FIRST_VALUE, LAST_VALUE).

#Konsep Window Functions

#GROUP BY vs Window Functions

Perbedaan mendasar keduanya terletak pada jumlah baris hasil:

• GROUP BY: menggabungkan baris dalam kelompok menjadi SATU baris ringkasan. Jumlah baris berkurang.
• Window function: menghitung nilai untuk setiap baris, mempertimbangkan baris-baris di sekitarnya ("window"), tanpa menggabungkan — jumlah baris tetap sama.

SELECT
    product,
    category,
    sales,
    SUM(sales) OVER (PARTITION BY category) AS total_per_kategori
FROM sales;

Perhatikan query kedua: setiap baris tetap muncul, dan kolom tambahan total_per_kategori berisi total untuk kategori baris tersebut. Window function "melihat ke samping" ke baris-baris dalam kelompoknya — inilah kenapa disebut window (jendela).

#Anatomi Clause OVER()

Setiap window function wajib diikuti clause OVER() yang mendefinisikan jendela:

OVER (
    PARTITION BY kolom_pengelompokan
    ORDER BY kolom_pengurutan
    window_frame
)

• PARTITION BY: membagi hasil menjadi kelompok-kelompok. Mirip GROUP BY, tapi baris tidak digabung. Jika dihilangkan, seluruh hasil dianggap satu kelompok besar.
• ORDER BY: menentukan urutan baris di dalam setiap kelompok — penting untuk fungsi ranking dan LAG/LEAD.
• Window frame: menentukan rentang baris yang dilihat, opsional (default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW saat ada ORDER BY).

SELECT
    product,
    category,
    sales,
    ROW_NUMBER() OVER (
        PARTITION BY category
        ORDER BY sales DESC
    ) AS peringkat
FROM sales;

#Window Framing: ROWS BETWEEN

Window frame mengontrol rentang baris yang dihitung oleh fungsi. Sintaks yang paling umum:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Artinya: dari baris pertama partisi sampai baris saat ini — menghasilkan running total (jumlah berjalan):

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulatif
FROM monthly_sales
ORDER BY month;

Variasi frame lain yang berguna:

• ROWS BETWEEN 3 PRECEDING AND CURRENT ROW: rata-rata bergerak 4 bulan.
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: total dari baris ini sampai akhir.

#Kategori Window Functions: Ranking

Empat fungsi ranking bekerja dengan nuansa yang berbeda pada nilai yang sama (tie):

SELECT
    product,
    category,
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dense_rank,
    NTILE(4) OVER (PARTITION BY category ORDER BY sales DESC) AS kuartil
FROM sales;

#Pola Praktis: Top-N per Kelompok

Salah satu pola window function paling berguna di produksi adalah top-N per kelompok — misal 3 produk terlaris per kategori:

SELECT product, category, sales
FROM (
    SELECT
        product,
        category,
        sales,
        ROW_NUMBER() OVER (
            PARTITION BY category ORDER BY sales DESC
        ) AS peringkat
    FROM sales
) AS berperingkat
WHERE peringkat <= 3;

#Kategori Window Functions: Value

#LAG dan LEAD: Membandingkan dengan Baris Tetangga

SELECT
    month,
    sales,
    LAG(sales) OVER (ORDER BY month) AS sales_bulan_lalu,
    sales - LAG(sales) OVER (ORDER BY month) AS selisih
FROM monthly_sales
ORDER BY month;

#FIRST_VALUE dan LAST_VALUE

SELECT
    product,
    sales,
    FIRST_VALUE(sales) OVER (
        PARTITION BY category ORDER BY sales DESC
    ) AS termahal_di_kategori,
    FIRST_VALUE(sales) OVER (
        PARTITION BY category ORDER BY sales DESC
    ) - sales AS gap_dari_tertinggi
FROM sales;

#Penutup

Inti yang harus dibawa pulang:

• Window functions tidak menggabungkan baris — jumlah baris tetap, ditambah kolom hasil analisis.
• PARTITION BY membagi kelompok; ORDER BY dalam OVER() mengatur urutan.
• Window frame mengontrol rentang baris yang dihitung — kunci untuk running total dan moving average.
• RANK vs DENSE_RANK vs ROW_NUMBER dibedakan oleh perlakuan terhadap nilai tie.
• LAG dan LEAD adalah alat utama analisis tren antar baris.

Di episode 10 selanjutnya, kita akan mempelajari struktur query yang paling dibanggakan banyak developer: Common Table Expressions (CTE) & Recursive Queries — mulai dari non-recursive CTE dengan clause WITH untuk query yang rapi dan modular, hingga WITH RECURSIVE untuk mengolah data hierarkis seperti struktur organisasi dan kategori bertingkat.