t-SQL ile Daha Performanslı ve Anlaşılır Hesaplamalar Yapın

SQL Server 2012 ile gelen Window Function var, farklı boyutlardaki hesaplamalarımızı sub-query kullanmadan hem daha performanslı, hem de daha rahat okunabilir bir şekilde yazmamıza olanak sağlıyor.

Kullanmaya alışkın olduğumuz özetleme fonksiyonları (SUM, Count gibi) yanına Over kelimesi eklenerek bir window function a dönüşüyor. Ayrıca yeni gelen fonksiyonlar ile de hesaplamalarımızı yapmak daha da kolay hale geliyor. Over içerisine yazılan Partition By ifadesi hesaplamaların neye göre yapacağımızı, Order By ifadesi ise sıralama gerektiren fonksiyonlarda sıralamanın hangi kolonlara göre yapılacağınız ifade ediyor. Hem Partition By, hem de Order By için virgül (,) ile ayırarak istediğimiz kadar kolonu ekleyebiliyoruz. Ayrıca her bir alt sorgu ( sub query) tabloya tek tek giderek tabloyu tarıyor ancak window function kullandığımızda bu tarama yükünü azaltıyoruz ve hesaplamala işlemlerimiz daha da performanslı hale geliyor. Aşağıdaki örnekleri ve video muzu inceleyerek kullanımı hakkında daha detaylı bilgi sahibi olabilirsiniz.

Müşteri, Ürün ve Tarih bazında 2018 yılı Ocak - Temmuz satış rakamlarının tutulduğu, 200 ürün ve 300 müşterimizin olduğu 12.000 civarında kaydığımızın olduğu tablomuzda, her bir kaydın yanına Müşterinin o ay yaptığı toplam satışı, şimdiye kadar yaptığı toplam satışı, ürünün ilgili aydaki toplam satışını ve şimdiye kadar yapılan toplam satışı eklemek için, window function kullanmadan, her bir boyuta göre hesaplamalarımızı sub-query ile yapmamız ve bu hesaplamalarımızı uygun kolonlar ile bağlamamız gerekiyor.

Aşağıdaki ilk sorgu bu yöntem ile yazılmış bir sorgu.

Aynı sorgunun window fonksiyonları ile yazılmış halini de bir altta görebilirsiniz.

SET STATISTICS TIME ON yaparak çalışan her bir sorgunun ne kadar sürdüğünü de mesajlara yazdırabiliriz.

Örneğin aşağıdaki sorgu, Müşteri numarası ve aya göre toplam satışı getiriyor. Yani her bir müşteri için o ayki koli satış toplamını alıyor. Bunu sorgu olarak yazdıktan sonra da her bir kolon ile de bağlamak gerekiyor.

Aşağıda ise aynı sorgunun Over ile window fonksiyona çevrilmiş halini görebilirsiniz.

SUM(Koli) ifadesi aynen kalıyor. Yanına Over ekleniyor ve içerisine Partition By dan sonra group by da kullandığımız kolonlar ekleniyor. Böylece her bir müşteri ve ay için satışlar hesaplanıyor ve sorgumuza ekleniyor.

Rank / Dense_Rank ile sıralama

Diyelim ki müşterilerimizi en fazla satış yapandan, en az satış yapana doğru sıralamak istiyoruz. Aşağıdaki sorgudaki With ifadesi bize hafızada geçici bir query yaratıyor (CTE: Common table expression) Artık "Musteri" adı ile tablo gibi sorgumuzu çağırabiliriz. Bu sorgu, her bir müşteriye yapılan toplam satışı ve kaç farklı ürün aldığını getiriyor. DISTINCT ifadesi ile ürün birden fazla kez geçse de, 1 kere sayıyor.

Not: DISTINCT ifadesi window function larda kullanılamıyor. Ürünleri tekil sayabilmek için CTE kullandık.

Aşağıdaki sorgu, Toplam kolonunda yazan değerleri büyükten küçüğe doğru sıralayarak en fazla satış yapılan müşteri için 1, en az yapılan müşteri için 300 yazıyor. (300 müşteri kaydımız var

Sorgu sonucundan da göreceğimiz gibi 2250 nolu müşterimiz 168 koli ile 137. sırada iken, 2190 nolu müşterimiz ise 181 koli ile 128. sırada.

Yine her bir müşteri için en az ürün çeşidi alandan, en fazla alana doğru bir sıralama yapalım. Bunun için iki farklı sıralama fonksiyonu kullandık.

Rank ve Dense_Rank fonksiyonlarının her ikisi de verdiğimiz sıralamaya göre bir sayı üretiyorlar. Bir yarışmada 1.yi, 2.yi belirlemek gibi. Eğer sonuçları aynı olan kayıtlar var ise bu kayıtlar için aynı sıralama üretiliyor. Ancak aralarında yukarıda işaretli olan satırlardan da görebileceğiniz gibi bir fark var. Rank fonksiyonu, 2 tane 2. var ise, bir sonraki kaydı 4. olarak sayarken Dense_Rank ise 3. olarak sayıyor. Rank kayıt sayısına bakarken, Dense_Rank ise değerlerin kaçıncı olduğuna bakıyor.

Row_Number ve NewID () ile Rastgele Sıralama

Row_Number() Fonksiyonu, adından da anlaşılacağı gibi bir satır numarası üretiyor. Yine Partition By daki kolona göre numarayı yeniden başlatıyor ve satır numarasını da order by daki kolonlara göre yapıyor.

Burada başka bir numara yaparak rastgele kayıtları nasıl seçeceğimizden bahsedelim. Test veri seti oluşturmak için bir tablodan rastgele n kadar kayıt almak isteyebilirsiniz ya da aşağıdaki örnekte yaptığımız gibi, her bir müşterinin her bir sipariş satırı için bir çekiliş hakkı verdiğinizi ve talihli 15 kişiye de hediyeler dağıtacağınızı varsayalım. Peki bunu nasıl yapabiliriz.

TOP 15 bize 15 kayıt getiriyor.

Buyuk_Cekilis kolonu ise Row_Number fonksiyonu ile bir satır numarası üretiyor. Ancak buradaki asıl numara NewID() fonksiyonu. NewID() her çalıştırdığında uniqueidentifier tipinde tekil bir değer üretiyor ve tablodaki her bir kayıt için de farklı değerler üretiliyor. NewID() ye göre sıralamak ise tamamen rastgele bir seçim imkanı sağlıyor.

Eğer bu çekilişi aylık yapmak istiyorsak ve her aydan rastgele 15 kişi seçmek istiyorsak o zaman Over(Partition By MonthKey). yazmak yeterli. Her bir ay için numaratör 1 den başlayacak. Tabii en sondaki ORDER BY ifadesini AylikCekilis e çevirmek ve TOP 15 i kaldırmayı atlamamak gerekli.

NTILE ile verilerimizi gruplama

Sayısal verilerimiz var ve bunları gruplandırmak isteyelim. Örneğin 18 - 75 arasında değişen yaşlarda 35 farklı yaş verimiz var ve 5 eşit grup halinde bu verileri almak istiyoruz. Bunun için tek tek grupları hesaplamaya ihtiyacımız yok. NTILE fonksiyonu aldığı parametreye göre verilerimizi bölüyor.

Yine aynı tablomuzdan bir örnek yapalım. Ürünlerimizi toplam satışlarına göre 3 gruba ayırmak istediğimizi ve üretim planımızı da buna göre yapacağımızı varsayalım. İlk grup bizim en çok satış yaptığımız %33 grup olacak ve bu ürünleri üretmeye devam edeceğiz. 2. %33 lük grup ise bizim izleme grubumuz olacak ve bu ürünleri bir çeyrek daha izleyip kararı ona göre vereceğiz, 3. %33 lük grup ise üretim den vazgeçmeyi düşüneceğimiz ürünler olsun. Bunu yapmanın kolay yolu NTILE(3) ile verimizi 3 e bölmek istediğimizi söylemek OVER(Order By Toplam DESC) ile de verileri önce Toplam kolonuna göre büyükten küçüğe göre sırala sonra 3 eşit parçaya böl demiş oluyoruz. 200 farklı ürünümüz var ve 66 veya 67 lik gruplar halinde veriyi dağıtmasını bekliyoruz.

Örnekte de gördüğünüz gibi ilk 67 lik satır 1. gruba dahil iken, 68. den itibaren 2. grup başlıyor.

Sayfalama ve kayıt seçme (OFFSET - FETCH )

Her ne kadar window fonksiyon olmasa da, window fonksiyonlar ile kullanıldığında çok fayda sağlayan, SQL Server 2012 ile birlikte gelen ve ORDER BY ifadesi ile kullanılan OFFSET ve FETCH ifadelerinden bahsetmek gerekli.

Özellike uygulama geliştiriciler, sayfalama yapmak ve belli aralıktaki verileri getirmek için bu ifadeleri kullanabilirler.

* Her zaman ORDER BY dan sonra kullanılır

* OFFSET n : n kadar kayıt atla demektir

* FETCH n : n kadar kayıt getir demektir. OFFSET in arkasından kullanılır ve opsiyoneldir. Kullanılmadığında geriye kalan tüm kayıtları getirir.

Bu örnekte "Urun" tablo ifadesi, Ürün bazında toplam satışları getiriyor. Toplam satışlar büyükten küçüğe doğru sıralanıyor ve ilk 150 kayıt atlanıyor. 151. kayıttan başlanarak 15 satır getiriliyor. FETCH i çalıştırmaz isek, en az satışı olan 50 kayıt (200 ürünümüz vardı) getiriliyor.

Detaylar için Youtube kanalımızdaki videomuza gözatabilirsiniz.

Teşekkürler

Birsen Tunç Yazar

inteley Yazılım Danışmanlık

20.09.2018