Daftar Materi

SQL untuk Analisis Data Materi 04

Window Functions Dasar

Tujuan Pembelajaran

  • Membedakan window function dari agregasi GROUP BY.
  • Menggunakan ROW_NUMBER dan RANK dengan urutan deterministik.
  • Menghitung agregat per kelompok sambil mempertahankan detail baris.

Menghitung dalam jendela tanpa menggabungkan baris

Window function menambahkan hasil hitung pada setiap baris yang ada. Dalam contoh, PARTITION BY membagi pesanan menurut pelanggan. Di dalam setiap kelompok, ORDER BY menentukan urutan tanggal dan ID, lalu ROW_NUMBER memberi nomor 1, 2, dan seterusnya. Jumlah baris tetap sama karena query tidak merangkum detail menjadi satu baris per pelanggan.

Window function menghitung nilai atas sekumpulan baris terkait, tetapi tetap mengembalikan baris detail satu per satu. Berbeda dengan GROUP BY, ia tidak mereduksi semua anggota kelompok menjadi satu baris. Klausa OVER mendefinisikan jendela; PARTITION BY membaginya menjadi kelompok dan ORDER BY menentukan urutan di dalam kelompok.

SELECT id_pesanan, id_pelanggan, tanggal_pesanan,
       ROW_NUMBER() OVER (
           PARTITION BY id_pelanggan
           ORDER BY tanggal_pesanan, id_pesanan
       ) AS urutan_pesanan
FROM pesanan
ORDER BY id_pelanggan, urutan_pesanan;

Setiap pelanggan mendapat nomor mulai dari satu. ID pesanan menjadi tie-breaker saat tanggal sama agar urutan stabil.

Peringkat dan agregat window

PARTITION BY dapat dibayangkan sebagai memulai kembali hitungan untuk setiap kelompok. ORDER BY di dalam OVER menentukan baris mana yang mendapat nomor lebih dulu. ORDER BY di bagian akhir query mengatur tampilan seluruh hasil. Keduanya punya tugas berbeda, jadi menulis urutan luar tetap penting meskipun window sudah memiliki urutan.

RANK() memberi peringkat yang sama pada nilai seri dan melompati angka peringkat sesudahnya; DENSE_RANK() tidak melompati. Keduanya tersedia pada banyak DBMS modern. Contoh: RANK() OVER (ORDER BY omzet DESC) merangking omzet. Agregat juga bisa menjadi window function, misalnya SUM(omzet) OVER (PARTITION BY id_pelanggan) menambahkan total pelanggan pada setiap baris pesanan.

Untuk running total, tambahkan urutan dan frame eksplisit, misalnya ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ketersediaan dan detail window frame dapat bergantung versi DBMS. Hasil tetap perlu urutan akhir melalui ORDER BY luar; urutan dalam OVER tidak menjamin urutan tampilan.

Latihan Interaktif

Latihan 01

Buat CTE dengan nomor urut pesanan per pelanggan menggunakan ROW_NUMBER yang diurutkan menurut tanggal dan ID. Tampilkan riwayat pelanggan ID 1.

WITH riwayat AS (
    SELECT id_pelanggan, id_pesanan, tanggal_pesanan,
           ROW_NUMBER() OVER (
               PARTITION BY id_pelanggan
               ORDER BY tanggal_pesanan, id_pesanan
           ) AS nomor_pesanan
    FROM pesanan
)
SELECT id_pelanggan, id_pesanan, nomor_pesanan
FROM riwayat
WHERE id_pelanggan = 1
ORDER BY nomor_pesanan;

Lihat Petunjuk

Nomor ulang per pelanggan memerlukan PARTITION BY. Tambahkan ID sebagai tie-breaker bila tanggal sama.

Lihat Pembahasan

Window function menambahkan nomor tanpa menggabungkan pesanan. RANK dan SUM juga dapat dipakai dengan OVER, misalnya untuk peringkat dan total berjalan.

Ringkasan

  • Window function mempertahankan detail baris, GROUP BY mereduksi per kelompok.
  • PARTITION BY mendefinisikan kelompok dan ORDER BY menentukan urutan window.
  • ROW_NUMBER, RANK, dan agregat window tersedia pada banyak DBMS modern; cek versi yang digunakan.
  • Tambahkan tie-breaker untuk nomor yang deterministik dan ORDER BY luar untuk tampilan.

Bacaan lebih lanjut