11 Farklı Yöntem ile SubQueries – Alt Sorgular

Merhaba arkadaşlar bu yazımda sizlere SubQueries (Alt Sorgular)’ den bahsedeceğim.

Alt sorgular kısaca parantezler kullanarak sorgu içerine yerleştirilmiş ek sorgulardır desek yanlış olmaz. Temel alt sorguların kullanımı kolaydır ancak içinde barındırdığı SQL sorgularının karmaşıklığı nedeniyle alt sorguyu geliştirmesi zorlaşabilir. Alt sorgular soyut olarak bir veritabanı kavramından ziyade bir analiz tekniğidir.

Şimdi sizlere 11 başlık altında alt sorguların kullanımını göstereceğim.

1-) Veri Sonuçlarına Yeni Alanlar Ekleme

Verileri analiz ederken bazı durumlarda verinin bir bölümünün veri seti ile nasıl ilişkili olduğunu görmek istediğimiz durumlar vardır. Bu gibi durumlarda aşağıdaki gibi iç içe sorgu ile istediğimiz verileri tek bir tabloda görüntüleyebiliriz.

SELECT CO.CountryName
,COUNT(SD.StockID) AS CarsSold
,(SELECT COUNT(SalesDetailsID)
FROM Data.SalesDetails) AS SalesTotal
FROM Data.SalesDetails SD
INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID
INNER JOIN Data.Customer CU ON SA.CustomerID = CU.CustomerID
INNER JOIN Data.Country CO ON CU.Country = CO.CountryISO2
GROUP BY   CO.CountryName

Bu sorguda ilk olarak ülke başına satılan araba sayısını saydırıp, ikinci adımda ise tüm ülkelere yapılan toplam satış sayısını saydırıp getirdik.

Böylece ülkede yapılan satış ile toplam yapılan satış arasında görsel bir kıyaslama yapma imkânı sağladık.

2-) Değeri Toplamın Yüzdesi Olarak Görüntüleme

Hesaplamalar ve bir bütünün yüzdelerini görüntülemek veri analizinin temellerini oluşturur. Yapacağımız bu örnekte satılan araba markalarının satış listesini getirecek ve yanlarına da her bir araç için yapılan satışın genel toplamdaki yüzdesini göstereceğiz.

SELECT     MK.MakeName
,SUM(SD.SalePrice) AS SalePrice
,SUM(SalePrice) / (SELECT SUM(SalePrice)
FROM Data.SalesDetails) * 100 AS SalesRatio
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
GROUP BY   MK.MakeName

Bu sorguda satılan her aracın (Marka, model, stok ve satış detayları) bilgilerini birleştirir. Daha sonrasında marka başına toplam satış fiyatını SUM operatörüyle getirir. Son olarak da marka başı toplam satış fiyatını tüm araçların toplam satış fiyatına bölünmesiyle oluşan SalesRatio sütununu hesaplar ve bize getirir.

Bu çıkan sonuç ile markaların genel satış toplamını ve bu satışın tüm markalar ile kıyaslanmasının sonucunu görüntüleyebiliriz.

3-) Verileri Filtrelemek İçin Alt Sorgu Kullanma

Bu aşamada satılan en pahalı aracın rengini tespit edeceğiz.

SELECT ST.Color
FROM Data.Stock AS ST
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
WHERE SD.SalePrice = (SELECT MAX(SalePrice) FROM Data.SalesDetails)

Bu kod bloğu ile öncelikle satılan en pahalı aracı, satış fiyatı bilgilerini kullanarak buluyoruz. Daha sonra bu aracın ID değerini alarak StockCode kısmındaki aynı ID değere sahip olan aracı buluyoruz ve bulduğumuz aracın rengini sonuç olarak istiyoruz.

Sorgumuzun sonucunda satış yapılan en pahalı aracın rengi ‘British Racing Green’ olarak getirildi.

4-) Verileri Filtrelemek İçin Hesaplamanın Bir Parçası Olarak Bir Alt Sorguyu Kullanma

Bu sorguda örnek olarak bir maliyet sınırlaması çalışması yapacağız. Amacımız stoktaki tüm araçların onarım maliyetini genel ortalama ile kıyaslayarak ortalamanın epeyi üzerinde (3x)  masrafa sahip araçları tespit etmek.

SELECT MK.MakeName, MD.ModelName, ST.RepairsCost
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
WHERE ST.RepairsCost > 3 * (SELECT AVG(RepairsCost) FROM Data.Stock)

Öncelikle ortalama onarım masrafını hesaplıyoruz. Daha sonrasında bu ortalamayı 3 ile çarpıyoruz. Ve çıkan sonucun üzerinde kalan marka ve modelleri listeliyoruz.

Sonuç olarak ortalama onarım maliyetinin 3 katı onarım maliyeti olan marka ve modellerin listesi.

5-) Birden Çok Alt Sorgu Kullanarak Veri Aralığında Filtreleme

Bu sorgu tekniğinde birden fazla alt sorgu kullanarak; ürünlerin onarım maliyetlerinin tüm stoklar için ortalama onarım maliyetlerinin -%10 – +%10 aralığında olan marka ve modelleri listeleyeceğiz.

SELECT MK.MakeName, MD.ModelName, ST.Cost, ST.RepairsCost
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
WHERE RepairsCost BETWEEN
(SELECT AVG(RepairsCost) FROM Data.Stock) * 0.9
AND
(SELECT AVG(RepairsCost) FROM Data.Stock) * 1.1

Bu sorguda diğerlerinden farklı olarak BETWEEN … AND operatörünü kullandım. Çünkü istediğimiz sonuç ortalama onarım maliyetlerinin -%10 – +%10 aralığında olan marka ve modeller.

Data.Make tablomuzdan ürün marka ve model isimlerini çekiyoruz. Data.Stock tablomuzdan da ürün fiyatı ve onarım fiyatını çekiyoruz. Daha sonra where komutu altında alt sorgu ile istediğimiz ortalama onarım maliyeti aralığını da çekip bunu bir tablo halinde listeliyoruz.

Ürün marka ve model isimleri ile beraber ortalama onarım ücreti  + – %10 olan ürünlerin tablosu.

6 – ) İkinci Bir Toplama Kullanarak Çıktıyı Filtreleme

Bu teknikte ortalama satış fiyatı , genel ortalama satış fiyatının 4 katı olan ürünleri listeleyeceğiz.

SELECT MK.MakeName, AVG(SD.SalePrice) AS AverageUpperSalePrice
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
GROUP BY MK.MakeName
HAVING AVG(SD.SalePrice) > 4 * (SELECT AVG(SalePrice)
FROM Data.SalesDetails)

Öncelikle her marka için ortalama satış fiyatını hesaplattırdık. Daha sonra alt sorgu içerisinde tüm araçlar için ortalama satış fiyatını hesaplattırdık ve son olarak yine aynı alt sorguyu 4 ile çarparak satılan tüm markaların marka bazındaki ortalama satış fiyatıyla karşılaştırttık.

Sonuç olarak ‘Bugatti’ ve ‘McLaren’ markaları ortalama satış fiyatının 4 katından daha fazla ücretlere satılmakta olduğu bilgisine ulaştık.

7 – ) Verileri Filtrelemek İçin Bir Alt Sorgudan Birden Çok Sonuç Çıkarmak

Bu teknikte en çok satan ilk 2 markanın tüm satışlarını listeleyeceğiz.

SELECT MKX.MakeName, SDX.SalePrice
FROM Data.Make AS MKX
INNER JOIN Data.Model AS MDX ON MKX.MakeID = MDX.MakeID
INNER JOIN Data.Stock AS STX ON STX.ModelID = MDX.ModelID
INNER JOIN Data.SalesDetails SDX ON STX.StockCode = SDX.StockID
WHERE MakeName IN (
SELECT  TOP (2) MK.MakeName
FROM  Data.Make AS MK
INNER JOIN Data.Model AS MD
ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST
ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD
ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales SA
ON SA.SalesID = SD.SalesID
GROUP BY  MK.MakeName
ORDER BY  SUM(SA.TotalSalePrice) DESC)
ORDER BY  MKX.MakeName, SDX.SalePrice DESC

Bu sorgunun alt sorgusunda en çok satan 2 markayı buluyoruz.

Bu sonuç sadece alt sorgu çalıştırıldığında çıkan en çok satılan 2 markanın listesi.

Sorgunun tamamını çalıştırdığımızda bu 2 markanın tüm satışlarını listelemiş oluyoruz.

 Bu sorgu sonucu 106 satırlık sonuç geliyor fakat uzun olmaması için küçük bir kısmını buraya koyuyorum. Gördüğünüz tablo en çok satış yapan 2 markanın tüm satışlarının listesi.

8 – ) Karmaşık Alt Sorgular

Bu teknikte hangi markaların en çok satış sağladığını ve özellikle en çok satış yapan ilk üç markanın kaç araba sattığını listeleyeceğiz.

SELECT MK.MakeName
,COUNT(MK.MakeName) AS VehiclesSold
,SUM(SD.SalePrice) AS TotalSalesPerMake
FROM  Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
WHERE  MakeName IN (
SELECT TOP (3) MK.MakeName
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD
ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST
ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD
ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales AS SA
ON SA.SalesID = SD.SalesID
GROUP BY  MK.MakeName
ORDER BY COUNT(MK.MakeName) DESC)
GROUP BY  MK.MakeName
ORDER BY  VehiclesSold DESC

Bu kod bloğunun alt sorgusunda en çok satış yapan ilk 3 marka listeleniyor. Sadece alt sorgu kısmını çalıştırdığımızda çıkan sonuç şu şekilde oluyor .

Daha sonra alt sorgudan gelen bu çıktıya ilaveten ilgili markaların yaptığı toplam satış ekleniyor ve çıktımız son halini alıyor.

9 –  ) İç İçe Alt Sorgular

Bazı sorunları yanıtlamak için daha karmaşık sorgulamalar gerekebilir. Bu gibi durumlarda alt sorguların içine alt sorgu ekleyerek bir çözüm bulabiliriz. Bu teknikte iç içe birden fazla alt sorgu kullanacağız. Yapacağımız örnekte önce satılan en pahalı aracı daha sonra rengini ve son olarak da bu renge sahip olan en pahalı 5 aracı listeleyeceğiz.

SELECT TOP 5 MK.MakeName, MD.ModelName, SD.SalePrice
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID
WHERE Color IN (SELECT     ST.Color
FROM Data.Model AS MD
INNER JOIN Data.Stock AS ST
ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD
ON ST.StockCode = SD.StockID
WHERE SD.SalePrice =
(SELECT MAX(SD.SalePrice)
FROM Data.SalesDetails SD
INNER JOIN  Data.Sales SA
ON SA.SalesID = SD.SalesID))

İlk adım olarak iç içe olan alt sorguyu çalıştırarak en pahalı arabanın fiyatını buluyoruz.

Daha sonra alt sorgu ile bu fiyata sahip olan arabanın rengini buluyoruz.

Son olarak dış sorgu ve alt sorguları birlikte çalıştırarak bu renge sahip en pahalı 5 arabayı listeliyoruz.

10 – ) Verileri Hariç Tutmak İçin Alt Sorguları Kullanma

Bu teknikte alt sorguyu kullanarak tablomuzda istemediğimiz verileri nasıl çıkartabileceğimizi öğreneceğiz. Uygulayacağımız örnekte öncelikle alt sorgu içerisinde en pahalı satışların yapıldığı 4 ülkeyi belirleyecek ve NOT IN operatörüyle bu 4 ülkeyi sorgulama yapacağımız tablodan çıkartıp kalan ülkeler için yapılan satışları marka ve toplam satış olarak listeleyeceğiz.

SELECT MK.MakeName, SUM(SA.TotalSalePrice) AS TotalSales
FROM  Data.Make AS MK INNER JOIN Data.Model AS MD
ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID
INNER JOIN Data.Customer CU ON SA.CustomerID = CU.CustomerID
INNER JOIN Data.Country CO ON CU.Country = CO.CountryISO2
WHERE CountryName NOT IN (
SELECT TOP 4 CO.CountryName
FROM Data.Sales AS SA
INNER JOIN Data.Customer CU
ON SA.CustomerID = CU.CustomerID
INNER JOIN Data.Country CO
ON CU.Country = CO.CountryISO2
GROUP BY CountryName
ORDER BY SUM(SA.TotalSalePrice) ASC)

 İlk olarak alt sorgu içerisinde toplamda en pahalı satış yapılan 4 ülkeyi buluyoruz.

Daha sonra NOT IN Operatörünü de kullanarak bu 4 ülkeyi saf dışı bırakarak kalan ülkeler için marka bazında toplam satışı listeliyoruz.

11 – ) Sorgular Ve Alt Sorgular Arasında Filtreleme

Bu teknikte dış sorgu ve alt sorguya WHERE komutuyla filtreleme yapacak ve dış sorgu ile alt sorgu sonuçlarını karşılaştıracağız. Örnek olarak alt sorgu içerisinde 2015 yılında yapılan toplam satışı bulacağız ve sonrasında tüm markaların 2015 yılında yaptıkları satışın toplam satışa göre oranını hesaplattıracağız.

SELECT MK.MakeName
,SUM(SD.SalePrice) AS SalePrice
,SUM(SD.SalePrice) /
(SELECT SUM(SD.SalePrice)
FROM Data.SalesDetails SD
INNER JOIN Data.Sales AS SA
ON SA.SalesID = SD.SalesID
WHERE YEAR(SaleDate) = 2015) AS SalesRatio
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
INNER JOIN Data.Sales AS SA ON SA.SalesID = SD.SalesID
WHERE YEAR(SA.SaleDate) = 2015
GROUP BY MK.MakeName

Öncelikle alt sorgu içerisinde 2015 yılında yapılan toplam satışı buluyoruz.

Daha sonrasında 2015 yılında markaların yaptığı satışların genel toplama göre oranını tablo halinde getiriyoruz.

 Çeşitli tekniklerle SubQueries konusunu anlatmaya çalıştım umarım faydalı olmuştur. Bir sonraki yazıda görüşmek üzere.