PostgreSQL Advanced: Memahami Window Functions dan CTEs

Daftar Isi

• Pendahuluan
• Kenapa SQL Dasar Belum Cukup?
• Apa Itu Window Functions?
• Contoh Window Function PostgreSQL yang Sering Dipakai
• Apa Itu CTE?
• Contoh CTE PostgreSQL agar Query Lebih Rapi
• Menggabungkan CTE dan Window Functions
• Pertimbangan Performa
• Kesalahan Umum
• Checklist
• FAQ
• Kesimpulan

Pendahuluan

PostgreSQL sering digunakan dengan syntax SQL yang sudah familiar: SELECT, WHERE, JOIN, GROUP BY, dan ORDER BY. Fitur ini cukup banyak digunakan untuk kebutuhan development sehari-hari, tetapi beberapa kebutuhan reporting dan analytics memerlukan query yang lebih ekspresif lagi.

Contohnya:

• Customer mana yang membuat order pertama di setiap region?
• Berapa running total revenue per hari?
• Bagaimana ranking setiap produk di dalam kategorinya?
• Event apa yang terjadi tepat sebelum failure?
• Bagaimana memecah query kompleks menjadi langkah yang mudah dibaca?

Window functions dan common table expressions, atau CTEs, adalah dua fitur PostgreSQL yang membantu menjawab pertanyaan seperti ini tanpa membuat SQL berubah menjadi “labirin”.

Dengan window functions, kamu bisa menghitung nilai dari baris yang saling terkait tanpa kehilangan detail tiap baris. CTEs membantu memecah query kompleks menjadi langkah-langkah yang lebih jelas. Saat dipakai bersama, keduanya membuat SQL tingkat lanjut lebih mudah ditulis, dibaca, dan dirawat.

Kenapa SQL Dasar Belum Cukup?

GROUP BY berguna saat kamu ingin satu hasil per grup. Misalnya total sales per customer:

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

Tapi kadang kamu butuh insight grup dan detail baris asli sekaligus. Misalnya kamu ingin setiap order, ditambah running total per customer dari waktu ke waktu. GROUP BY biasa akan menggabungkan baris, jadi tidak cukup.

Di sinilah window functions diperlukan. Fitur ini menghitung nilai dari sekumpulan baris yang terkait, tanpa menggabungkan baris-baris tersebut menjadi satu hasil.

CTE menyelesaikan masalah yang berbeda: keterbacaan query. Saat logika bisnis berkembang, nested subquery bisa sulit dipahami. CTE memungkinkan kamu memberi nama pada hasil sementara sehingga query terbaca seperti rangkaian langkah yang jelas.

Apa Itu Window Functions?

Window function menghitung nilai dengan melihat baris saat ini bersama baris lain yang masih terkait. Misalnya, baris lain dalam customer yang sama, kategori yang sama, atau urutan waktu yang sama.

Bentuk dasarnya seperti ini:

function_name() OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

Ada tiga bagian yang perlu diperhatikan:

• OVER memberi tahu PostgreSQL bahwa perhitungan ini memakai window function.
• PARTITION BY membagi baris menjadi grup, mirip cara kamu berpikir tentang “per customer” atau “per kategori”.
• ORDER BY menentukan urutan di dalam setiap grup, misalnya dari order paling lama ke paling baru.

Contoh:

SELECT
  customer_id,
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

Query ini tetap mengembalikan setiap baris order, lalu menambahkan kolom running_total untuk total berjalan per customer. Jadi kamu tidak kehilangan detail order, tetapi tetap bisa melihat akumulasi nilainya.

Contoh hasil:

customer_id	order_id	order_date	total_amount	running_total
C01	O101	2026-01-01	100	100
C01	O102	2026-01-05	75	175
C02	O201	2026-01-03	60	60
C02	O202	2026-01-08	140	200

Contoh Window Function PostgreSQL yang Sering Dipakai

Ranking baris

Gunakan ROW_NUMBER, RANK, atau DENSE_RANK saat kamu perlu memberi posisi pada baris berdasarkan urutan tertentu. Contohnya, produk dengan revenue tertinggi di setiap kategori.

SELECT
  product_id,
  category_id,
  revenue,
  RANK() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS revenue_rank
FROM product_revenue;

Query ini memberi ranking produk di setiap kategori, berdasarkan revenue terbesar ke terkecil.

Contoh hasil:

product_id	category_id	revenue	revenue_rank
P01	C10	500	1
P02	C10	500	1
P03	C10	300	3
P04	C20	900	1

Perbedaan tiga fungsi ranking ini penting saat ada nilai yang sama:

• ROW_NUMBER selalu memberi nomor unik, walaupun nilainya sama.
• RANK memberi ranking yang sama untuk nilai seri, lalu melewati ranking berikutnya.
• DENSE_RANK memberi ranking yang sama untuk nilai seri, tetapi tidak meninggalkan gap.

Membandingkan dengan baris sebelumnya

Gunakan LAG untuk mengambil nilai dari baris sebelumnya dan LEAD untuk mengambil nilai dari baris berikutnya. Ini berguna saat kamu ingin memahami urutan kejadian, bukan hanya nilai tiap baris secara terpisah.

SELECT
  user_id,
  event_time,
  event_type,
  LAG(event_type) OVER (
    PARTITION BY user_id
    ORDER BY event_time
  ) AS previous_event
FROM user_events;

Query ini menampilkan event saat ini sekaligus event sebelumnya untuk user yang sama. Pola seperti ini berguna untuk funnel, audit trail, dan debugging alur user.

Contoh hasil:

user_id	event_time	event_type	previous_event
U01	10:00	view_product	null
U01	10:03	add_to_cart	view_product
U01	10:05	checkout	add_to_cart

Running total

Running total adalah salah satu penggunaan window function yang paling praktis. Kamu bisa melihat nilai per baris sekaligus total akumulatif sampai baris tersebut.

SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
  ) AS running_revenue
FROM daily_orders;

Contoh hasil:

order_date	total_amount	running_revenue
2026-01-01	100	100
2026-01-02	80	180
2026-01-03	120	300

Moving average

Window frame memungkinkan kamu menentukan berapa banyak baris yang ikut dihitung. Pada contoh berikut, PostgreSQL menghitung rata-rata dari baris saat ini dan enam baris sebelumnya.

SELECT
  day,
  revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_average
FROM daily_revenue;

Query ini menghitung rolling average tujuh hari. Hasilnya sering dipakai untuk meratakan fluktuasi harian agar trend lebih mudah dibaca.

Contoh hasil:

day	revenue	seven_day_average
2026-01-07	140	121.43
2026-01-08	160	130.00
2026-01-09	155	137.86

Apa Itu CTE?

Common table expression, atau CTE, adalah hasil sementara yang diberi nama dan hanya berlaku di dalam satu query. CTE dimulai dengan WITH.

WITH paid_orders AS (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, COUNT(*) AS order_count
FROM paid_orders
GROUP BY customer_id;

CTE paid_orders membuat query lebih mudah dibaca. Daripada menaruh filter status = 'paid' di tengah query yang lebih besar, kamu memisahkannya sebagai satu langkah bernama.

Contoh hasil:

customer_id	order_count
C01	2
C02	1

CTE sangat membantu saat query punya beberapa tahap, misalnya:

• filter raw data
• agregasi hasil
• ranking baris
• memilih record final

Contoh CTE PostgreSQL agar Query Lebih Rapi

Misalnya kamu perlu mencari daftar customer teratas berdasarkan revenue dari order yang sudah dibayar.

Tanpa CTE, query seperti ini bisa cepat menjadi bertumpuk karena filtering, agregasi, dan sorting ditulis dalam satu blok besar. Dengan CTE, tiap langkah bisa dipisahkan:

WITH paid_orders AS (
  SELECT customer_id, total_amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT
    customer_id,
    SUM(total_amount) AS total_spent
  FROM paid_orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
ORDER BY total_spent DESC
LIMIT 10;

Contoh hasil:

customer_id	total_spent
C01	175
C02	140
C03	95

Setiap CTE sebaiknya mewakili satu ide. Pada contoh ini, paid_orders fokus pada filter order yang sudah dibayar, sedangkan customer_totals fokus menghitung total per customer. Pemisahan seperti ini membuat review dan debugging lebih mudah.

Nama CTE yang baik menjelaskan makna data, bukan sekadar mekaniknya:

• paid_orders
• monthly_revenue
• latest_customer_events
• ranked_products

Nama lemah seperti data1 atau temp membuat query kompleks lebih sulit dirawat, terutama saat query itu dibaca lagi beberapa minggu kemudian.

Menggabungkan CTE dan Window Functions

CTE dan window functions sangat berguna saat dipakai bersama. CTE membantu memecah langkah, sementara window function membantu menghitung nilai berbasis urutan atau grup.

Contoh pertama: mencari paid order pertama untuk setiap customer.

WITH paid_orders AS (
  SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
  FROM orders
  WHERE status = 'paid'
),
ranked_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS order_position
  FROM paid_orders
)
SELECT *
FROM ranked_orders
WHERE order_position = 1;

CTE pertama memfilter order yang sudah dibayar. CTE kedua memberi nomor urut order untuk setiap customer. Query final mengambil order dengan posisi pertama.

Contoh hasil:

order_id	customer_id	order_date	total_amount	order_position
O101	C01	2026-01-01	100	1
O201	C02	2026-01-03	60	1

Contoh lain: mencari tiga produk teratas di setiap kategori.

WITH product_totals AS (
  SELECT
    category_id,
    product_id,
    SUM(quantity * price) AS revenue
  FROM order_items
  GROUP BY category_id, product_id
),
ranked_products AS (
  SELECT
    *,
    DENSE_RANK() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC
    ) AS category_rank
  FROM product_totals
)
SELECT *
FROM ranked_products
WHERE category_rank <= 3;

Contoh hasil:

category_id	product_id	revenue	category_rank
C10	P01	500	1
C10	P02	500	1
C10	P03	300	2
C20	P04	900	1

Pola ini umum pada dashboard dan query reporting. Kamu menghitung nilai agregat dulu, memberi ranking di dalam tiap grup, lalu mengambil ranking yang dibutuhkan.

Pertimbangan Performa

SQL tingkat lanjut bisa rapi dibaca, tetapi tetap bisa mahal dijalankan. Query yang terasa cepat di dataset kecil belum tentu aman di data production. Selalu test query penting dengan data yang realistis.

Gunakan:

EXPLAIN ANALYZE

untuk melihat bagaimana PostgreSQL mengeksekusi query: apakah memakai index, melakukan sort besar, atau membaca terlalu banyak baris.

Perhatikan:

• sort besar akibat ORDER BY di window function
• index yang belum ada pada kolom filter, join, atau urutan
• CTE yang memproses terlalu banyak baris sebelum difilter
• scan berulang pada table besar
• join mahal yang dilakukan sebelum data diperkecil

Index yang sering membantu mencakup:

• kolom yang dipakai di WHERE
• kolom yang dipakai untuk join
• kolom yang dipakai di PARTITION BY dan ORDER BY, tergantung bentuk query

Jangan menambah index secara membabi buta. Index bisa mempercepat query read tertentu, tetapi juga menambah storage dan membuat proses write lebih berat. Gunakan query plan untuk memutuskan index mana yang benar-benar perlu.

Kesalahan Umum

Memakai window function saat GROUP BY cukup

Jika hanya butuh satu baris per grup, GROUP BY biasanya lebih sederhana. Gunakan window function saat kamu tetap butuh detail tiap baris.

Lupa ORDER BY

Beberapa window function bergantung pada urutan. Tanpa ORDER BY yang jelas, hasilnya bisa tidak stabil atau tidak sesuai maksud, terutama untuk ranking, running total, LAG, dan LEAD.

Membuat rantai CTE terlalu panjang

CTE meningkatkan keterbacaan, tetapi terlalu banyak CTE juga bisa membuat query sulit diikuti. Jika satu query mulai terasa seperti pipeline panjang, pertimbangkan view, materialized view, atau simplifikasi di level aplikasi.

Ranking sebelum filtering

Filter lebih awal jika memungkinkan. Memberi ranking pada baris yang sebenarnya tidak diperlukan akan membuat PostgreSQL bekerja lebih keras.

Mengabaikan ties

Pilih ROW_NUMBER, RANK, atau DENSE_RANK secara sengaja. Nilai seri bisa mengubah hasil bisnis, misalnya saat menentukan “top 3” produk atau kandidat.

Checklist

• Gunakan GROUP BY saat butuh satu baris per grup.
• Gunakan window functions saat butuh detail baris plus kalkulasi berbasis grup.
• Definisikan PARTITION BY dan ORDER BY dengan jelas.
• Gunakan CTE untuk memberi nama pada langkah query.
• Beri nama CTE secara deskriptif.
• Filter lebih awal jika memungkinkan.
• Cek perilaku ranking saat ada nilai seri.
• Gunakan EXPLAIN ANALYZE untuk query penting.
• Test dengan volume data yang mirip production.
• Tambahkan index berdasarkan query plan, bukan tebakan.

FAQ

Apakah CTE sama dengan temporary table?

Tidak. CTE adalah bagian dari satu query dan hilang setelah query selesai. Temporary table dibuat terpisah dan bisa dipakai ulang selama session masih aktif.

Apakah window functions lebih lambat daripada GROUP BY?

Tidak selalu. Keduanya menyelesaikan masalah berbeda. Window functions mungkin membutuhkan sorting dan bisa mahal pada dataset besar, jadi ukur dengan query plan nyata sebelum menyimpulkan.

Apakah window function bisa dipakai di WHERE?

Tidak langsung pada query level yang sama. Gunakan subquery atau CTE, lalu filter hasil window function di query luar.

Apakah setiap query kompleks harus memakai CTE?

Tidak. Gunakan CTE saat membantu keterbacaan atau reuse di dalam query. Hindari menambah layer jika layer itu tidak membuat logika lebih jelas.

Cara terbaik belajar fitur ini apa?

Mulai dari dataset kecil. Tulis satu query dengan GROUP BY, lalu tulis ulang dengan window function. Bandingkan hasilnya, lalu lihat query plan-nya agar kamu paham perbedaan cara PostgreSQL mengeksekusinya.

Kesimpulan

Window functions dan CTEs membuat PostgreSQL jauh lebih ekspresif. Window functions membantu membuat ranking, running total, perbandingan antarbaris, dan moving average tanpa kehilangan detail baris. CTEs membantu menyusun logika query menjadi langkah-langkah yang lebih mudah dibaca.

Saat dipakai bersama, keduanya menjadi alat yang kuat untuk reporting, analytics, debugging, dan product insight. Mulailah dari pertanyaan yang jelas, jaga setiap langkah query tetap punya tujuan, dan ukur performa sebelum memakai query di production.