SQL deyimlerini öğrenmeye kaldığımız yerden devam edelim. Bu yazıda SQL'de veriler üzerinde kolay işlem yapmamızı sağlayan hazır fonksiyonlardan bahsedeceğiz. Öncelikle PL/SQL'de fonksiyonların nasıl ayrıldığını kısaca inceleyeceğiz. Ancak konumuz SQL olduğu için PL/SQL fonksiyonlarını ayrıntılı olarak uygulama yaparak işlemeyeceğiz. Daha sonra SQL fonksiyonlarını -COUNT, AVG, MIN, MAX- ayrıntılı olarak inceleyeceğiz.
Fonksiyon Kavramı
Programlama ile uğraşanların haşır neşir olduğu bu kavram oradaki anlamıyla eş bir kullanıma sahiptir. Yani fonksiyonunuz argüman veya argümanlar alır, onu amacınız doğrultusunda işler ve size bir sonuç döndürür. SQL'de de bu fonksiyonları çok sık kullanacaksınız. Fonksiyonlar farklı sayılarda sonuç döndürmelerine göre iki şekilde incelenirler:
- Satır başına bir sonuç döndüren fonksiyonlar: Çekilen her satır için bir sonuç değeri üretir. Yani üzerinde işlem yapılan her satır için bir sonuç üretip bunu geriye döndürürler. Argümanlar sütun ismi yada sizin belirlediğiniz bir ifade olabilir. Bu tip fonksiyonlar iç içe yani fonksiyon1(fonksiyon2(...)) şeklinde kullanılabilirler. Yazı içerisinde tüm fonksiyonları vermeyeceğim. Bu fonksiyonlardan kimini belki de hiç kullanmayacaksınız. Şimdi bu fonksiyon başlığı altındaki fonksiyon çeşitlerini bir görelim:
- Karakter fonksiyonları: Verilen karakterler üzerinde düzenleme işlemleri yaparlar.
- Nümerik fonksiyonlar: Sayısal veriler üzerinde; mod alma, sayıyı bir üst yada bir alt değere yuvarlama gibi temel işlemler yaparlar.
- Tarih-saat fonksiyonları: Tarih verileri üzerinde, iki tarih arasındaki zaman dilimini bulma, tarih yuvarlama, o aydaki son günü bulma gibi çok da zaruri olmayan bilgileri işlerler :) Bizim düzeyimizde olan birisi için bu fonksiyonların çok da zaruri olmadığını düşünüyorum.
- Tip dönüşüm fonksiyonları: Diğer fonksiyonlara nazaran biraz daha kullanışlı olduğu söylenebilir. TO_CHAR(), TO_NUMBER(), TO_DATE() fonksiyonları olarak karşımıza çıkar.Şunu da biliyoruz ki bazen veri tipi dönüşüm işlemleri bizim isteğimiz dışında sağlanır. Bu durum kapalı veri tipi dönüşümüne bir örnektir. Ancak dönüşümleri mauel olarak bu fonksiyonlarla sağlarsak açık veri tipi dönüşümü yapmış oluruz. Örneğin Oracle karakter veri tipindeki (CHAR) bir veriyi otomatik olarak tarih(DATE) yada sayı(NUMBER) veri tipine kapalı olarak dönüştürür. Yine bu işlemleri tersten de uygulayabilir.
- Satır kümeleri için bir sonuç döndüren fonksiyonlar(Grup Fonksiyonları): Birden çok satır için tek bir sonuç döndürür. Mesela bir departmandaki çalışanların maaşlarının ortalamasını bulan bir fonksiyon düşünebilirsiniz. Tüm çalışanlar için tek bir sonuç dönecektir. Grup fonksiyonları NULL değerleri görmezden gelir yani işlemlere sokmazlar. Bu fonksiyonları ayrıntılı olarak vermek istiyorum. Çünkü bunları sık sık kullanmanız gerekecektir:
- AVG(): Aldığı argümanın (sütun ismi) aritmetik ortalamasını hesaplar. Sayısal verilerle çalışır.
- COUNT(): Aldığı argümana ait verilerin sayısını döndürür. COUNT(*) ile kullanılırsa tüm sütundaki NULL değerler de dahil tüm satırları sayacaktır. Tekrar eden verileri saymak istemezseniz COUNT(DISTINCT sayilacakveri) şeklinde kullanabilirsiniz.
- SUM(): Herhangi bir sütundaki sayısal değerlerin toplamını döndürür.
- MAX() ve MIN(): Bir sütundaki en büyük ve en küçük değerleri döndürür. Sayısal veriler de dahil olmak üzere tarih, karakter veri tipleri ile de çalışır.
Fonksiyonlar hakkında bu şekilde kısaca bilgi verilebilir. Ancak şunu belirteyim ki fonksiyonlar ayrıca bir konudur. Fakat burada uzun uzun bahsetmenin şimdilik anlamsız olacağını düşünüyorum ki bu fonksiyonların pek çoğunu hiç kullanmayacaksınız. Yeri geldiğinde kullanırsak o fonksiyon hakkında açıklama yaparız. Yine kontrol etmek isterseniz BURADAN fonksiyonların listesine ve içeriğine erişebilirsiniz. Yazının girişinde bahsettiğim üzere satır başına bir sonuç döndüren fonksiyonlar PL/SQL'e dair özel fonksiyonlardır. Bizim grup fonksiyonlarıyla ilgilenmemizin sebebi ise SQL fonksiyonları olmalarıdır. Şimdilik grup fonksiyonlarını bilmeniz yeterli gibi. Bunlarla ilgili örneklere geçelim:
employees tablosundaki tüm çalışanlara ait; en yüksek, en düşük maaşları getiren, ayrıca bu maaşların toplamını ve aritmetik ortalamasını isteyen bir sorgu hazırlayalım:
SELECT MAX(salary) "En Çok",
MIN(salary) "En Az",
SUM(salary) "Toplam",
ROUND(AVG(salary), 0) "Ortalama"
FROM employees;
En Çok En Az Toplam Ortalama
---------------------- ---------------- ------------ ----------
24000 2100 691416 6462
1 rows selected
Temel bir sorguyla tüm fonksiyonların kullanımını görmüş olduk. Buradaki ROUND() fonksiyonuna gelince; iki argüman alır. ROUND(yuvarlanacaksayi, basamaksayisi) şeklinde olan bu argümanlardan verilen sayının ondalık kısmı yakın olan tam kısma yuvarlanır. Örneğin burada AVG() işleminin sonucu 6461,8 çıktığı için 6462'ye yuvarlanır. Ayrıca basamak sayısı 0 olarak verildiği için virgülden sonra 0 basamak gösterilir. Yani hiç gösterilmez.
Gruplama:
İşlemleri şimdiye kadar hep tüm tabloyu baz alarak gerçekleştirdik. Ancak bazı durumlarda tablodaki departman başına yada çalışanların çalıştığı bölge başına gibi özel gruplarla çalışılmak istenebilir. Bu gibi durumlarda GROUB BY ifadesi devreye girer.
Örneğin az önceki sorguda tüm maaşların ortalamasını aldık. Bunu her bölümü kendi içinde gruplandırarak her grubun kendi içinde ortalamasını bulmak isteseydik sorguya bir kaç ifade eklememiz gerekecekti:
SELECT department_ıd,
ROUND(MAX(salary),0) "En Yüksek",
ROUND(AVG(salary),0) "Ortalama"
FROM employees
GROUP BY department_ıd;
DEPARTMENT_ID En Yüksek Ortalama
---------------------- ---------------------- -------------------
100 12008 8601
30 11000 4150
7000 7000
20 13000 9500
...
12 rows selected
Bakacak olursak; tabloda 12 adet bölüm olduğu da göz önünde bulundurulursa gruplama mantığı anlaşılıyor. Normalde grup fonksiyonlarının grup başına 1 sonuç döndürdüğünü söylemiştik. Burada grup sayısı 12'ye çıktığı için sonuç sayısı da otomatik olarak 12 oluyor. Şuna dikkat edilmelidir ki grup fonksiyonu argümanı olarak kullanılan sütun ismi gruplandırma ifadesi olarak kullanılamaz. Yukarıda da salary ifadesinin gruplandırma olarak kullanılmadığı gibi. Şimdi SELECT deyimimizin geldiği son evreyi görelim:
SELECT sütunismi, grupfonksiyonu(sütun)
FROM tabloismi
[WHERE sart]
[GROUP BY gruplamasarti]
[ORDER BY sütunismi];
GROUP BY ile birden çok sütuna göre de gruplandırma yapabilirsiniz. Şimdi COUNT fonksiyonunun kullanımı gruplandırma ile birlikte görelim.
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
JOB_ID COUNT(*)
---------- ----------------------
AC_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
...
19 rows selected
Bakacak olursak; tabloda bulunan tüm mesleklere göre yapılan bir gruplandırma görüyoruz. Sağ kısımdaki COUNT(*) sütunu bize o meslek çatısı altında kaç çalışanın bulunduğunu gösteriyor. Yani bu kısımdaki tüm değerlerin toplamı 107 yapacaktır. Çünkü tabloda toplamda 107 çalışan kaydı mevcuttur. Şimdi gruplandırma ifadesine bir de bölüm ID'sini ekleyerek sonuca bakalım:
SELECT department_ıd, job_id, COUNT(*)
FROM employees
GROUP BY job_id, department_ıd;
DEPARTMENT_ID JOB_ID COUNT(*)
---------------------- ---------- ----------------------
60 IT_PROG 5
100 FI_MGR 1
30 PU_MAN 1
...
SA_REP 1
...
20 rows selected
Görüldüğü gibi bu kez sonuç sayısı 20'ye çıkmıştır. Yine COUNT(*) sütunundaki değerler toplamı 107 yapacaktır. Burada şunu anlıyoruz ki GROUP BY ifadesi NULL değerleri de dikkate almaktadır.Sonuçlar da iki değere göre gruplandırılmıştır.
Tüm departmanlarda çalışan kişilere ait toplam maaşları departman ve o departmandaki iş bazında göstermek ve sonuçları da bölüm ID'lerinin artan sıralaması ile görmek istersek:
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id;
DEPARTMENT_ID JOB_ID SUM(SALARY)
---------------------- ---------- ----------------------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
...
20 rows selected
Bakacak olursak department_id terim olarak job_id'yi kapsar. Yani bir departmanda birden fazla iş bölümü olabilir. Bu yüzden job_id kısmında bir tekrarlama görülmez. Ancak department_id ise job_id sayısına göre kendi içinde gruplandırılır fakat bu tekrarlı olarak gözlenir. Mesela 20 nolu bölümdeki MK_MAN işinde çalışanların toplam maaşının 13000 ve yine aynı bölümde çalışan MK_REP çalışanlarının da toplam maaşının 6000 olduğu şeklinde bir yorum getirilir.
Gruplandırma Problemleri
GROUP BY ifadesini kullanırken bazı sıkıntılarla karşılaşabilirsiniz. Bunları şimdiden görmek yanlış kullanımlarınızın önüne geçecektir.
SELECT department_id, COUNT(last_name)
FROM employees;
Bu sorgunun doğru çalışacağını düşünüyor musunuz? Hayır, aşağıdaki hatayı verecektir:
"tek gruplu bir grup fonksiyonu değil"
COUNT bildiğiniz gibi gruplandırmalar ile düzgün olarak çalışan bir fonksiyondur. Yani burada bir gruplama olmadan düzgün bir çalışma söz konusu olamaz. Aynı şekilde gruplandırma SELECT deyiminde var olan department_id ile yapılmalıdır. Farklı bir ifade ile yapılırsa yine hata alacaksınız. Sorgunun düzgün şekli şöyle olmalı:
SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_ıd;
Tahmin edeceğiniz gibi bölümlerdeki bütün soy isimlerini bölümlere ayırarak sayacaktır.
FROM employees
GROUP BY department_id;
Bu sorgunun sıkıntısı da çok farklı değildir. job_id'yi SELECT'e ekleyerek sonuçlarda job_id sütununun da görüntülenmesini talep ediyoruz. Ama gruplandırmada bu sütunu belirtmediğimiz için sonuçlar birbiri ile uyumsuz ve anlamsız olacaktır. Bu da bize hata olarak geri dönüyor. Bu hatayı elimine etmek için iki yol vardır. Ya job_id SELECT deyiminden tamamen kaldırılır, yada GROUP BY kısmına ikinci bir grup şartı olarak eklenir.
Gruplu İfadelerde Şart İşlemi
Gruplandırmalı sorgularda WHERE'i şart olarak kullanamazsınız. Hata olarak geri dönecektir. Bu gibi durumlarda sorgu üzerinde şartlı işlemler yapmak istediğinizde HAVING ifadesini kullanmalısınız. Örneğin her iş bölümünde çalışanların en küçük maaşı 8000'den büyük olan bölümlerde çalışanların en yüksek maaşlarını bulmak istersek şu sorgu işimizi görür.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>8000 ;
DEPARTMENT_ID MAX(SALARY)
---------------------- ----------------------
70 10000
90 24000
110 12008
3 rows selected
Anladığımız şu olmalıdır: Tablo üzerinde en küçük maaşı 8000'den fazla olan sadece 3 bölüm bulunuyor. Bu 3 bölümün en yüksek maaşları ise tabloda görüldüğü gibidir. Kullanım mantığı WHERE ile tamamen aynı olup tek farkı HAVING olarak kullanmamızdır. Bir sorguda hem HAVING hem de WHERE şartları kullanılabilir. Bununla ilgili şöyle biraz karmaşık bir sorgu tasarlayabiliriz.
'MK_MAN', 'MK_REP' ve 'ADD_AST' işlerinde çalışmayan çalışanları iş ID'lerine göre gruplandırarak bu işlere ait ortalama maaşları; ortalama maaşların 10000'den yüksek olanlara ait olanları göstermek istersek ve sonuçları da bu ortalama maaşa göre artan sıralama ile sıralamak istersek (biliyorum çok karmaşık gelebilir ama bu ifadelerle SQL öğrenilir :) ) sorgu şöyle olmalıdır:
SELECT job_id, AVG(salary) "Ortalama Maaş"
FROM employees
WHERE job_id NOT IN ('MK_MAN', 'MK_REP', 'ADD_AST')
GROUP BY job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary);
JOB_ID Ortalama Maaş
---------- ----------------------
PU_MAN 11000
AC_MGR 12008
FI_MGR 12008
...
6 rows selected
Grup fonksiyonlarını iç içe kullanabileceğimizi söylemiştik. Örneğin iş tipine göre maaşların en küçüklerini bulup bu maaşların ortalamasını almak istersek şu sorguyu kullanırız:
SELECT ROUND(AVG(MIN(salary)), 0)
FROM employees
GROUP BY job_ıd;
ROUND(AVG(MIN(SALARY)),0)
-------------------------
8675
1 rows selected
Son olarak SQL deyimimiz ne olmuş bir bakalım:
SELECT sütunismi, grupfonksiyonu
FROM tabloismi
[WHERE sart(lari)]
[GROUP BY ifadesi]
[HAVING grupsarti]
[ORDER BY sütunismi];
Son bir kaç uygulama daha yapalım:
employees tablosundaki bölüm sayısını bulalım:
SELECT COUNT(DISTINCT department_ıd) "Bölüm Sayısı"
FROM employees;
Bölüm Sayısı
----------------------
11
1 rows selected
Yöneticisi olan tüm bölümleri kendi içinde gruplayarak bu bölümlerden maksimum maaş ile minimum maaş farkının 5000'den büyük olanlarının manager_id'si ile o bölümdeki minimum maaşı maaşa göre azalan sıralama ile getiren bir sorgu yazalım:
SELECT manager_ıd, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MAX(salary) - MIN(salary) > 5000
ORDER BY MIN(salary) DESC;
MANAGER_ID MIN(SALARY)
---------------------- ----------------------
100 5800
101 4400
2 rows selected
Bu konuyu da burada kapatalım. Bir sonraki konuda görüşmek üzere...
Hiç yorum yok:
Yorum Gönder