SQL MAX MIN SUM İçin Farklı Alternatifler

Bu makalede bir tablodaki en yüksek veya en düşük veya aradaki kayıtları nasıl bulacağımız konusunda farklı alternatifleri örneklendireceğiz.
Aşağıdaki verileri içeren bir tablomuz olduğunu düşünelim.

Bu tabloda mağazaların 201005 ayındaki ciroları tarih bazında tutulmaktadır.

1. Örnek Bu tablodaki en yüksek ciro değerine sahip mağazaları bulacağız. Bunun için kullanabileceğimiz alternatifler şunlardır;
1. Yöntem : Bu tür sorgular için akla gelen ilk yöntem sub-query (içiçe sorgu) kullanmaktır. Yani önce en yüksek ciro değeri bulunur ardından ikinci SELECT içerisinde bu değer filtre olarak verilir.

SELECT location_id, amount FROM Deneme
WHERE amount=(SELECT MAX(amount) FROM Deneme)


Aynı şekilde en düşük ciro değerine sahip mağazalar için de MIN fonksiyonu kullanılır.

SELECT location_id, amount FROM Deneme
WHERE amount=(SELECT MIN(amount) FROM Deneme)

2. Yöntem : Diğer alternatif önceki makalede yazdığım WITH TIES yantümcenin kullanılmasıdır. Bu tümce aracılığıyla performans sorunu yaşatma ihtimali olan subquery kullanmadan en yüksek değere sahip tüm satırlar elde edilebilir.

SELECT TOP 1 WITH TIES location_id, amount
FROM Deneme ORDER BY amount DESC

3. Yöntem : Satırları ciro alanına göre numaralandırıp ona göre en düşük veya en yüksek numara filtre olarak verilerek te bu sorgu çözülebilir. Satır sırası için SQL Server 2005 ve üzerinde ROW_NUMBER() ve RANK fonksiyonları kullanılır. Bu fonksiyonların detaylı kullanımını SQL Server 2005’te yeni ne var – III [T-SQL] makalemde bulabilirsiniz. SQL Server 2000’de sıralama için hazır bir fonksiyon bulunmamaktadır. SQL Server 2005 tarafında aynı olan ciro satırlarının aynı satır numarasını alması için RANK fonksiyonu kullanılır. Ciroyu artan sıralarsak en düşük ciroyu azalan sıralarsak en yüksek ciroyu elde etmiş oluruz.

SELECT RANK() OVER(ORDER BY amount DESC) [RowNumber],
location_id, amount
FROM Deneme


Görüldüğü gibi en yüksek ciro olan 420 satırları 1 olarak işaretlendi. Bu kayıtlar üzerinden RowNumber=1 filtresini vermemiz yeterli olacaktır. Bu dinamik satırı WHERE bölümünden doğrudan çağıramadığımız için sorgunun sonucunu bir sub-select olarak kullanacağız.

--En yüksek ciro (MAX)
SELECT * FROM (
	SELECT RANK() OVER(ORDER BY amount DESC) [RowNumber],
	location_id, amount
	FROM Deneme
) as t WHERE RowNumber=1

--En düşük ciro (MIN)
SELECT * FROM (
	SELECT RANK() OVER(ORDER BY amount) [RowNumber],
	location_id, amount
	FROM Deneme
) as t WHERE RowNumber=1

SQL Server 2000’de RANK fonksiyonun yaptığı işi yapan bir fonksiyon bulunmamaktadır.
4. Yöntem : Son yöntem olarak SQL Server 2005 ile birlikte CTE (Common Table Expressions) yapısının kullanılmasıdır.

WITH cteMaxAmount AS (
	SELECT MAX(amount) max_amount FROM Deneme
)
SELECT * FROM Deneme a
	INNER JOIN cteMaxAmount b ON a.amount=b.max_amount

2. Örnek Önceki örnekte toplam cirosu en yüksek mağazayı değil tek seferde en yüksek ciroyu yapmış mağazaları bulmaya çalıştık. Toplam cirosu en yüksek mağaza veya mağazaları bulmak için bilindiği gibi mağaza koduna göre özetleme yapıp cirolar toplanır.

SELECT location_id, SUM(amount) amount FROM Deneme
GROUP BY location_id


Bu sorguya TOP 1 eklediğimizde toplam cirosu ek yüksek mağazayı elde etmiş oluruz.

SELECT TOP 1 location_id, SUM(amount) amount FROM Deneme
GROUP BY location_id

Aynı şekilde her mağazanın tarihe bakmaksızın en yüksek / en düşük cirosunu bulmak için MAX() ve MIN() fonksiyonları kullanılır. Öncelikle tabloyu mağaza ve ciroya göre sıralayalım.

SELECT location_id,date_id,amount FROM Deneme
ORDER BY location_id,amount DESC

SELECT location_id, MAX(amount) amount FROM Deneme
GROUP BY location_id 

SELECT location_id, MIN(amount) amount FROM Deneme
GROUP BY location_id


Örneğimizi biraz daha geliştirelim. Bu mağazalar bu ciroları hangi gün yapmış. Yani mağazaların yüksek ciro yaptığı gün(-leri) bulacağız. Yani aşağıdaki gibi birşey yazıyor olacağız.

SELECT * FROM Deneme
WHERE amount=MAX(amount)
GROUP BY location_id

Fakat bu şekilde bir yazım olmadığı için öncelikle mağazaların en yüksek ciroları bulacağız ardından tabloyu kendisiyle yeniden JOIN edip o ciro değerine sahip günleri bulacağız. Örnekte L1 mağazası en yüksek cirosunu (420) 1.günde yapmış. L2 mağazası en yüksek cirosunu (420) 9 ve 11’inde yapmış. L3 mağazası tek kayıt olup ayın 1’inde en yüksek cirosunu gerçekleştirmiş.

SELECT a.location_id,a.date_id,a.amount FROM Deneme a INNER JOIN
	(SELECT location_id, MAX(amount) amount FROM Deneme
	GROUP BY location_id) b
ON a.location_id=b.location_id AND a.amount=b.amount
ORDER BY 1,3

Öncelikle tabloyu mağazaya göre özetleyip her mağazanın en yüksek ciro tutarını bulduk ve sonuç listesini “b” olarak temsil ettik. Ardından ana tabloyu (a) bu türemiş tabloyla birleştirdik.

3. Örnek Önceki örneklerde hep en üst veya en alttaki (TOP N, BOTTOM N) kayıtlarını bulduk. Oysa bazı durumlarda aradaki kayıtlara ihtiyacımız olabilir. Örneğin ciroya göre 2.sırada olan mağazaları bulmak gibi.
Aradaki kayıtları bulmak için yani ciroya göre sıralama yapıp 2. veya 3. sırada olan mağazaları bulmak için doğrudan bir fonksiyon bulunmamaktadır. Bunun için kullanılacak yöntem satırlarda sıralamaya göre numara vermektir. SQL Server 2005 ve üstü için ROW_NUMBER() fonksiyonu kullanılır. ROW_NUMBER() fonksiyonunda PARTITION parametresi mağaza kodu için kullanarak her mağaza için 1’den başlayacak şekilde numaralandırmasını sağlayacağız. Ayrıca ORDER BY parametresinde ciroyu azalan olarak sıralaylıp en yüksek cironun başta olmasını talep edeceğiz. Daha sonra sogunun sub-query olarak kullanıp bu numara alanın 2-3 aralığına denk gelenleri alacağız.

SELECT * FROM (
	SELECT ROW_NUMBER() OVER(PARTITION BY location_id ORDER BY amount DESC)RowNumber,
	location_id, amount
	FROM Deneme
) as t WHERE RowNumber BETWEEN 2 AND 3

Böylece yüksek ciroya göre 2. veya 3. sırada olan mağazaları bulmuş olduk.

SQL MAX MIN SUM İçin Farklı Alternatifler” hakkında 5 yorum

  1. bilgehoca

    ahmet hocam çok teşekkür
    SELECT a.location_id,a.date_id,a.amount FROM Deneme a INNER JOIN
    (SELECT location_id, MAX(amount) amount FROM Deneme
    GROUP BY location_id) b
    ON a.location_id=b.location_id AND a.amount=b.amount
    ORDER BY 1,3 örneğinde inner join in farklı bır kullanımını görerek şeklinde sorun çözüldü….:)

    SELECT kitapadi from kitap INNER JOIN
    (SELECT top 1 kitapno, count(*) amount FROM islem
    GROUP BY kitapno order by amount desc) b
    ON b.kitapno=kitap.kitapno

    Cevapla

sedat için bir cevap yazın Cevabı iptal et

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir