Pengantar Fungsi Jendela SQL

Fitur yang sangat kuat yang Anda sukai untuk dibenci (tetapi perlu diketahui)

Fungsi jendela SQL menyediakan beberapa fitur yang sangat kuat dan berguna. Tetapi bagi banyak orang, karena mereka sangat asing dengan SQL standar, mereka sulit dipelajari dan dipahami, memiliki sintaks yang aneh—dan sangat sering dihindari.

Fungsi jendela dapat dijelaskan secara sederhana sebagai fungsi kalkulasi yang mirip dengan agregasi, tetapi jika agregasi normal melalui klausa GROUP BY digabungkan kemudian menyembunyikan baris individual yang diagregasi, fungsi jendela memiliki akses ke baris individual dan dapat menambahkan beberapa atribut dari baris tersebut ke dalam hasil yang ditetapkan.

Dalam tutorial fungsi jendela SQL ini, saya akan membantu Anda memulai dengan fungsi jendela, menjelaskan manfaat dan kapan Anda akan menggunakannya, dan memberi Anda contoh nyata untuk membantu konsep tersebut.

Jendela ke Data Anda

Salah satu fitur yang paling sering digunakan dan penting dalam SQL adalah kemampuan untuk menggabungkan atau mengelompokkan baris data dengan cara tertentu. Namun, dalam beberapa kasus, pengelompokan bisa menjadi sangat kompleks, tergantung pada apa yang diperlukan.

Pernahkah Anda ingin mengulang hasil kueri Anda untuk mendapatkan peringkat, daftar x teratas, atau serupa? Pernahkah Anda memiliki proyek analitik di mana Anda ingin menyiapkan data Anda tepat untuk alat visualisasi, tetapi merasa hampir tidak mungkin atau begitu rumit sehingga tidak sepadan?

Fungsi jendela dapat membuat segalanya lebih mudah. Setelah Anda mendapatkan hasil kueri Anda—yaitu, setelah klausa WHERE dan agregasi standar apa pun, fungsi jendela akan bertindak pada baris yang tersisa ( jendela data) dan memberikan apa yang Anda inginkan.

Beberapa fungsi jendela yang akan kita lihat meliputi:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Terlalu mudah

Klausa OVER adalah yang menentukan fungsi jendela dan harus selalu disertakan dalam pernyataan. Default dalam klausa OVER adalah seluruh rowset. Sebagai contoh, mari kita lihat tabel karyawan di database perusahaan dan tunjukkan jumlah total karyawan di setiap baris, beserta info setiap karyawan, termasuk saat mereka mulai bekerja di perusahaan.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Di atas, seperti banyak fungsi jendela, juga dapat ditulis dengan cara non-jendela yang lebih familiar—yang, dalam contoh sederhana ini, tidak terlalu buruk:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Tapi sekarang, katakanlah kita ingin menunjukkan jumlah karyawan yang memulai di bulan yang sama dengan karyawan di baris. Kita perlu mempersempit atau membatasi jumlah hanya pada bulan itu untuk setiap baris. Bagaimana itu dilakukan? Kami menggunakan klausa jendela PARTITION , seperti:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Partisi memungkinkan Anda untuk memfilter jendela menjadi beberapa bagian dengan nilai atau nilai tertentu. Setiap bagian sering disebut bingkai jendela.

Untuk melangkah lebih jauh, katakanlah kami tidak hanya ingin mengetahui berapa banyak karyawan yang memulai di bulan yang sama, tetapi kami ingin menunjukkan dalam urutan mana mereka memulai bulan itu. Untuk itu, kita dapat menggunakan klausa ORDER BY yang sudah dikenal. Namun, dalam fungsi jendela, ORDER BY bertindak sedikit berbeda dari yang dilakukannya di akhir kueri.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Dalam hal ini, ORDER BY memodifikasi jendela sehingga dimulai dari awal partisi (dalam hal ini bulan dan tahun saat karyawan memulai) ke baris saat ini. Dengan demikian, penghitungan dimulai ulang di setiap partisi.

Peringkat itu

Fungsi jendela bisa sangat berguna untuk tujuan peringkat. Sebelumnya kami melihat bahwa menggunakan fungsi COUNT agregasi memungkinkan kami untuk melihat dalam urutan apa Karyawan bergabung dengan perusahaan. Kita juga bisa menggunakan fungsi peringkat jendela, seperti ROW_NUMBER() , RANK() , dan DENSE_RANK() .

Perbedaannya bisa dilihat setelah kita menambah pegawai baru pada bulan berikutnya, dan menghapus partisi tersebut:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Anda dapat melihat perbedaannya. ROW_NUMBER() memberikan hitungan berurutan dalam partisi yang diberikan (tetapi dengan tidak adanya partisi, ia melewati semua baris). RANK() memberikan peringkat setiap baris berdasarkan klausa ORDER BY . Ini menunjukkan ikatan, dan kemudian melompati peringkat berikutnya. DENSE_RANK juga menunjukkan ikatan, tetapi kemudian berlanjut dengan nilai berurutan berikutnya seolah-olah tidak ada ikatan.

Fungsi peringkat lainnya termasuk:

• CUME_DIST – Menghitung peringkat relatif dari baris saat ini dalam sebuah partisi
• NTILE – Membagi baris untuk setiap partisi jendela secara merata
• PERCENT_RANK – Persen peringkat dari baris saat ini

Perhatikan juga dalam contoh ini bahwa Anda dapat memiliki beberapa fungsi Window dalam satu kueri—dan baik partisi maupun urutannya dapat berbeda di masing-masing!

Baris dan Rentang dan Bingkai, Oh My

Untuk lebih menentukan atau membatasi bingkai jendela Anda dalam klausa OVER() , Anda dapat menggunakan ROWS dan RANGE . Dengan klausa ROWS , Anda dapat menentukan baris yang disertakan dalam partisi Anda seperti baris sebelumnya atau setelah baris saat ini.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Dalam contoh ini, bingkai jendela beralih dari baris pertama ke baris saat ini dikurangi 1, dan ukuran jendela terus meningkat untuk setiap baris.

Rentang bekerja sedikit berbeda dan kami mungkin mendapatkan hasil yang berbeda.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Rentang akan menyertakan baris dalam bingkai jendela yang memiliki nilai ORDER BY yang sama dengan baris saat ini. Dengan demikian, Anda mungkin bisa mendapatkan duplikat dengan RANGE jika ORDER BY tidak unik.

Beberapa menggambarkan ROWS sebagai operator fisik sementara RANGE adalah operator logis. Bagaimanapun, nilai default untuk ROWS dan RANGE selalu UNBOUNDED PRECEDING AND CURRENT ROW .

Apa lagi?

Sebagian besar fungsi agregat standar bekerja dengan fungsi Window. Kami telah melihat COUNT dalam contoh. Lainnya termasuk SUM , AVG , MIN , MAX , dll.

Dengan fungsi jendela, Anda juga dapat mengakses catatan sebelumnya dan catatan berikutnya menggunakan LAG dan LEAD , dan FIRST_VALUE dan LAST_VALUE . Misalnya, katakanlah Anda ingin menunjukkan pada setiap baris angka penjualan untuk bulan ini, dan perbedaan antara angka penjualan bulan lalu. Anda mungkin melakukan sesuatu seperti ini:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Pada dasarnya, Fungsi SQL Window Sangat Kuat

Meskipun ini adalah pengantar singkat untuk fungsi jendela SQL, semoga ini akan memicu minat Anda untuk melihat semua yang dapat mereka lakukan. Kami mempelajari bahwa fungsi jendela melakukan perhitungan yang mirip dengan cara fungsi agregasi, tetapi dengan manfaat tambahan bahwa mereka memiliki akses ke data dalam baris individual, yang membuatnya cukup kuat. Mereka selalu berisi klausa OVER , dan mungkin berisi PARTITION BY , ORDER BY , dan sejumlah agregasi ( SUM , COUNT , dll.) dan fungsi posisi lainnya ( LEAD , LAG ). Kami juga belajar tentang bingkai jendela dan bagaimana mereka merangkum bagian data.

Perhatikan bahwa rasa SQL yang berbeda dapat mengimplementasikan fungsi jendela secara berbeda, dan beberapa mungkin tidak mengimplementasikan semua fungsi atau klausa jendela. Pastikan untuk memeriksa dokumentasi untuk platform yang Anda gunakan.

Jika, sebagai pengembang SQL, Anda tertarik untuk menyetel kinerja basis data SQL, lihat Penyetelan Kinerja Basis Data SQL untuk Pengembang .

Selamat berjendela!

Untuk informasi lebih lanjut tentang implementasi spesifik, lihat:

• Dokumentasi Fungsi Jendela PostgreSQL untuk implementasi PostgreSQL.
• SELECT - OVER Clause (Transact-SQL) dokumen oleh Microsoft.
• Fungsi Jendela di SQL Server untuk gambaran umum yang bagus tentang implementasi SQL Server, dan bagian 2.