Veri Bilimi Mülakatları İçin Bilmeniz Gereken İlk Beş SQL Window Fonksiyonu

SQL, veri dünyasında evrensel bir dildir ve bir veri uzmanı olarak elde edilmesi gereken en önemli beceridir.
SQL’in bu kadar önemli olmasının nedeni, veri tartışma aşamasında gerekli olan temel beceri olmasıdır. SQL üzerinden birçok veri keşfi, veri işleme, boru hattı geliştirme ve gösterge tablosu oluşturma yapılır.

Büyük veri bilimcilerini iyi veri bilimcilerinden ayıran şey, büyük veri bilimcilerinin SQL’in yeteneklerinin izin verdiği kadar verileri tartışabilmesidir. SQL’in sunduğu her şeyi tam olarak kullanabilmenin büyük bir kısmı, window fonksiyonlarının nasıl kullanılacağını bilmektir.

1) LEAD() ve LAG() ile Deltalar

LEAD() ve LAG() çoğunlukla, belirli bir metrik için bir zaman periyodunu önceki zaman periyoduyla karşılaştırırken kullanılır. Birkaç örnek vermek gerekirse…

• Her yılın satışları ile bir önceki yılın satışları arasındaki deltayı alabilirsiniz.
• Aydan aya kayıt/dönüşüm/web sitesi ziyareti sayısındaki deltayı alabilirsiniz.
• Kullanıcı kaybını aylık olarak karşılaştırabilirsiniz.

Örnek: Aşağıdaki sorgu, maliyetlerdeki aylık yüzde değişimini nasıl sorgulayabileceğinizi gösterir.

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) SUM() veya COUNT() ile Kümülatif Toplamlar

Çalışan toplamları hesaplamak, SUM() veya COUNT() ile başlayan bir Windows işlevi aracılığıyla basitçe yapılabilir. Bu, belirli bir metriğin zaman içindeki gelişimini göstermek istediğinizde güçlü bir araçtır. Daha spesifik olarak, aşağıdaki durumlarda yararlıdır:

• Zaman içinde geçerli bir toplam gelir ve maliyet elde edin
• Kullanıcı başına uygulamada harcanan toplam süreyi elde edin
• Zaman içinde değişen toplam dönüşüm elde edin

Örnek: Aşağıdaki örnek, aylık maliyetlerin kümülatif toplamı sütununu nasıl ekleyebileceğinizi gösterir:

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

3) AVG() ile Hareketli Ortalamalar

AVG(), zaman içinde hareketli ortalamaları hesaplamanıza izin verdiği için Windows fonksiyonlarında gerçekten güçlüdür.

Hareketli ortalamalar, kısa vadede değerleri tahmin etmenin basit ama etkili bir yoludur. Ayrıca, bir grafikteki uçucu eğrileri yumuşatmada son derece kullanışlıdırlar. Genel olarak, hareketli ortalamalar, şeylerin hareket ettiği genel yönü ölçmek için kullanılır.

Daha spesifik olarak…

• Haftalık satışların genel eğilimini elde etmek için kullanılabilirler (ortalama zamanla artıyor mu?). Bu, bir şirket olarak büyümeyi gösterir.
• Aynı şekilde, haftalık dönüşümlerin veya web sitesi ziyaretlerinin genel eğilimini elde etmek için de kullanılabilirler.

Örnek: Aşağıdaki sorgu, dönüşümler için 10 günlük hareketli ortalamanın alınmasına bir örnektir.

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

4) ROW_NUMBER()

ROW_NUMBER(), özellikle ilk veya son kaydı almak istediğinizde kullanışlıdır. Örneğin, spor salonu üyelerinin spor salonuna ne zaman geldiklerini gösteren bir tablonuz varsa ve spor salonuna geldikleri ilk günün tarihini almak istiyorsanız, müşteriye göre (isim/kimlik) BÖLÜM ve satın alma tarihine göre SİPARİŞ verebilirsiniz. Ardından, ilk satırı elde etmek için, rowNumber’ın bire eşit olduğu satırları filtrelemeniz yeterlidir.

Örnek: Bu örnek, her üyenin (kullanıcının) ziyaret ettiği ilk tarihi almak için ROW_NUMBER() işlevini nasıl kullanabileceğinizi gösterir.

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

5) DENSE_RANK() ile Sıralamayı Kaydet

DENSE_RANK(), eşit değerler için aynı sıralamayı döndürmesi dışında ROW_NUMBER() fonksiyonuna benzer. Yoğun sıralama, en iyi kayıtları almak söz konusu olduğunda oldukça kullanışlıdır, örneğin:

• Bu hafta en çok izlenen 10 Netflix şovunu çekmek istiyorsanız
• Harcanan dolar bazında ilk 100 kullanıcıyı almak istiyorsanız
• En az aktif 1000 kullanıcının davranışını görmek istiyorsanız

Örnek: En iyi müşterilerinizi toplam satışlara göre sıralamak istiyorsanız, DENSE_RANK() kullanmak uygun bir işlev olacaktır.

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