Lima Fungsi Jendela SQL Teratas Yang Harus Anda Ketahui Untuk Wawancara Ilmu Data

SQL adalah bahasa universal di dunia data dan merupakan keterampilan paling penting yang harus dikuasai sebagai profesional data.

Alasan SQL sangat penting adalah karena itu adalah keterampilan utama yang diperlukan selama fase perselisihan data. Banyak eksplorasi data, manipulasi data, pengembangan pipeline, dan pembuatan dasbor dilakukan melalui SQL.

Apa yang membedakan ilmuwan data yang hebat dari ilmuwan data yang baik adalah bahwa ilmuwan data yang hebat dapat memperdebatkan data sebanyak yang dimungkinkan oleh kemampuan SQL. Sebagian besar untuk dapat sepenuhnya menggunakan semua yang ditawarkan SQL adalah dengan mengetahui cara menggunakan fungsi jendela.

Dengan itu, mari selami!

1. Delta dengan LEAD() dan LAG()

 
LEAD() dan LAG() sebagian besar digunakan saat membandingkan satu periode waktu dengan periode waktu sebelumnya untuk metrik tertentu. Untuk memberikan beberapa contoh…

• Anda bisa mendapatkan delta antara penjualan setiap tahun dan penjualan tahun sebelumnya
• Anda bisa mendapatkan delta dalam jumlah pendaftaran/konversi/kunjungan situs web setiap bulan
• Anda dapat membandingkan churn pengguna setiap bulan

Contoh:

Kueri berikut menunjukkan bagaimana Anda dapat menanyakan persentase perubahan bulanan dalam biaya

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Jumlah Kumulatif dengan SUM() atau COUNT()

 
Menghitung total berjalan dapat dengan mudah dilakukan melalui fungsi windows yang dimulai dengan SUM() atau COUNT(). Ini adalah alat yang ampuh ketika Anda ingin menunjukkan pertumbuhan metrik tertentu dari waktu ke waktu. Lebih khusus lagi, ini berguna dalam keadaan berikut:

• Dapatkan total pendapatan dan biaya berjalan dari waktu ke waktu
• Dapatkan total waktu yang dihabiskan di aplikasi per pengguna
• Dapatkan total konversi berjalan dari waktu ke waktu

Contoh:

Contoh berikut menunjukkan bagaimana Anda dapat menyertakan kolom jumlah kumulatif dari biaya bulanan:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Rata-Rata Bergerak dengan AVG()

 
AVG() sangat kuat dalam fungsi windows karena memungkinkan Anda untuk menghitung moving averages lembur.

Rata-rata bergerak adalah cara sederhana, namun efektif, untuk memperkirakan nilai dalam jangka pendek. Mereka juga sangat berguna untuk menghaluskan kurva yang mudah berubah pada grafik. Umumnya, rata-rata bergerak digunakan untuk mengukur arah umum di mana segala sesuatunya bergerak.

Lebih spesifik…

• Mereka dapat digunakan untuk mendapatkan tren umum penjualan mingguan (apakah rata-rata naik dari waktu ke waktu?). Ini akan menunjukkan pertumbuhan sebagai sebuah perusahaan.
• Mereka juga dapat digunakan untuk mendapatkan tren umum konversi mingguan atau kunjungan situs web.

Contoh:

Kueri berikut adalah contoh untuk mendapatkan rata-rata pergerakan 10 hari untuk konversi.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() sangat berguna ketika Anda ingin mendapatkan catatan pertama atau terakhir. Misalnya, jika Anda memiliki tabel kapan anggota gym datang ke gym dan Anda ingin mendapatkan tanggal hari pertama mereka datang ke gym, Anda dapat PARTITION BY customer (nama/id) dan ORDER BY tanggal pembelian. Kemudian, untuk mendapatkan baris pertama, Anda cukup memfilter baris dengan rowNumber sama dengan satu.

Contoh:

Contoh ini menunjukkan bagaimana Anda dapat menggunakan ROW_NUMBER() untuk mendapatkan tanggal pertama saat setiap anggota (pengguna) berkunjung.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Untuk rekap, jika Anda perlu mendapatkan catatan pertama atau terakhir, ROW_NUMBER() adalah cara yang bagus untuk mencapainya.

5. Rekam Peringkat dengan DENSE_RANK()

 
DENSE_RANK() mirip dengan ROW_NUMBER() kecuali bahwa ia mengembalikan peringkat yang sama untuk nilai yang sama. Pemeringkatan padat cukup berguna dalam hal mengambil rekor teratas, misalnya:

• Jika Anda ingin menarik 10 acara Netflix yang paling banyak ditonton minggu ini
• Jika Anda ingin mendapatkan 100 pengguna teratas berdasarkan dolar yang dihabiskan
• Jika Anda ingin melihat perilaku 1000 pengguna yang paling tidak aktif

Contoh:

Jika Anda ingin memberi peringkat pelanggan teratas Anda berdasarkan total penjualan, DENSE_RANK() akan menjadi fungsi yang sesuai untuk digunakan.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Terima kasih sudah membaca!

 
Dan itu saja! Saya harap ini membantu Anda dalam persiapan wawancara Anda — Saya yakin jika Anda mengetahui 5 konsep ini dari dalam ke luar, Anda akan berhasil dalam hal sebagian besar masalah fungsi jendela SQL di luar sana.

Seperti biasa, saya berharap yang terbaik dalam upaya belajar Anda!

 
 
Terence Shin adalah penggemar data dengan 3+ tahun pengalaman dalam SQL dan 2+ tahun pengalaman dalam Python, dan seorang blogger di Towards Data Science dan KDnugget.

Original. Diposting ulang dengan izin.

Sumber: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html