Window function menghitung nilai untuk setiap baris dari sekumpulan baris yang berhubungan, yaitu window-nya, tanpa menggabungkan baris seperti yang dilakukan GROUP BY. Tulis sebagai function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY membagi baris menjadi kelompok dan ORDER BY menentukan urutan di dalam setiap kelompok.
Setiap baris tetap ada di hasil dan membawa total region-nya: 1000 untuk north, 500 untuk south. Porsi setiap sales lalu cukup satu ekspresi (ana punya 50 persen dari north). Agregat apa pun (sum, count, avg, min, max) menjadi window function saat kamu menambahkan OVER. OVER () yang kosong membuat seluruh hasil menjadi satu window.
Memberi nomor baris dengan ROW_NUMBER, RANK, dan DENSE_RANK
Ketiga fungsi peringkat ini hanya berbeda saat ada baris yang seri pada nilai ORDER BY:
ben dan cy sama-sama punya 85. row_number tetap memberi mereka 2 dan 3; siapa yang mendapat 2 tidak terdefinisi kecuali ORDER BY memecah nilai serinya. rank memberi keduanya 2 dan melompat ke 4 untuk dee. dense_rank memberi keduanya 2 dan berlanjut dengan 3.
| Fungsi | Nilai seri | Celah setelah nilai seri |
|---|---|---|
row_number() | nomor berbeda | tidak |
rank() | nomor sama | ya |
dense_rank() | nomor sama | tidak |
Mengambil N baris teratas per kelompok
Window function dievaluasi setelah WHERE, jadi kamu tidak bisa memfilternya secara langsung. Beri nomor baris di subquery atau CTE, lalu filter di luarnya:
Hasilnya dua sales terbaik di setiap region. Pakai rank() alih-alih row_number() untuk menyimpan setiap baris yang seri di peringkat kedua. Untuk baris pertama saja per kelompok, DISTINCT ON lebih singkat.
Menghitung total berjalan atau rata-rata bergerak
Dengan ORDER BY di dalam OVER, agregat berjalan di atas sebuah frame. Secara default frame dimulai dari baris pertama partisi sampai baris saat ini, yang menghasilkan total berjalan. Klausa ROWS BETWEEN menentukan frame yang lain, misalnya baris saat ini dan dua baris sebelumnya:
Total berjalan mencapai 130 di hari 5, dan rata-rata tiga hari di hari 5 adalah (30 + 20 + 50) / 3 = 33.3.
Frame default-nya adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Frame ini mencakup setiap baris yang seri dengan baris saat ini pada nilai ORDER BY, jadi dua baris dengan hari yang sama mendapat total berjalan yang sama. Tulis ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW untuk menambahkannya satu per satu.
Membandingkan dengan baris sebelumnya: LAG dan LEAD
lag(col) membaca nilai dari baris sebelum baris saat ini menurut urutan window, dan lead(col) dari baris sesudahnya. Keduanya mengembalikan NULL di luar batas kecuali kamu memberikan default sebagai argumen ketiga.
Hari 1 tidak punya baris sebelumnya, jadi previous dan change bernilai NULL. Klausa WINDOW mendefinisikan window sekali, dengan nama, untuk dipakai bersama oleh beberapa fungsi.
first_value berjalan dengan frame default, tapi last_value tidak: frame default berakhir di baris saat ini, jadi ia mengembalikan nilai baris saat ini. Tambahkan ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING supaya frame mencakup seluruh partisi. Fungsi khusus window lainnya adalah ntile(n), percent_rank(), cume_dist(), dan nth_value(); referensi window function PostgreSQL memuat semuanya.
Memfilter baris yang dihitung agregat
FILTER (WHERE ...) juga berlaku untuk agregat window, untuk total bersyarat di setiap baris:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Pertanyaan yang sering diajukan
Apa perbedaan GROUP BY dan PARTITION BY?
GROUP BY mengembalikan satu baris per kelompok. PARTITION BY di dalam OVER mempertahankan setiap baris dan memberi masing-masing nilai yang dihitung atas kelompoknya, jadi kamu bisa menampilkan jumlah sebuah baris di samping total kelompoknya.
Bisakah memakai window function di WHERE?
Tidak bisa. Window function berjalan setelah WHERE, GROUP BY, dan HAVING, jadi hanya boleh ada di daftar select dan ORDER BY. Hitung nilainya di subquery atau CTE, lalu filter di query luar.
Kenapa LAST_VALUE mengembalikan nilai baris saat ini?
Dengan ORDER BY di OVER, frame default berakhir di baris saat ini (dan baris yang seri dengannya), jadi baris terakhir frame adalah baris saat ini. Tambahkan ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING supaya frame mencakup seluruh partisi.
Apa perbedaan ROWS dan RANGE dalam sebuah frame?
ROWS menghitung baris fisik: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW adalah baris saat ini dan dua baris sebelumnya. RANGE bekerja pada nilai ORDER BY, jadi baris dengan nilai yang sama (peer) selalu masuk frame bersama-sama.