Fungsi SQL Window pada Wawancara Ilmu Data yang Ditanyakan Oleh Airbnb, Netflix, Twitter, dan Uber

Fungsi jendela adalah sekelompok fungsi yang akan melakukan penghitungan di seluruh rangkaian baris yang terkait dengan baris Anda saat ini. Mereka dianggap sebagai 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 membahas mengapa dan kapan Anda akan menggunakannya.

4 Jenis Fungsi Jendela

1. Fungsi agregat tetap

o Ini adalah agregat seperti AVG, MIN/MAX, COUNT, SUM

o Anda perlu menggunakannya untuk menggabungkan data Anda dan mengelompokkannya menurut kolom lain seperti bulan atau tahun

2. Fungsi penilaian

atau ROW_NUMBER, RANK, RANK_DENSE

o Ini adalah fungsi yang membantu Anda menentukan posisi data Anda. Anda dapat mengevaluasi seluruh kumpulan data atau menempatkannya berdasarkan grup seperti bulan atau negara

o Sangat berguna untuk menghasilkan indeks posisi dalam grup

3. Hasilkan statistik

o Ini bagus jika Anda perlu membuat statistik sederhana seperti NTILE (persentil, kuartil, median)

o Anda dapat menggunakan ini untuk seluruh kumpulan data Anda atau berdasarkan grup

4. Menangani data deret waktu

o Fungsi jendela yang sangat umum terutama jika Anda perlu menghitung tren seperti rata-rata bergulir dari bulan ke bulan atau metrik pertumbuhan

o LAG dan LEAD adalah dua fungsi yang memungkinkan Anda melakukan ini.

1. Fungsi agregat tetap

Fungsi agregat umum adalah fungsi seperti mean, count, sum, mean/max yang digunakan pada kolom. Tujuannya adalah untuk menggunakan fungsi agregat jika Anda ingin menggunakan agregat pada grup yang berbeda dalam kumpulan data, seperti bulan.

Ini mirip dengan jenis perhitungan yang dapat dilakukan dengan fungsi agregat yang akan Anda temukan di klausa SELECT, tetapi tidak seperti fungsi agregat biasa, fungsi jendela tidak mengelompokkan beberapa baris menjadi satu baris keluaran, mereka mengelompokkan bersama atau mempertahankan identitasnya sendiri. , tergantung bagaimana Anda menemukannya.

Rata-rata () Contoh:

Mari kita lihat contoh fungsi jendela rata-rata () yang diterapkan untuk menjawab pertanyaan analitik data. Anda dapat melihat pertanyaan dan menulis kode di tautan di bawah ini:

platform.stratascratch.com/coding-question?id=10302&python=

Ini adalah contoh sempurna menggunakan fungsi jendela dan kemudian menerapkan rata-rata () ke grup bulan. Di sini kami mencoba menghitung jarak rata-rata per dolar per bulan. Ini sulit dilakukan dalam SQL tanpa fungsionalitas jendela ini. Di sini kami telah menggunakan fungsi jendela rata-rata () pada kolom ke-3 di mana kami telah menemukan nilai rata-rata untuk tahun untuk setiap bulan 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 menjalankan fungsi jendela akan terlihat seperti ini:

PILIH tanggal_permintaan,

a.dist_to_cost,

AVG (a.dist_to_cost) OVER (PARTITION BY a.request_mnth) SEBAGAI avg_dist_to_cost

DARI

(PILIH *,

to_char (tanggal_permintaan :: tanggal, ‘YYYY-MM’) SEBAGAI request_mnth,

(distance_to_travel/finance_cost) US dist_to_cost

DARI uber_request_logs) a

PESANAN MENURUT date_request

2. Fungsi Posisi

Fungsi rating adalah utilitas penting bagi ilmuwan data. Anda terus-menerus mengevaluasi 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 () – tergantung pada kasus penggunaan Anda. Fungsi ini akan membantu Anda membuat daftar data Anda secara berurutan dan berkelompok berdasarkan apa yang Anda inginkan.

Contoh Peringkat ():

Mari kita lihat contoh fungsi jendela posisi untuk melihat bagaimana kita dapat mengatur data dalam kelompok 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 hanya akan memberi kita 3 gaji teratas di semua departemen, jadi kita perlu mengurutkan gaji berdasarkan departemen satu per satu. Hal ini dilakukan dengan peringkat () dan dibagi dengan departemen. Dari sana, sangat mudah untuk memfilter 3 teratas di semua departemen

Berikut adalah kode untuk menghapus tabel ini. Anda dapat menyalin dan menempel di editor SQL di tautan di atas dan melihat output yang sama.

PILIH posisi,

gaji,

PERINGKAT () AKHIR (DIVISI MENURUT a.departemen

ORDER BY a.salary DESC) SEBAGAI rank_id

DARI

(PILIH posisi, gaji

DARI twitter_karyawan

KELOMPOK MENURUT DEPARTEMEN, GAJI

PESANAN MENURUT DEPARTEMEN, GAJI) a

ORDER OLEH DEPARTEMEN,

gaji DESC

3. NTI

NTILE adalah fungsi yang sangat berguna bagi mereka yang melakukan 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 tugas harian Anda dan NTILE memudahkan untuk menghasilkan output ini.

NTILE mengambil argumen tentang jumlah tempat sampah (atau pada dasarnya berapa banyak keranjang yang Anda inginkan untuk membagi data Anda), dan kemudian membuat jumlah tempat sampah ini dengan membagi data Anda ke dalam jumlah tempat sampah tersebut. Anda menentukan bagaimana data diatur dan dipisahkan, jika Anda menginginkan grup tambahan.

NTILE (100) Contoh

Dalam contoh ini, kita akan belajar bagaimana 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=

Hal yang Anda coba lakukan di sini adalah mengidentifikasi 5 persen klaim teratas berdasarkan skor yang dirilis oleh algoritme. Tetapi Anda tidak dapat menemukan 5% teratas dan memesan berdasarkan karena Anda ingin menemukan 5% teratas menurut negara bagian. Jadi salah satu cara untuk melakukannya adalah dengan menggunakan fungsi peringkat NTILE () dan kemudian PARTITION berdasarkan status. Anda kemudian dapat menggunakan filter di klausa WHERE untuk mendapatkan 5% teratas.

Berikut adalah kode untuk menghapus seluruh tabel di atas. Anda dapat menyalin dan menempel di tautan di atas.

PILIH nomor_kebijakan,

negara,

biaya_klaim,

skor_penipuan,

persentil

DARI

(PILIH *,

NTILE (100) LEBIH (PARTISI MENURUT NEGARA

ORDER BY fraud_score DESC) SEBAGAI PERSENTILE

DARI Fraud_score) a

DIMANA persentil <= 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, seperti pengambilan sampel dari data sebelumnya atau data yang akan datang.

Anda dapat menggunakan LAG dan LEAD untuk menghitung pertumbuhan bulanan atau rata-rata bulat. Sebagai ilmuwan data dan analis bisnis, Anda terus-menerus berurusan dengan data deret waktu dan membuat metrik pada waktu itu.

LAG() Contoh:

Dalam contoh ini, kami ingin menemukan persentase pertumbuhan dari tahun ke tahun, yang merupakan pertanyaan yang sangat umum dijawab oleh ilmuwan data dan analis bisnis setiap hari. Pernyataan masalah, data, dan editor SQL ada di tautan berikut jika Anda ingin mencoba mengkodekan sendiri solusinya: platform.stratascratch.com/coding-question?id=9637&python=

Yang sulit dari masalah ini adalah bahwa data sudah disediakan – Anda harus menggunakan nilai baris sebelumnya dalam metrik Anda. Tetapi SQL tidak dibuat untuk melakukan itu. SQL dibangun untuk menghitung apa pun yang Anda inginkan selama nilainya berada di baris yang sama. Jadi kita dapat menggunakan fungsi jendela lag () atau lead () yang akan mengambil baris sebelumnya atau berikutnya dan meletakkannya di baris Anda saat ini yang dilakukan pertanyaan ini.

Berikut adalah kode untuk menghapus seluruh tabel di atas. Anda dapat menyalin dan menempelkan kode di editor SQL di tautan di atas:

PILIH tahun,

host_current_year,

host_year_the_year,

bulat (((host_current_year – host_previous_year)/(cast (host_previous_year US figure)))*100)

DARI

(PILIH tahun,

host_current_year,

LAG (current_year_host, 1) OVER (ORDER BY year) SEBAGAI prev_year_host

DARI

(Pilih ekstrak

DARI host_sejak :: tanggal) SEBAGAI tahun,

hitung (id) saat_tahun_host

DARI airbnb_search_details

DI MANA hos_sejak TIDAK NULL

KELOMPOK BERDASARKAN EKSTRAK (tahun

DARI host_sejak :: tanggal)

BISNIS BERDASARKAN TAHUN) t1) t2

Leave a comment

Your email address will not be published. Required fields are marked *