Apa itu Fungsi Window dalam SQL

Fungsi window dalam SQL adalah jenis fungsi yang melakukan perhitungan pada sejumlah baris tabel yang somehow terkait dengan baris saat ini. Melalui fungsi window, setiap baris input dapat memiliki hasil yang unik, dan hal ini dapat digunakan untuk menyelesaikan masalah kompleks dengan relatif mudah. Fungsi window banyak digunakan dalam analisis data.

Tujuan Fungsi Window

Tujuan utama dari fungsi window adalah memberikan alat yang kuat bagi analis data untuk menyelesaikan masalah yang memerlukan perhitungan sesuatu pada sekelompok baris, yang mungkin dipartisi berdasarkan nilai tertentu, diurutkan berdasarkan nilai lain. Misalnya, Anda dapat menghitung rata-rata bergerak, jumlah kumulatif, atau menghitung total berjalan dari bidang tertentu.

Salah satu keunggulan utama dari fungsi window adalah kemampuannya untuk melakukan perhitungan pada baris-baris yang terkait dengan baris saat ini. Sebaliknya, dengan jenis operasi SQL lainnya, baris-baris diperlakukan secara independen.

Konsep Partisi dan Frame

Partisi

Partisi dalam SQL adalah pembagian total set baris menjadi set-set yang lebih kecil. Klausa PARTITION BY bertanggung jawab atas operasi ini dalam fungsi window.

Mari kita pertimbangkan tabel sales yang berisi data penjualan suatu perusahaan:

Jika kita mempartisi tabel ini berdasarkan kolom Region:

SELECT Region, Sales,
SUM(Sales) OVER (PARTITION BY Region) AS total_sales
FROM sales;

Hasil set dibagi menjadi dua partisi: East dan West, dan jumlah penjualan dihitung untuk setiap partisi.

Frame

Frame adalah subset dari partisi saat ini dan dapat dianggap sebagai "jendela geser" yang bergerak di atas partisi dan mendefinisikan set baris saat ini untuk menghitung fungsi window. Frame didefinisikan oleh klausa ROWS dalam fungsi window.

Untuk memahami frame dengan lebih baik, mari lanjutkan dengan tabel sales. Misalkan kita ingin menghitung total penjualan berjalan di setiap region:

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

Frame, atau "jendela", dimulai dari baris pertama setiap partisi (UNBOUNDED PRECEDING) dan berakhir pada baris saat ini (CURRENT ROW), efektif menciptakan total berjalan.

Struktur Fungsi Window

Klausa OVER

Klausa OVER mendefinisikan jendela atau set baris di mana fungsi window akan diterapkan. Klausa ini digunakan bersama dengan fungsi window. Tanpa klausa OVER, fungsi window tidak dapat berfungsi. Jendela didefinisikan oleh klausa ORDER BY dan klausa PARTITION BY.

Misalnya, kita mungkin ingin menemukan total gaji setiap departemen di suatu perusahaan. Kueri SQL dengan klausa OVER bisa seperti ini:

SELECT department, salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

Ini akan mengembalikan hasil di mana setiap baris berisi departemen, gaji individu, dan total gaji untuk departemen tersebut.

Klausa PARTITION BY

Klausa PARTITION BY membagi set hasil menjadi partisi (atau kelompok). Fungsi window diterapkan pada setiap partisi secara terpisah dan perhitungan diulang untuk setiap partisi.

Misalnya, jika Anda ingin menghitung gaji tertinggi di setiap departemen, Anda dapat menulis:

SELECT department, salary,
MAX(salary) OVER (PARTITION BY department) AS highest_salary
FROM employees;

Ini akan mengembalikan tabel di mana setiap baris berisi departemen, gaji seorang karyawan, dan gaji tertinggi dalam departemen tersebut.

Klausa ORDER BY

Klausa ORDER BY dalam klausa OVER mendefinisikan urutan baris dalam setiap partisi untuk fungsi window. Ini dapat mengubah hasil fungsi window.

Misalnya, untuk menemukan gaji kumulatif di setiap departemen, Anda dapat menggunakan:

SELECT department, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS cumulative_salary
FROM employees;

Ini akan menghasilkan tabel di mana setiap baris berisi departemen, gaji seorang karyawan, dan gaji kumulatif dalam departemen tersebut diurutkan berdasarkan gaji.

Klausa ROWS

Klausa ROWS dalam SQL mendefinisikan frame window dalam partisi berdasarkan offset fisik dari posisi baris input saat ini. Kata kunci yang berbeda membantu mendefinisikan frame window ini:

UNBOUNDED PRECEDING

Kata kunci ini menentukan bahwa frame window dimulai dari baris pertama partisi. Misalnya:

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

Dalam kasus ini, untuk setiap baris, running_total mencakup semua penjualan dari awal partisi hingga baris saat ini.

n PRECEDING

Ini menentukan bahwa frame window dimulai dari baris ke-n sebelum baris saat ini dalam partisi. Misalnya, jika n adalah 2:

SELECT Salesperson, Region, Sales,
AVG(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS average_sales
FROM sales;

Di sini, average_sales pada setiap baris adalah rata-rata baris saat ini dan dua baris sebelumnya dalam partisi yang sama.

CURRENT ROW

Kata kunci ini mewakili baris saat ini dalam partisi. Frame window dapat berakhir atau dimulai dari baris saat ini. Misalnya:

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total
FROM sales;

Di sini, running_total pada setiap baris adalah total penjualan dari baris saat ini hingga akhir partisi.

n FOLLOWING

Ini menentukan bahwa frame window berakhir pada baris ke-n setelah baris saat ini dalam partisi. Jika n adalah 1:

SELECT Salesperson, Region, Sales,
AVG(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS average_sales
FROM sales;

Di sini, average_sales pada setiap baris adalah rata-rata baris saat ini dan baris berikutnya dalam partisi yang sama.

UNBOUNDED FOLLOWING

Kata kunci ini menentukan bahwa frame window berakhir pada baris terakhir partisi. Misalnya:

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total
FROM sales;

Dalam hal ini, untuk setiap baris, running_total mencakup semua penjualan dari baris saat ini hingga akhir partisi.

Klausa RANGE BETWEEN

Klausa RANGE BETWEEN dalam fungsi window mendefinisikan frame baris dengan nilai ORDER BY yang sama dengan baris saat ini, dan baris-baris n PRECEDING dan n FOLLOWING dalam partisi. Ini beroperasi pada representasi logis, bukan fisik, dari data.

Untuk menggunakan klausa RANGE BETWEEN, Anda perlu memiliki klausa ORDER BY dalam fungsi window. Misalnya, anggap kita ingin menghitung total penjualan untuk setiap salesperson dan dua salesperson yang menjual tepat di bawah dan tepat di atas mereka:

SELECT Salesperson, Sales,
SUM(Sales) OVER (ORDER BY Sales RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sales_range_total
FROM sales;

Ini akan mengembalikan hasil di mana setiap baris berisi salesperson, penjualan mereka, dan sales_range_total yang mencakup penjualan mereka serta penjualan salesperson yang menjual tepat di bawah dan tepat di atas mereka.

Membandingkan Fungsi Window dengan GROUP BY

Fungsi window SQL dan klausa GROUP BY keduanya adalah alat yang digunakan untuk agregasi data. Namun, mereka memiliki tujuan yang berbeda dan berperilaku secara berbeda.

GROUP BY

Klausa GROUP BY digunakan dengan fungsi agregat seperti SUM, AVG, MAX, MIN, COUNT, dll., untuk mengelompokkan hasil set berdasarkan satu atau beberapa kolom. Ketika menggunakan GROUP BY, sistem database membagi baris yang dikembalikan dari pernyataan SELECT menjadi kelompok-kelompok. Untuk setiap kelompok, Anda dapat menerapkan fungsi agregat.

Misalnya, Anda dapat mengelompokkan tabel sales berdasarkan kolom Salesperson dan menghitung total penjualan untuk setiap salesperson:

SELECT Salesperson, SUM(Sales) as total_sales
FROM sales
GROUP BY Salesperson;

Ini akan mengembalikan hasil di mana setiap baris berisi salesperson dan total penjualan mereka.

Perbedaan antara Fungsi Window dan GROUP BY

Perbedaan mendasar antara GROUP BY dan fungsi window adalah bagaimana mereka menangani baris-baris dataset:

• Ketika Anda menggunakan GROUP BY, baris-baris dikelompokkan menjadi satu set baris output yang lebih kecil, kehilangan identitas individual mereka. Fungsi agregat kemudian diterapkan, mengembalikan satu baris output untuk setiap grup.
• Sebaliknya, dengan fungsi window, setiap baris mempertahankan identitasnya dan dapat memiliki hasil yang unik. Fungsi window melakukan perhitungan pada set baris yang terkait dengan baris saat ini.

Misalnya, jika Anda ingin menghitung total penjualan dan rata-rata penjualan untuk setiap salesperson. Menggunakan GROUP BY, Anda dapat menulis:

SELECT Salesperson, SUM(Sales) as total_sales, AVG(Sales) as average_sales
FROM sales
GROUP BY Salesperson;

Ini akan mengembalikan hasil di mana setiap baris berisi salesperson, total penjualan mereka, dan rata-rata penjualan mereka.

Tetapi bagaimana jika Anda ingin menyertakan total penjualan dan rata-rata penjualan ini di setiap baris tabel asli? Di sinilah fungsi window digunakan:

SELECT Salesperson, Sales,
SUM(Sales) OVER (PARTITION BY Salesperson) as total_sales,
AVG(Sales) OVER (PARTITION BY Salesperson) as average_sales
FROM sales;

Ini akan mengembalikan hasil di mana setiap baris berisi salesperson, penjualan mereka, total penjualan mereka, dan rata-rata penjualan mereka.