Yeni Başlayanlar İçin SQL Pencere İşlevlerini ve Yeteneklerini Anlama Kılavuzu

İşe alım süreci boyunca ekibim için birçok yetenekli adayla tanışmanın mutluluğunu yaşadım. İşimiz karmaşık veri kümelerini ele almayı gerektirdiğinden, her adayın akıllı çözümler bulma yeteneğini ölçmek benim için önemliydi. Yeterliliklerini değerlendirmek için SQL'deki pencere işlevleriyle ilgili deneyimlerini sordum. Birçoğu bu işlevleri biliyor olsa da, çok azı bunları etkili bir şekilde kullanabildi.

Pencere fonksiyonları neredeyse 20 yıldır ortalıkta olmasına rağmen birçok SQL geliştiricisi hala bunları kavramakta zorluk çekiyor. Deneyimli geliştiricilerin bile StackOverflow'un ne yaptığını gerçekten anlamadan kodu kopyalayıp yapıştırması alışılmadık bir durum değildir. Bu makale size yardımcı olmak için burada! Pencere işlevlerini anlaşılması kolay bir şekilde açıklayacağım ve bunların gerçek dünyada nasıl çalıştıklarını size gösterecek örnekler sunacağım.

Pencere işlevlerini duydunuz mu? Pek çok sorunu çözebilecek harika analitik araçlardır. Örneğin, müşteri kimliği gibi ortak bir özelliği paylaşan bir dizi satırı hesaplamanız gerektiğini varsayalım. Pencere fonksiyonlarının kullanışlı olduğu yer burasıdır! Toplama işlevleri gibi çalışırlar ancak bunları bir arada gruplamak yerine her satırın benzersizliğini korumanıza olanak tanırlar. Ayrıca, pencere işlevlerinin sonuçları çıktı seçiminde ekstra bir alan olarak görünür. Bu, analitik raporlar hazırlarken, hareketli ortalamaları hesaplarken ve toplamları hesaplarken veya farklı ilişkilendirme modellerini çözerken son derece faydalıdır.

SQL ve pencere fonksiyonlarının dünyasına hoş geldiniz! Yeni başlıyorsanız doğru yerdesiniz. Bu makale yeni başlayanlar için uygundur, açık açıklamalar içerir ve karmaşık terminoloji veya ileri düzey kavramlar içermez. Konuya tamamen yeni olsanız bile kolaylıkla takip edebileceksiniz.

İçeriğe Genel Bakış

• Pencere İşlevleriyle Kullanılan İşlev Türleri
  • Toplama işlevleri
  • Sıralama işlevleri
  • Değer fonksiyonları
• Toplu Pencere İşlevleri
• Temel Çıkarımlar

Pencere İşlevleriyle Kullanılan İşlev Türleri

Pencere işlevlerinin bir dizi satıra (pencere adı verilen) uygulanabileceği üç ana işlev türü vardır: bunlar toplama, sıralama ve değer işlevleridir. Aşağıdaki resimde her kategoriye giren farklı işlevlerin adlarını görebilirsiniz.

Toplama işlevleri

Bunlar bir veri grubu üzerinde matematiksel işlemler gerçekleştirerek tek bir kümülatif değer elde edilmesini sağlar. Ortalama, toplam satır sayısı, maksimum veya minimum değerler veya her pencere veya bölüm içindeki toplam toplam dahil olmak üzere çeşitli toplamları hesaplamak için kullanılırlar.

• SUM: sütundaki tüm değerleri toplar

• COUNT: NULL değerler hariç sütundaki değerlerin sayısını hesaplar

• AVG: sütundaki ortalama değeri bulur

• MAX: sütundaki en yüksek değeri tanımlar

• MIN: sütundaki en düşük değeri tanımlar

  
  

Sıralama işlevleri

Bunlar bir bölümdeki her satıra bir sıra veya sıra vermek için kullanılır. Bu, sıralı sayılar atamak veya sıralamayı belirli değerlere dayandırmak gibi belirli kriterleri değerlendirerek yapılır.

• ROW_NUMBER: bir bölümdeki her yeni kayda sıralı bir sıralama numarası atar
• RANK: sonuç kümesindeki her satırın sıralamasını belirtir. Bu durumda sistem aynı değerleri tespit ederse bunlara aynı sıralamayı atayacak ve bir sonraki değere atlayacaktır.
• DENSE_RANK: sonuç kümesinin bir bölümündeki her satıra bir sıra atar. RANK işlevinden farklı olarak işlev, sonraki değerleri atlamadan aynı değerler için sıraları döndürür.
• NTILE: Mevcut hattın hangi gruba ait olduğunu belirlememizi sağlar. Grup sayısı parantez içinde verilmiştir.
  

Değer fonksiyonları

Bunlar, bir gruptaki farklı satırlar arasındaki değerleri karşılaştırmayı kolaylaştırır ve aynı zamanda değerleri o gruptaki ilk veya son değerle karşılaştırmanıza olanak tanır. Bu, bir penceredeki farklı satırlar arasında kolayca hareket edebileceğiniz ve pencerenin başındaki veya sonundaki değerleri kontrol edebileceğiniz anlamına gelir.

• LAG veya LEAD: kendi kendine birleştirme işlemi gerçekleştirmeye gerek kalmadan önceki veya sonraki satırdaki verilere erişin. Bu işlevler, zaman içindeki farkları hesaplamak gibi, aynı sonuç kümesi veya bölüm içindeki bir satırı başka bir satırla karşılaştırmayı gerektiren sorunları çözerken özellikle faydalıdır.
• FIRST_VALUE veya LAST_VALUE: tanımlanmış bir pencere veya bölümden ilk veya son değeri alır. Bu işlevler özellikle belirli bir zaman dilimi içindeki farkları hesaplamak istediğinizde kullanışlıdır.

Pencere işlevlerine başlamak için varsayımsal bir 'maaş' tablosu oluşturalım ve onu verilerle dolduralım.

Tablo oluşturma:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

Tablonun doldurulması:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

'Maaş' tablosunu başarıyla doldurup doldurmadığımızı kontrol edelim:

 select * from salary 

Bir sonraki sorgu tablomuzdaki çalışanların isimlerini ve maaşlarını gösterecektir:

 select employee_name, salary from salary 

Maaşların toplamının, ortalama maaşın, maksimum, minimum ve satır sayısının hesaplanması, toplu işlevlerin bazı yaygın kullanım durumlarıdır:

Toplama fonksiyonu uygulandığında maaşlar toplanıp tek satırda gösterilir.

Peki ya 'maaş' tablosundan çalışanların isimlerini ve maaşlarını, üçüncü sütunda ise tüm maaşların toplamını görüntülemek istersek? Bu değer tüm satırlar için aynı olmalıdır.

Bir pencere işlevini kullanmak harika bir fırsat!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

sum(salary) over() fonksiyonunun her satırındaki maaşların toplamını hesaplayan pencere fonksiyonuna daha yakından bakalım.

over() ifadesi, işlevin üzerinde çalıştığı bir pencereyi veya bir dizi satırı tanımlar. Örneğimizde pencere tablonun tamamıdır, yani fonksiyon tüm satırlara uygulanacaktır.

over() ifadesi yalnızca over() den önce istenen işlevlerle eşleştirildiğinde çalışır.

Örneğin, sum(salary) over() ; burada sum() bir toplama işlevidir. Ve sum(salary) over() ifadesinin tamamı bir toplam pencere işlevidir.

Daha önce de söylediğim gibi pencere fonksiyonlarının uygulandığı tüm fonksiyonlar üç gruba ayrılabilir: toplama, sıralama ve değer fonksiyonları.

Toplama işlevleri sum() , count() , avg() , min() , max() over() () ifadesiyle birlikte bir grup toplama pencere işlevi oluşturur.

Bu makalede, bu özel pencere işlevleri türüne odaklanacağız.

Toplu Pencere İşlevleri

Örneklere dönelim!

Çalışanların isimlerini rica edelim; maaşları; tüm maaşların toplamı; ortalama, maksimum ve minimum maaş; çalışan sayısı.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

Artık pencere işlevlerinin ne olduğunu daha net anladığımıza göre, bunların işinizde yararlı olabileceği bazı durumları inceleyelim.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

Dördüncü sütunda her maaş için toplam maaş bütçesinin yüzdesini hesapladık. Jessa'nın maaşı tüm maaş bütçesinin neredeyse %15'ini oluşturuyor.

Ayrıca salary/sum(salary)over() yüzdelerini hesaplayan formülü order by sıralamaya yerleştirdiğimizi unutmayın. Bir pencere işlevi yalnızca çıktı select içinde değil aynı zamanda order by de bulunabilir.

Başka bir örnek: Maaşları şirketin ortalama maaşıyla karşılaştıralım.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

Gördüğümüz gibi Andrew'un maaşı ortalamanın 2110 dolar altında, Jessa'nınki ise ortalamanın 1690 üzerinde.

Üç sütun isteyelim: çalışanın adı, departmanı ve maaşı. Ayrıca bunları departmanlara göre sıralayacağız.

 select employee_name, department, salary from salary order by department 

Şimdi aynı üç sütunu ve ayrıca tüm çalışanların maaşlarının toplamını içeren bir sütunu isteyeceğiz. Bunun bir pencere fonksiyonuyla yapılabileceğini zaten biliyorsunuz.

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

Peki ya son sütunda gösterildiği gibi tüm maaşların toplamını değil de her departmanın maaşlarının toplamını talep etmek istersek:

over() ifadesine parametreye partition by ekleyerek bunu yapabiliriz:

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

Partition by pencere fonksiyonunu tüm satırlara (pencerenin tamamına) değil, sütun bölümlerine uygulamamıza olanak tanır.

Basit bir gruplandırmaya benzemiyor mu? Her departmanın maaşlarının toplamını hesaplamak için departmanlara göre bir gruplama (pencere fonksiyonlarının argo bölümündeki bölümler) yapar ve tutarı hesaplarız:

 select department, sum(salary) from salary group by department 

Temelde, gruplandırma ile partition by arasındaki fark, group by grup başına bir satır döndürmesi, buna göre partition by ise işlevin sonuçları group by ile toplama fonksiyonunun sonuçlarıyla aynı olmasına rağmen tüm satırlara toplama sağlamasıdır. bir gruba dayalı işlev.

Pencere fonksiyonlarına geri dönelim:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

Pencere fonksiyonunu, özellikle de parametreye partition by kullanarak, her çalışanın maaşının, departmanın maaşlarının toplamından payını hesaplayabiliriz. Veya örneğin maaşları departmandaki ortalama maaşla karşılaştırmak için.

Temel Çıkarımlar

Özetlemek gerekirse:

• Bir pencere işlevi, bir şekilde geçerli satırla ilişkili olan bir dizi satır boyunca hesaplama gerçekleştirir.

• Pencere fonksiyonlarının uygulandığı ana fonksiyon türleri toplama, sıralama ve değer fonksiyonlarıdır.

• Bir pencere işlevini kullanmak için, sorgu sonuç kümesindeki bir pencereyi (bir dizi satır) tanımlayan over() yan tümcesini uygulamanız gerekir. Pencere işlevi daha sonra penceredeki her satır için bir değer hesaplar;

• Toplama işlemini gerçekleştirmek istediğiniz sütunu belirtmek için, partition by cümlesini over() cümlesine eklemeniz gerekir. Partition by , gruplandırmaya benzer ancak grup başına bir satır yerine, toplama işlevi uygulanmış tüm satırları döndürür.

  
  

Şimdilik bu kadar! Sonraki birkaç makalede, yeni başlayanlara uygun basit örneklerle daha gelişmiş SQL kavramlarını keşfedeceğim, o yüzden bizi izlemeye devam edin!