Yeni Başlayanlar İçin SQL Pencere İşlevlerini Anlama Kılavuzu - Bölüm 2

İçinde önceki makale , çeşitli analitik problemleri çözmek için harika bir araç olan pencere işlevlerinden bahsettik; karmaşık sorgular yazmaya gerek kalmadan gelişmiş analiz ve veri işleme gibi özelliklere erişmenizi sağlayabilirler. Okumaya devam etmeden önce, SQL'de pencere fonksiyonlarının nasıl çalıştığına dair temel fikri kavramanıza yardımcı olacağı için ilk bölümden başlamanızı tavsiye ederim.

Artık temel bilgilere aşina olduğunuza göre daha gelişmiş SQL kavramlarını inceleyelim. İlk bakışta biraz karmaşık gelebilir ancak olası tüm durumlar için yeni başlayanlara uygun basit örnekler sunacağım, böylece driftimi yakalamam kolay olacak.

İçeriğe Genel Bakış

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

Kümülatif Toplam

over() ifadesinin hiçbir parametreye sahip olmadığı veya parametreye göre bir bölüme sahip olduğu örnekleri daha önce değerlendirmiştik. Şimdi over() ifadesinin ikinci olası parametresine ( order by) bakacağız.

Çalışan kimliğini, çalışan adını, departmanını, maaşını ve tüm maaşların toplamını talep edelim:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

Şimdi over() ifadesine order by parametresini ekleyeceğiz:

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

Sanırım burada olup bitenlere daha yakından bakmamız gerekiyor:

1. Her şeyden önce, çalışan_id artık azalan düzende sıralanıyor.

   
   

2. Pencere fonksiyonunun uygulanmasından elde edilen sütunda artık kümülatif bir toplam bulunmaktadır.

   
   

Kümülatif toplama aşina olduğunuzu düşünüyorum. Özü basittir; kümülatif toplam veya toplam, "şimdiye kadar ne kadar" anlamına gelir. Kümülatif toplamın tanımı, daha fazla eklemeyle artan veya büyüyen belirli bir dizinin toplamıdır.

Örneğimizde şöyle bir şey var: Employee_id değeri en yüksek olan çalışanın maaşı 3700, kümülatif toplamı da 3700. İkinci çalışanın maaşı 1500, kümülatif toplamı ise 5200. Üçüncü çalışanın maaşı 1500. maaşı 2900 olanın kümülatif toplamı 8100'dür, vb.

over() ifadesindeki order by parametresi sırayı belirtir. Pencere işlevlerinin toplanması durumunda, kümülatif toplamın sırasını belirler.

over() ifadesinde hem bölümleme ölçütü hem de sıralama ölçütü nitelikleri belirtilebilir.

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

Bu durumda kümülatif toplam bölümlere göre hesaplanacaktır.

Kümülatif toplamı anlattıktan sonra belki de sıklıkla kullanılan tek kümülatif toplam türü olduğunu söylememiz gerekir. Aksine, kümülatif ortalama ve kümülatif sayım nadiren kullanılır.

Yine de Kümülatif ortalama hesaplamasına bir örnek vereceğiz; bu bize belirli bir noktaya kadar bir dizi değerin ortalamasını söyler:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

Sıralama Penceresi İşlevleri

Bir değerin bir değerler kümesi içindeki konumunu belirlemek için sıralama penceresi işlevlerini kullanırız. OVER cümleciği içindeki ORDER BY ifadesi, her değere kendi belirlenmiş bölümü içinde bir sıra atanarak sıralamanın temelini belirler. Sıralama kriterleri için satırlar aynı değerleri paylaştığında onlara aynı sıralama atanır.

Sıralama penceresi fonksiyonlarının nasıl çalıştığını görmek için maaş tablosundan şu sütunları talep edelim: çalışan kimliği, çalışan adı, departman ve maaş:

 select employee_id, employee_name, department, salary from salary 

Şimdi, row_number() over() pencere işleviyle bir sütun daha ekliyoruz:

 select employee_id, employee_name, department, salary, row_number() over() from salary 

Row_number() over() pencere işlevi, sıralarını değiştirmeden satırlara sayılar atadı. Şu ana kadar bu bize pek bir değer katmadı değil mi?

Peki ya satırları maaş sırasına göre azalan şekilde numaralandırmak istersek? Bunu başarmak için sıralama düzenini belirtmemiz yani parametreye göre sırayı over() ifadesine aktarmamız gerekiyor.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Karşılaştırma için geri kalan sıralama işlevlerini sorguya ekleyeceğiz:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

Her bir sıralama penceresi fonksiyonunu inceleyelim:

1. Row_number() over(order by maaş desc) pencere işlevi, satırları azalan maaş sırasına göre sıralar ve satır numaralarını atar. Annie ve Tony'nin aynı maaşa sahip olduğunu ancak onlara farklı numaralar verildiğini unutmayın.

   
   

2. Rank() over(order by maaş desc ) pencere işlevi, rütbeleri maaşın azalan sırasına göre atar. Aynı değerler için aynı sıralamayı atar ancak sonraki değer yeni bir satır numarası alır.

   
   

3. Yoğun_rank() over(maaş açıklamasına göre sırala) pencere işlevi, rütbeleri maaşın azalan sırasına göre atar. Aynı değerler için aynı rütbeyi atar.

   
   

4. Pencere işlevi yüzde_rank() üzeri(maaş açıklamasına göre sırala) , şu formülle hesaplanan geçerli satırın göreceli (yüzde) sıralamasıdır: (sıra - 1) / (bölümdeki toplam satır sayısı - 1).

   
   

5. Pencere işlevi ntile(5) over(order by maaş desc), satır sayısını 5 eşit parçaya böler ve her parçaya bir sayı atar. Parça sayısı ntile(5) fonksiyonu içinde belirtilir.

   
   

Kullanım Durumları

Sıralama penceresi işlevlerini kullanarak pratik görevleri keşfetmenin zamanı geldi. Çalışan kimliğini, çalışan adını, departmanı ve maaşı görüntüleyeceğiz ve satır numaralarını azalan maaş sırasına göre atayacağız.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Bazen departmanlar (bölümler) içindeki satırları maaş sırasına göre azalan şekilde numaralandırmanız gerekebilir. Bu, over() ifadesine özniteliğe göre bölüm eklenerek yapılabilir:

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

Görevi daha zorlu hale getirelim. Her departmanda en yüksek maaşı alan yalnızca bir çalışanı tutmamız gerekiyor. Bu, bir alt sorgu kullanılarak elde edilebilir:

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

Ve bir örnek daha, her şehirde en yüksek maaşı alan üç çalışanı göstermemiz gerekirse şunu yapacağız:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

Bu tür görevler çok yaygındır, özellikle de bazı niteliklerin bölümleri (grupları) içindeki belirli sayıda satırı artan veya azalan sırada görüntülemeniz gerektiğinde. Uygulamada, sürekli olarak row_number() over() pencere fonksiyonunu ve tabii ki yoğun_rank() over() pencere fonksiyonunu kullanıyorum.

Ofset Pencere İşlevleri

Bu işlevler, geçerli satırdan uzaklıklarına bağlı olarak diğer satırlardan veri döndürmenize olanak tanır. Daha görsel hale getirmek için, ilk_değer(), son_değer() ve nth_değer() işlevlerini inceleyelim.

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

First_value(maaş) over(bölüm bazında bölümleme) ve last_value(salary) over(bölüm bazında bölümleme) işlevleri, bölüm (bölüm) içindeki ilk ve son maaş değerlerini görüntüler.

Buna karşılık, nth_value(salary, 2) over(bölüm bazında) işlevi bölüm (bölüm) içindeki ikinci maaş değerini gösterir. Lütfen nth_value() işlevinde ek bir argümanın (bölüm içindeki satır numarası) belirtildiğini unutmayın. Bizim durumumuzda satır numarası 2 olduğundan fonksiyon ikinci maaş değerini göstermektedir.

Yukarıdakilerin dışında lag() ve lead() fonksiyonları da vardır. Lag() fonksiyonu geçerli satırın önündeki satırdan değer almak için kullanılır. Lead() işlevi, geçerli satırın ardından gelen bir satırdan değer elde etmek için kullanılır.

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

Gördüğünüz gibi lag (maaş) over (maaşa göre sırala) işlevi maaşları bir satır aşağı kaydırır ve lead(maaş) over(maaşa göre sırala) işlevi maaşları bir satır yukarı kaydırır. Bu işlevler oldukça benzer olsa da, lag() işlevini kullanmayı daha uygun buluyorum.

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

Burada lag() daha önce olduğu gibi aynı işlevi yerine getiriyor, ancak artık özellikle bölümler (departmanlar) içinde.

Temel Çıkarımlar

Ve son olarak, bugün ele aldığımız konulara kısa bir genel bakış:

• Kümülatif toplam, bir dizinin sonraki her eklemeyle biriken toplamını temsil eder.

  
  

• Sıralama penceresi işlevleri, bir değer kümesi içindeki bir değerin konumunu belirlemek için kullanılır; ifadeye göre sıralama, sıralamanın temelini belirtir.

  
  

• Ofset pencere işlevleri arasında f irst_value() , last_value() ve nth_value() yer alır ve geçerli satıra olan mesafelerine bağlı olarak diğer satırlardan veri alınmasına olanak tanır. lag() ve lead() işlevlerini unutmayın. Lag() işlevi, geçerli satırdan önceki satırdan değer almak için kullanışlı olabilir; lead() işlevi ise geçerli satırın ardından gelen satırdan değer almak için kullanılır.

  
  

Bana katıldığınız için teşekkürler. Bu makalenin SQL'deki pencere işlevlerinin yeteneklerini daha iyi anlamanıza yardımcı olacağını ve rutin görevlerde daha güvenli ve hızlı olmanızı sağlayacağını umuyorum.