2. SQL Server 2012 ve Yeni Analitik Fonksiyonlar - LAG() Fonksiyonu

• LAG() fonksiyonu 2012 ile gelen 8 yeni analitik fonksiyondan biridir.
• Bu fonksiyon extra bir join yapmadan bir önceki satırdaki verileri mevcut satırda işlememize imkan verir. LEAD() fonksiyonu mantığı ile çalışır sadece terse işlemektedir.

Daha iyi anlamak için aşağıdaki örneklerimizi inceleyebiliriz.

Kullanımı;

LAG( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Örnek Veri tabanımız;

Örnek 1;

Açıklama 1;

İlk örneğimizde en basit anlamda LAG() fonksiyonunun kullanımını gördük. Mevcut satırımızda bir önceki satırda istediğimiz değerini gösteriyoruz. Eğer değer yok ise NULL olarak gözükmekte.

Örnek 2;

   Açıklama 2;

Bu örneğimizde ufak bir değer ataması yaptık. Bir önceki satırdaki Value değişkeni ile mevcut satırdaki Value değerini birbirinden çıkarıyoruz. Bunun gibi satır kontrollerini farklı değerler ve satırlar üzerinde de yapabiliriz. Böylece elimizdeki sorgudaki istediğimiz alanları extra join işlemi yapmadan karşılaştırabiliriz.

Örnek 3;

   Açıklama 3;

Bu örneğimizde kaç satır önceki değerler ile karşılaştırma yapıp mevcut satırda kullanacağımızı gösterdik. Bu değeri daha da arttırabilirsiniz. Böylece mevcut satırda istediğiniz n. satır önceki değerleri çağırıp istediğiniz gibi kullanabilirsiniz. 3 satır önceki değer yok ise NULL gelmekte.

Örnek 4;

   Açıklama 4;

Bu örneğimizde bir önceki kayıt eğer yok ise LAG() fonksiyonu getiremediği için NULL ataması yapıyordu. Bu tarz durumlarda NULL ise hangi değeri atayacağımız belirledik. Örneğimizde de NULL değerlere 0.00 ataması yapıldı.

Örnek 5;

   Açıklama 5;

Son örneğimizde ise dikkat ederseniz PARTITION kullandık. Böylece hem hızlı hem performanslı olan bu yapıda istediğimiz LAG() işlemini hangi alan üzerinden yapacağımız belirtiyoruz. Tabi bu durumda NULL değerlerimiz bir hayli fazla olmakta. Çünkü partition yaptığımız Name değerinde satırların sayısı az.

SQL Server'da Stored Procedure

• SQL ifadeleri SQL Serverda Stored Procedure olarak saklanmaktadır. 
• Basit örneklemek gerekirse " SELECT * FROM Birds " ifadesini saklayabiliriz. Ama büyük yapılarda kullanılan Stored Procedureler daha komplekstir. 
• Stored Procedure'ler birden fazla tablonun farklı JOIN işlemleri ile bağlanmasıyla ve parametreli veya parametresiz değerlerin WHERE koşullarıyla kısıtlanmasıyla istenilen sonuça hızlı ve sade bir şekilde ulaşmamızı sağlar.

ÖRNEK:
CREATE PROCEDURE Person_Selection
@yas INT

AS

IF(Yas> 19)
SELECT * FROM Person WHERE Yas > @yas ORDER BY Yas
ELSE
SELECT * FROM Person WHERE Yas> @yas and Eriskin=1 ORDER BY Yas

GO

39. SUBQUERIES-HAVING Dönüşümü

• İlişkili alt sorgularla elde ettiğimiz sonuçları aynı zamanda HAVING işlemi ile de elde edebiliriz. Bu bize kullanım çeşitliliği sağlar.

Örnek: İnceleyeceğimiz iki örnekte aynı kategorideki ürünlerden birim fiyatı ortalama birim fiyatından yüksek olan ürünleri kategoriye göre listelenmesini istiyoruz. İlk örneğimizde herhangi bir dönüşüm yapmadan gerekli koşullarımızı sağlıyoruz.

Sorgu:
SELECT ProductName,CategoryID,UnitPrice FROM Products AS p1
    WHERE p1.UnitPrice>(SELECT AVG(UnitPrice) FROM Products AS p2 WHERE p1.CategoryId=p2.CAtegoryId)
    ORDER BY CategoryId

Örnek: Bu örneğimizde INNER JOIN ve HAVING kullanarak aynı sonucu listeliyoruz.

Sorgu:
SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice FROM Products AS p1
    INNER JOIN Products AS p2 WITH (NOLOCK) ON p1.CategoryID=p2.CategoryID
    GROUP BY p1.ProductName,p1.CategoryID,p1.UnitPrice
    HAVING p1.UnitPrice>AVG(p2.UnitPrice)
    ORDER BY p1.CategoryId

Ders 40 'a geçebilirsiniz.

37. SUBQUERIES-İlişkili Alt Sorgular

• Aslında iç sorgular ve dış sorgular belirli bir alan yani sütun üzerinden ilişkili ve birbirlerine bağlıdır. 
• Bunu genelde WHERE koşulu üzerinden sağlarız.

Örnek: Aşağıdaki örneğimizde her bir ürün için verilmiş en yüksek sipariş miktarını listelemekteyiz.

Sorgu: 
SELECT DISTINCT ProductId,Quantity
FROM [order details] AS ord1
WHERE Quantity=( SELECT MAX(Quantity)FROM [order details] AS ord2
                  WHERE ord1.productid=ord2.productid )
ORDER BY ProductId

Ders 38'e geçebilirsiniz.

32. SELF-JOINS Kullanımı

• Bir tablonun kendisiyle JOIN işlemine tutulmasına SELF-JOIN işlemi denir. 
• Tablodaki aynı değerleri içeren farklı kayıtlar mevcutsa bunları listeleyebilmek için kendisiyle birleştiririz.  
• Bir tablo içinde bulunan satırlar arasında referans ilişkisi varsa, o tablo için farklı takma adlar (alias) kullanılarak JOIN sorguları gerçekleştirilebilir. 
• Çok kullanılan bir yöntem değildir.

 Örnek: Aşağıdaki sorgumuzda aynı tabloyu kendisiyle birleştirdik. Daha çok SELF-JOIN işlemini aynı değerleri olan farklı kayıtlar üzerinde işlem yaparken kullanmalıyız. Örnekte basitce aynı tablo içi kıyaslama yaptık. SicilNo olarak her çalışanın altında olan çalışanları listeledik.

Sorgu: 
SELECT a.employeeid AS SicilNo,a.lastname,a.title
,b.employeeid AS AltSicilNo, b.lastname ,b.title
FROM employees AS a
INNER JOIN employees AS b
ON a.title=b.title
WHERE a.employeeid < b.employeeid
ORDER BY a.employeeid

Ders 33'e geçebilirsiniz.

31. İkiden Fazla Tablonun Birleştirilmesi

• Büyük sorguların yazıldığı durumlarda ikiden fazla tablonun belirli kısıtlamalarla birleştirilmesi gerekebilir. 
• JOIN işlemiyle birleştirilen tablolar bir başka tabloya aradaki ortak sütunlar üzerinden bağlanabilir.

Örnek: Aşağıdaki örnekte Sipariş Detayları(Order Details) tablosunu ara tablo şeklinde kullanarak, Siparişler (Orders) ve Ürünler(Products) tablosundan gerekli bilgileri görüntülenmiştir. Bir sipariş birden fazla ürünü içerebilir ve bir ürün birden fazla siparişte yer alabilir. Sonuç kümemizde Sipariş Detayları (Order Details) tablosundan hiçbir alan görüntülemesekte sorguda kullanmalıyız.

Sorgu:

SELECT OrderDate,ProductName FROM Orders AS O

INNER JOIN [Order Details] AS OD ON O.Orderid=OD.Orderid

INNER JOIN Products AS P ON OD.Productid=P.Productid

WHERE OrderDate BETWEEN '7/8/96' AND '7/9/96'

ORDER BY OrderDate

Ders 32'ye geçebilirsiniz.

25. COMPUTE ve COMPUTE BY Kullanımı

• COMPUTE ifadesi tüm sonuç listemizi bize gösterir. 
• Farklı olarak tek sorguda 2 tane sonuç listesi bize döndürür. 2.liste detay listesidir. 
• Dikkat etmemiz gereken COMPUTE ile kullandığımız sütun SELECT ifadesinde yer almalıdır. 
• COMPUTE anahtar sözcüğü sorgumuzdaki sütunlara toplam fonksiyonları uygulamamıza izin verir.
• COMPUTE BY ile detaylı gruplandırma yapılabilir.

Örnek: Sorgumuzda ürün idsi 3 den küçükleri listeliyoruz. Ayrıntı olarak 2. sonuç kümemizde ürün id si 1 ve 2 olan ürünlerimizin sipariş adetine bakıyoruz.
Sorgu:
SELECT ProductId, OrderId, Quantity
FROM [Order Details]
WHERE ProductId<3
ORDER BY ProductId, OrderId
COMPUTE SUM(Quantity)

Örnek:  Bu sorgumuzda COMPUTE BY nasıl kullanılır onu görüyoruz. Ara toplamlara girerek ayrıntılı sorgulama yapıyoruz. Bu sorgumuzda amacımız hangi ürün id den ne kadar sipariş edilmiş onu öğrenelim.
Sorgu:
SELECT ProductId, OrderId, Quantity
FROM [Order Details]
WHERE ProductId<3
ORDER BY ProductId, OrderId
COMPUTE SUM(Quantity) BY ProductId

 

Ders 26 'ya geçebilirsiniz.

22. WITH ROLLUP Kullanımı

• GROUP BY ile bir toplama işlevi kullandığımızda her farklı değer için sadece sonuç satırı görüntülenir. 
• Gruplandırdığımız değerlerin detaylarını farklı yantümceler kullanarak görüntüleyebiliriz. 
• WITH ROLLUP ile daha detaylı sonuç listesi oluştururuz.
• Ayrıntılı bilgi ise GROUP BY ile kullanılan CUBE ve ROLLUP komutları bize ara toplamlar vermektedir. Örneğimizde gördüğümüz gibi null,null olarak bütün  değerler ara toplam olarak verilmiş, sonra ProductName sırasında ara toplamlar verilmiştir.
  

Örnek: Daha detaylı sonuç listemizde GROUP BY değerlerine ekleyerek bunu gerçekleştiriyoruz. İlerleyen derslerde göreceğimiz SQL LEN() fonksiyonunu burda görmüş oluyoruz. Ürünismi(ProductName) uzunluğu 8'den küçük olması koşulunu ekliyoruz. Dikkat etmeniz gereken GROUP BY değerlerine eklemediğiniz sütünu SELECT e yazamazsınız.
Sorgu:
SELECT ProductID,ProductName,CategoryID, SUM(UnitsInStock) AS 'Miktar'
FROM Products
WHERE LEN(ProductName)<8
GROUP BY ProductID,ProductName,CategoryId 
WITH ROLLUP ORDER BY ProductID,ProductName,CategoryId 

Ders 23' e geçebilirsiniz.

19. WITH TIES Kullanımı

• WITH TIES yantümcesi, ORDER BY ile sıralanan sonuç kümesinde son kayıt ile aynı değerde olan kayıtların da listelenmesini sağlar. 
• Bu durumda sonuç kümeniz belirtiğiniz n sayısından daha fazla olabilir.
•  WITH TIES yantümcesini sadece ORDER BY yantümcesi ile kullanabilirsiniz.

(İki sorgumuzu dikkatlice inceleyelim ve sonuçlarına bakalım. Resimde daha ayrıntılı görebilirsiniz.) 

Örnek: Normal TOP n ile çalıştırdığımız sorgularımızı eğer WITH TIES ile çalıştırırsak n den farklı sonuç listesi görebiliriz. Burda önemli nokta sıraladığımız yani ORDER BY ile istediğimiz quantity(adet) kısmıdır. Bu durumun anlamı ilk 5 değeri listeledikten sonra en son satıra ait quantity değeri daha varsa onlarıda getirmesidir. İlk sorguda 5 sonuçtan sonuncusunda quantity değerinin 120 olduğunu görmekteyiz. WITH TIES kullanılan sorguda quantity değeri 120 olan bütün değerlerin getirilmesini sağladık.  

Sorgu: SELECT TOP 5 WITH TIES orderid, productid, quantity
                                       FROM [order details] ORDER BY quantity DESC

 

Ders 20'ye Geçebilirsiniz.

 

18. TOP n Kullanımı

• Bazı durumlarda bir tablo veya sorgudaki tüm kayıtları görmek istemeyebiliriz. 
• Bir sonuç kümesindeki ilk n satırı veya tüm kayıtların belirli bir yüzdesini görüntüleyebilmek için TOP n anahtar kelimesini kullanırız.
• TOP n veya TOP n PERCENT anahtar kelimeleri ORDER BY  yan tümcesini ile birlikte kullanılmalıdır.
•  Aksi halde listelenen verilerde WHERE ifadesi ile belirtilen koşula uyan kayıtlar rastgele dizileceğinden, istediğimiz sonucu elde edemeyebiliriz.

 Söz Dizimi:  SELECT TOP n * FROM <Tablo Adı>

Örnek:Siparişlerimizi en çok sipariş sayısından azalacak şekilde ilk 10 tanesini listeleyelim.

Sorgu: SELECT TOP 10 * FROM [order details] ORDER BY quantity DESC 

Ders 19'a Geçebilirsiniz.

13. SQL ORDER BY

• ORDER BY yantümcesi satırları, belirtilen sütun(lar)a göre sıralamak için kullanılır.
• ORDER BY ile hangi sütuna göre sıralayacağımızı ve sıralamanın artan veya azalan şekilde yapılacağını belirleriz. 
• Sıralama yönü belirtilmez ise SQL veriyi artan şekilde sıralar.
• Sıralama yapılacak alanlar SELECT ifadesinde yer almak zorunda değildir.

Söz dizimi:    

            SELECT <sütun ad(lar)ı> FROM <tablo adı>

            ORDER BY <sütun adı><sıralama yönü>,<sütun adı><sıralama yönü>

(Bu örneğimizde CategoryID alanına göre artan ,bu CategoryID nin kendi içinde aynı değerler arasındada Unitprice değerini azalan olarak sıralandığını göreceğiz.) 

Sorgu: SELECT ProductID, ProductName,
CategoryID, UnitPrice
FROM Products
ORDER BY CategoryID ASC, UnitPrice DESC 

Ders-14 'e geçebilirsiniz.