İç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 ifadesinin hiçbir parametreye sahip olmadığı veya parametreye göre bir bölüme sahip olduğu örnekleri daha önce değerlendirmiştik. Şimdi ifadesinin ikinci olası parametresine ( over() over() 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 ifadesine parametresini ekleyeceğiz: over() order by 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: Her şeyden önce, artık azalan düzende sıralanıyor. çalışan_id 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: 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. Employee_id 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. over() 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. Dikkat! ifadesinde her iki nitelik de belirtilirse, by bölümü her zaman önce gelir ve ardından gelir. Örneğin: . over() by sırası over(personal_id'ye göre departman sırasına göre bölümleme) 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. cümleciği içindeki 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. OVER ORDER BY 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, pencere işleviyle bir sütun daha ekliyoruz: row_number() over() select employee_id, employee_name, department, salary, row_number() over() from salary 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? Row_number() over() 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: 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. Row_number() 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. 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ı rütbeyi atar. Yoğun_rank() over(maaş açıklamasına göre sırala) Pencere işlevi , ş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). yüzde_rank() üzeri(maaş açıklamasına göre sırala) Pencere işlevi satır sayısını 5 eşit parçaya böler ve her parçaya bir sayı atar. Parça sayısı fonksiyonu içinde belirtilir. ntile(5) over(order by maaş desc), ntile(5) Dikkat! Toplama işlevlerinden farklı olarak, örneğin sum(salary), sıralama işlevleri, örneğin satır_numarası(), içinde bir sütun almaz. Ancak ntile(5) fonksiyonunda parça sayısı 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 pencere fonksiyonunu ve tabii ki pencere fonksiyonunu kullanıyorum. row_number() over() yoğun_rank() over() 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 Dikkat! Her üç pencere fonksiyonunda da gerekli bölümü parametreye göre belirtmek için. Parametreye göre sıralama zorunlu değildir ancak bunu belirterek bölüm içindeki satırların sırasını değiştirebilirsiniz. Örneğin aşağıdaki sorguda bölüm (bölüm) içerisinde maaşa göre sıralama yaptık ve artık ilk_değer bölümdeki en yüksek maaş oluyor. 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 ve işlevleri, bölüm (bölüm) içindeki ilk ve son maaş değerlerini görüntüler. First_value(maaş) over(bölüm bazında bölümleme) last_value(salary) over(bölüm bazında bölümleme) Buna karşılık, işlevi bölüm (bölüm) içindeki ikinci maaş değerini gösterir. Lütfen 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. nth_value(salary, 2) over(bölüm bazında) nth_value() Yukarıdakilerin dışında ve fonksiyonları da vardır. 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. lag() lead() Lag() select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 Gördüğünüz gibi işlevi maaşları bir satır aşağı kaydırır ve işlevi maaşları bir satır yukarı kaydırır. Bu işlevler oldukça benzer olsa da, işlevini kullanmayı daha uygun buluyorum. lag (maaş) over (maaşa göre sırala) lead(maaş) over(maaşa göre sırala) lag() Dikkat! Bu işlevler için over() ifadesinde parametreye göre sıralamanın belirtilmesi zorunludur. Ayrıca bölümlemeyi kullanarak bölümlemeyi de belirtebilirsiniz, ancak bu zorunlu değildir. select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department Burada daha önce olduğu gibi aynı işlevi yerine getiriyor, ancak artık özellikle bölümler (departmanlar) içinde. lag() 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 sıralama, sıralamanın temelini belirtir. göre Ofset pencere işlevleri arasında f , ve yer alır ve geçerli satıra olan mesafelerine bağlı olarak diğer satırlardan veri alınmasına olanak tanır. ve işlevlerini unutmayın. işlevi, geçerli satırdan önceki satırdan değer almak için kullanışlı olabilir; işlevi ise geçerli satırın ardından gelen satırdan değer almak için kullanılır. irst_value() last_value() nth_value() lag() lead() Lag() lead() 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.
