Fungsi jendela adalah sekelompok fungsi yang akan melakukan penghitungan pada sekumpulan baris yang terkait dengan baris Anda saat ini. Mereka dianggap sql tingkat lanjut dan sering ditanyakan selama wawancara ilmu data. Ini juga banyak digunakan di tempat kerja untuk memecahkan berbagai jenis masalah. Mari kita rangkum 4 jenis fungsi jendela yang berbeda dan bahas alasan dan waktu Anda menggunakannya.
4 Jenis Fungsi Jendela
1. Fungsi agregat reguler
o Ini adalah agregat seperti AVG, MIN/MAX, COUNT, SUM
o Anda dapat menggunakannya untuk mengumpulkan data dan mengelompokkannya berdasarkan kolom lain seperti bulan atau tahun
2. Fungsi pemeringkatan
o ROW_NUMBER, PERINGKAT, RANK_DENSE
o Ini adalah fungsi yang membantu Anda menentukan peringkat data Anda. Anda dapat memberi peringkat pada seluruh kumpulan data Anda atau memberi peringkat berdasarkan kelompok seperti bulan atau negara
o Sangat berguna untuk menghasilkan indeks peringkat dalam kelompok
3. Menghasilkan statistik
o Ini bagus jika Anda perlu membuat statistik sederhana seperti NTILE (persentil, kuartil, median)
o Anda dapat menggunakan ini untuk seluruh kumpulan data atau berdasarkan grup
4. Menangani data deret waktu
o Fungsi jendela yang sangat umum terutama jika Anda perlu menghitung tren seperti rata-rata pergerakan bulan ke bulan atau metrik pertumbuhan
o LAG dan LEAD adalah dua fungsi yang memungkinkan Anda melakukan hal ini.
1. Fungsi agregat reguler
Fungsi agregat reguler adalah fungsi seperti rata-rata, hitungan, jumlah, min/maks yang diterapkan pada kolom. Tujuannya adalah untuk menerapkan fungsi agregat jika Anda ingin menerapkan agregasi ke grup berbeda dalam kumpulan data, seperti bulan.
Ini mirip dengan jenis penghitungan yang dapat dilakukan dengan fungsi agregat yang Anda temukan di klausa SELECT, namun tidak seperti fungsi agregat biasa, fungsi jendela tidak mengelompokkan beberapa baris ke dalam satu baris keluaran, melainkan dikelompokkan menjadi satu atau mempertahankan identitas mereka sendiri, tergantung bagaimana Anda menemukannya.
Rata-rata() Contoh:
Mari kita lihat salah satu contoh fungsi jendela avg() yang diimplementasikan untuk menjawab pertanyaan analisis data. Anda dapat melihat pertanyaan dan menulis kode pada link di bawah ini:
platform.stratascratch.com/coding-question?id=10302&python=
Ini adalah contoh sempurna menggunakan fungsi jendela dan kemudian menerapkan avg() ke grup bulan. Di sini kami mencoba menghitung jarak rata-rata per dolar per bulan. Ini sulit dilakukan di SQL tanpa fungsi jendela ini. Di sini kita telah menerapkan fungsi jendela avg() ke kolom ke-3 tempat kita menemukan nilai rata-rata untuk bulan-tahun untuk setiap bulan-tahun dalam kumpulan data. Kita dapat menggunakan metrik ini untuk menghitung perbedaan antara rata-rata bulan dan rata-rata tanggal untuk setiap tanggal permintaan dalam tabel.
Kode untuk mengimplementasikan fungsi jendela akan terlihat seperti ini:
PILIH a.tanggal_permintaan,
a.dist_to_cost,
AVG(a.dist_to_cost) LEBIH(PARTISI OLEH a.request_mnth) SEBAGAI avg_dist_to_cost
DARI
(PILIH *,
to_char(tanggal_permintaan::tanggal, 'YYYY-MM') SEBAGAI permintaan_mnth,
(jarak_ke_perjalanan/biaya_moneter) SEBAGAI dist_to_cost
DARI uber_request_logs) a
ORDER BERDASARKAN tanggal_permintaan
2. Fungsi Pemeringkatan
Fungsi pemeringkatan adalah utilitas penting bagi data scientist. Anda selalu memberi peringkat dan mengindeks data Anda untuk lebih memahami baris mana yang terbaik dalam kumpulan data Anda. Fungsi jendela SQL memberi Anda 3 utilitas peringkat — RANK(), DENSE_RANK(), ROW_NUMBER() — bergantung pada kasus penggunaan Anda. Fungsi-fungsi ini akan membantu Anda membuat daftar data Anda secara berurutan dan berkelompok berdasarkan keinginan Anda.
Peringkat() Contoh:
Mari kita lihat salah satu contoh fungsi jendela peringkat untuk melihat bagaimana kita dapat memberi peringkat data dalam grup menggunakan fungsi jendela SQL. Ikuti secara interaktif dengan tautan ini: platform.stratascratch.com/coding-question?id=9898&python=
Di sini kami ingin mencari gaji tertinggi berdasarkan departemen. Kita tidak bisa hanya menemukan 3 gaji teratas tanpa fungsi jendela karena ini hanya akan memberi kita 3 gaji teratas di semua departemen, jadi kita perlu mengurutkan gaji berdasarkan departemen satu per satu. Hal ini dilakukan oleh rank() dan dipartisi berdasarkan departemen. Dari sana, sangat mudah untuk memfilter 3 teratas di semua departemen
Berikut kode untuk menampilkan tabel ini. Anda dapat menyalin dan menempel di editor SQL pada tautan di atas dan melihat hasil yang sama.
PILIH departemen,
gaji,
RANK() OVER (PARTISI OLEH a.department
ORDER BY a.gaji DESC) AS rank_id
DARI
(PILIH departemen, gaji
DARI twitter_karyawan
KELOMPOK MENURUT departemen, gaji
ORDER BERDASARKAN departemen, gaji) a
ORDER OLEH departemen,
gaji DESC
3. NTILE
NTILE adalah fungsi yang sangat berguna bagi mereka yang berkecimpung dalam analisis data, analisis bisnis, dan ilmu data. Seringkali ketika tenggat waktu dengan data statistik, Anda mungkin perlu membuat statistik yang kuat seperti kuartil, kuintil, median, desil dalam pekerjaan sehari-hari Anda, dan NTILE memudahkan untuk menghasilkan keluaran ini.
NTILE mengambil argumen jumlah bin (atau pada dasarnya berapa banyak keranjang tempat Anda ingin membagi data Anda), lalu membuat jumlah bin ini dengan membagi data Anda ke dalam jumlah bin tersebut. Anda mengatur cara data diurutkan dan dipartisi, jika Anda menginginkan pengelompokan tambahan.
NTILE(100) Contoh
Dalam contoh ini, kita akan mempelajari cara menggunakan NTILE untuk mengkategorikan data kita ke dalam persentil. Anda dapat mengikuti secara interaktif di tautan di sini: platform.stratascratch.com/coding-question?id=10303&python=
Yang ingin Anda lakukan di sini adalah mengidentifikasi 5 persen klaim teratas berdasarkan skor yang dikeluarkan algoritme. Namun, Anda tidak dapat sekadar menemukan 5% teratas dan mengurutkannya karena Anda ingin menemukan 5% teratas menurut negara bagian. Jadi, salah satu cara untuk melakukannya adalah dengan menggunakan fungsi pemeringkatan NTILE(), lalu PARTITION menurut negara bagian. Anda kemudian dapat menerapkan filter dalam klausa WHERE untuk mendapatkan 5% teratas.
Berikut kode untuk menampilkan seluruh tabel di atas. Anda dapat menyalin dan menempelkannya pada tautan di atas.
PILIH nomor_kebijakan,
negara,
klaim_biaya,
skor_penipuan,
persentil
DARI
(PILIH *,
NTILE(100) OVER(PARTISI OLEH negara bagian
ORDER BY Fraud_score DESC) SEBAGAI persentil
DARI skor_penipuan) a
Persentil WHERE <=5
4. Menangani data deret waktu
LAG dan LEAD adalah dua fungsi jendela yang berguna untuk menangani data deret waktu. Satu-satunya perbedaan antara LAG dan LEAD adalah apakah Anda ingin mengambil dari baris sebelumnya atau baris berikutnya, hampir seperti pengambilan sampel dari data sebelumnya atau data mendatang.
Anda dapat menggunakan LAG dan LEAD untuk menghitung pertumbuhan bulan ke bulan atau rata-rata bergulir. Sebagai ilmuwan data dan analis bisnis, Anda selalu berurusan dengan data deret waktu dan membuat metrik waktu tersebut.
LAG() Contoh:
Dalam contoh ini, kami ingin mengetahui persentase pertumbuhan dari tahun ke tahun, yang merupakan pertanyaan umum yang dijawab oleh para ilmuwan data dan analis bisnis setiap hari. Pernyataan masalah, data, dan editor SQL ada di tautan berikut jika Anda ingin mencoba membuat kode solusinya sendiri: platform.stratascratch.com/coding-question?id=9637&python=
Yang sulit dari masalah ini adalah datanya sudah disiapkan — Anda perlu menggunakan nilai baris sebelumnya dalam metrik Anda. Tapi SQL tidak dibuat untuk melakukan itu. SQL dibuat untuk menghitung apa pun yang Anda inginkan selama nilainya berada di baris yang sama. Jadi kita bisa menggunakan fungsi jendela lag() atau lead() yang akan mengambil baris sebelumnya atau berikutnya dan meletakkannya di baris Anda saat ini, itulah yang dilakukan pertanyaan ini.
Berikut kode untuk menampilkan seluruh tabel di atas. Anda dapat menyalin dan menempelkan kode di editor SQL pada tautan di atas:
PILIH tahun,
host_tahun_saat ini,
host_tahun_sebelumnya,
round(((current_year_host – prev_year_host)/(cast(prev_year_host AS numerik)))*100) estimasi_pertumbuhan
DARI
(PILIH tahun,
host_tahun_saat ini,
LAG(host_tahun_saat ini, 1) LEBIH DARI (PESANAN BERDASARKAN tahun) SEBAGAI host_tahun_sebelumnya
DARI
(PILIH ekstrak(tahun
DARI host_since::tanggal) SEBAGAI tahun,
hitungan(id)host_tahun_saat ini
DARI airbnb_search_details
DIMANA host_since BUKAN NULL
KELOMPOK OLEH ekstrak (tahun
DARI host_sejak::tanggal)
ORDER BERDASARKAN tahun) t1) t2