Diyelim ki sitenizde bazı makaleler yayımlıyor ve en çok okunan makaleleri görmek istiyorsunuz..
Bunu basit bir sql cümlesi ile nasıl yaparız?
Birkaç yöntemi olmakla birlikte SQL SERVER 2005 ile gelen Rank() ve Dense_Rank() fonksiyonlarından bahsetmek istiyorum..
Sıralama (Ranking) Fonksiyonları (Rank(),Dense_Rank(),Row_Number() ):
[ranking_func_name] OVER ([partition_by_clause] order_by_clause)
1. ROW_NUMBER() fonksiyonu:
Her satır için 1 den başlayarak ardışık numara üretir. Her satır için farklı sayı ürettiğinden aynı gruplama içerisinde bir sayı sadece bir kez geçer.
Örnek Yazım şekli:
SELECT
ROW_NUMBER() Over( ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma
2. RANK() fonksiyonu:
Verilen sıralamaya göre aynı değere sahip satırlar, aynı sıra numarasını alırlar. En sonuncu sıra numarası, kayıt sayısına eşittir.
Örneğin "Bir yarışmaya 100 kişi katılır, 100 kişiden 99 u aynı puanı alırsa, 99 kişi 1. olurken, diğer kişi 100. (sonuncu) olur." şeklindeki bir sıralamayı hesaplamak için kullanılır.
Örnek yazım şekli:
SELECT
Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore
FROM @Okunma
3. DENSE_RANK() fonksiyonu:
Rank fonksiyonu gibi çalışır ancak üretilen rakamlar arasında boşluk olmaz.
Örneğin "Bir yarışmaya 100 kişi katılır, 100 kişiden 99 u aynı puanı alırsa, 99 kişi 1. olurken, diğer kişi 2. olur." şeklindeki bir sıralamayı hesaplamak için kullanılır.
Örnek yazım şekli:
SELECT
DENSE_RANK() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
DENSE_RANK() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore
FROM @Okunma
partition_by_clause
Fonksiyonun uygulandığı listeyi gruplara bölmek için kullanılır. Kullanılan sıralama fonksiyonu partition by da yazılan her gruba ayrı ayrı uygulanır ve grup değişiminde fonksiyon hesaplamaya en baştan başlar, sadece bu grup için yapar. Partition by ifadesi yazılmazsa veya "partition by null" yazılırsa tüm kayıt seti için fonksiyon çalışır.
order_by_clause
Sıralama fonksiyonlarının hangi sıraya göre hesaplanacağını burada ifade ederiz.
Aşağıdaki örnek de,makale adına göre bir sıra numarası vermek istersek
SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma
Aşağıdaki tüm sql cümlesini kopyalayıp çalıştırırsanız, hafızade bir tablo yaratıp içerisine örnek veriler ekleyecek ve istediğimiz sorguları çalıştıracaktır
DECLARE @Okunma TABLE
(MakaleID int, MakaleAdi varchar(100), Tur varchar(10),OkunmaSayisi int)
-- Örnek verileri ekleyelim
INSERT INTO @Okunma(MakaleID,OkunmaSayisi,Tur)
SELECT 1,10,'SQL'
UNION SELECT 2,10,'ASP'
UNION SELECT 3,15,'SQL'
UNION SELECT 4,10,'ASP'
UNION SELECT 5,30,'ASP'
UNION SELECT 6,18,'ASP'
UNION SELECT 7,10,'SQL'
UNION SELECT 8,30,'SQL'
UNION SELECT 9,20,'SQL'
UNION SELECT 10,20,'ASP'
UNION SELECT 11,9,'ASP'
-- Makale isimlerini oluşturalım...
UPDATE @Okunma Set MakaleAdi = 'Makale_' + CAST(MakaleID as varchar)
-- Tüm makelelerin listesi
SELECT * FROM @Okunma ORDER BY Tur,MakaleID
SELECT Tur,MakaleAdi,OkunmaSayisi,
Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore,
Dense_Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu_2,
Dense_Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore_2
FROM @Okunma
ORDER BY Tur, Siralama_TureGore
SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY OkunmaSayisi DESC,Tur ) as Row_Okunma
FROM @Okunma
-- SQL Script end
Bu sql cümlelerini çalıştırdığınızda 3 farklı resultset göreceksiniz.
1.@Okunma tablosuna eklediğimiz tüm kayıtlar :
| MakaleID | MakaleAdi | Tur | OkunmaSayisi |
| 2 | Makale_2 | ASP | 10 |
| 4 | Makale_4 | ASP | 10 |
| 5 | Makale_5 | ASP | 30 |
| 6 | Makale_6 | ASP | 18 |
| 10 | Makale_10 | ASP | 20 |
| 11 | Makale_11 | ASP | 9 |
| 1 | Makale_1 | SQL | 10 |
| 3 | Makale_3 | SQL | 15 |
| 7 | Makale_7 | SQL | 10 |
| 8 | Makale_8 | SQL | 30 |
| 9 | Makale_9 | SQL | 20 |
2. Okunma sayısına göre makalelerin sıralanması :
| Tur | MakaleAdi | OkunmaSayisi | Siralama Tumu | Siralama TureGore | Siralama Tumu_2 | Siralama TureGore_2 |
| ASP | Makale_5 | 30 | 1 | 1 | 1 | 1 |
| ASP | Makale_10 | 20 | 3 | 2 | 2 | 2 |
| ASP | Makale_6 | 18 | 5 | 3 | 3 | 3 |
| ASP | Makale_4 | 10 | 7 | 4 | 5 | 4 |
| ASP | Makale_2 | 10 | 7 | 4 | 5 | 4 |
| ASP | Makale_11 | 9 | 11 | 6 | 6 | 5 |
| SQL | Makale_8 | 30 | 1 | 1 | 1 | 1 |
| SQL | Makale_9 | 20 | 3 | 2 | 2 | 2 |
| SQL | Makale_3 | 15 | 6 | 3 | 4 | 3 |
| SQL | Makale_7 | 10 | 7 | 4 | 5 | 4 |
| SQL | Makale_1 | 10 | 7 | 4 | 5 | 4 |
Siralama Tumu kolonu : "Rank() Over(PARTITION BY NULL ORDER BY OkunmaSayisi DESC) as Siralama_Tumu" kodu ile oluşturuldu.
Makale_5 ve Makale_8 isimli makaleler 30 hit alarak en çok okunan 1. makale olmayı başarmışlar.
Rank ifadesini biraz daha açalım..
Partition by null ile, sayıları tekrar başlatmak istediğimiz herhangi bir kolon olmadığını, ilgilendiğimiz tüm kayıtlar için sıralama yapılmasını istediğimizi ifade ediyoruz.
Sıralamanın neye göre yapılacağı bilgisi ise "ORDER BY" ifadesinden sonra yazdığımız alan isimleri ile belirtiliyor. Biz "OkunmaSayisi" alanındaki verileri dikkate alacağız ancak en çok okunan makalenin daha üste gelmesini istediğimiz için sıralamanın büyükten küçüğe yapılmasını istediğimizi DESC ifadesini de ekleyerek söylüyoruz.. Buradaki "ORDER BY" ifadesinin yazımı ve anlamı select cümlesinde kullandığımız order by ile aynı... Birden fazla alana göre sıralama yapmak istersek, kolon isimlerini "," ile ayırarak yan yana yazabiliriz..
3. Satır sayısı:
| Tur | MakaleAdi | OkunmaSayisi | Row_Okunma |
|---|---|---|---|
| ASP | Makale_5 | 30 | 1 |
| SQL | Makale_8 | 30 | 2 |
| ASP | Makale_10 | 20 | 3 |
| SQL | Makale_9 | 20 | 4 |
| ASP | Makale_6 | 18 | 5 |
| SQL | Makale_3 | 15 | 6 |
| ASP | Makale_4 | 10 | 7 |
| ASP | Makale_2 | 10 | 8 |
| SQL | Makale_1 | 10 | 9 |
| SQL | Makale_7 | 10 | 10 |
| ASP | Makale_11 | 9 | 11 |
ROW_NUMBER() fonksiyonu:
Aşağıdaki örnek de, ilk 8 makale için, makale adına göre bir sıra numarası vermek istiyoruz.. İlk kolonda (Row_No_Tumu) de 1 den 8 kadar makale adına göre sıralı numara üretmesi için "partition by null" yazıyoruz. Row_No_TureGore kolonunda ise, her 2 tür içerisinde yine makale adına göre sıralı bir numara üretmesini istiyoruz. ancak her tür için numarayı 1 den başlatmasını da istiyoruz..
SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma
Örnek sonuç :
| Tur | MakaleAdi | Okunma Sayisi | Row_No Tumu | Row_No TureGore |
| SQL | Makale_1 | 10 | 1 | 1 |
| ASP | Makale_2 | 10 | 2 | 1 |
| SQL | Makale_3 | 15 | 3 | 2 |
| ASP | Makale_4 | 10 | 4 | 2 |
| ASP | Makale_5 | 30 | 5 | 3 |
| ASP | Makale_6 | 18 | 6 | 4 |
| SQL | Makale_7 | 10 | 7 | 3 |
| SQL | Makale_8 | 30 | 8 | 4 |
Makale_2 isimli makale tüm sıra numarasında 2 numarayı alırken, türe göre de 1 numarasını alıyor. Makale_2 den önce gelen Makale_1 başka bir türde yazılmış bir makale..
Hiç yorum yok:
Yorum Gönder