SQLite GROUP BY, HAVING ve Aggregate Fonksiyonlar

Örnek tablo

Örneklerde bölge ve temsilci bazında satışları tutan küçük bir tablo kullanıyoruz. Marmara bölgesindeki bir satırda adet bilgisini bilerek NULL bıraktık; özet fonksiyonlarının NULL'ı nasıl atladığını bu satırla göreceğiz.

CREATE TABLE satis(
  id       INTEGER PRIMARY KEY,
  bolge    TEXT,
  temsilci TEXT,
  adet     INTEGER,
  tutar    INTEGER
);
INSERT INTO satis(bolge, temsilci, adet, tutar) VALUES
  ('Ege',     'Deniz', 3,    900),
  ('Ege',     'Deniz', 1,    250),
  ('Ege',     'Arda',  2,    700),
  ('Marmara', 'Lale',  5,    1500),
  ('Marmara', 'Lale',  NULL, 400),
  ('Marmara', 'Okan',  4,    1100),
  ('Akdeniz', 'Sena',  1,    300);

Özet fonksiyonları ve NULL

Bir sorgu, sonuç listesinde en az bir özet fonksiyonu veya bir GROUP BY bölümü içeriyorsa SQLite onu "aggregate" sorgu olarak işler. GROUP BY yoksa bütün tablo tek bir grup sayılır ve sonuç tek satırdır. Aşağıdaki sorgu, yedi satırlık tablonun tamamını özetliyor:

SELECT count(*), count(adet), sum(adet), total(adet),
       printf('%.2f', avg(adet)), min(adet), max(adet)
  FROM satis;
-- çıktı: 7|6|16|16.0|2.67|1|5

Sonuçta iki farklı sayı dikkat çekiyor: count(*) 7, count(adet) 6 döndürdü. count(*) gruptaki satırların sayısıdır; count(X) ise X'in NULL olmadığı satırları sayar. sum, avg, min ve max de NULL değerleri hesaba katmaz; ortalama bu yüzden 16/7 değil 16/6 üzerinden hesaplandı. Ortalamayı burada printf() ile iki basamağa yuvarlayarak yazdık. Ondalık sayıların metne çevrilmesi SQLite 3.53.0 ile değiştiği için yuvarlanmamış bir ortalamanın son basamakları sürümden sürüme farklı görünebilir; bkz. SQLite matematik fonksiyonları.

Boş küme: sum ile total farkı

Hiç satır eşleşmediğinde özet fonksiyonları farklı davranır. GROUP BY içermeyen bir özet sorgusu girdi olmasa bile her zaman tam bir satır döndürür; bu satırdaki değerler ise fonksiyona göre değişir:

SELECT count(*), count(adet), sum(adet), total(adet), avg(adet), max(adet)
  FROM satis WHERE bolge = 'Karadeniz';
-- çıktı: 0|0||0.0||

sum() boş kümede NULL, total() ise 0.0 döndürür. Belgeler bu farkın nedenini açıkça yazar: SQL standardı boş kümenin toplamının NULL olmasını ister ve diğer veritabanları da böyle davrandığı için SQLite uyumluluk adına aynı kuralı izler. Standart dışı total() fonksiyonu bu tasarım sorununu aşmak için eklenmiştir. Raporlarda "bu ay hiç satış yok" durumunu 0 olarak göstermek istiyorsanız total() veya coalesce(sum(x), 0) kullanın.

İkisi arasında tip farkı da vardır. total() her zaman ondalık sayı döndürür. sum() ise bütün girdiler tamsayıysa tamsayı döndürür ve tamsayı taşması olursa hata verir; girdilerden biri ondalıksa sonuç da ondalık olur ve taşma hatası verilmez. avg() en az bir NULL olmayan girdi varsa her zaman ondalık döndürür ve belgelere göre total()/count() olarak hesaplanır.

SELECT avg(tutar), typeof(avg(tutar)), sum(tutar), typeof(sum(tutar)), typeof(total(tutar))
  FROM satis WHERE bolge = 'Akdeniz';
-- çıktı: 300.0|real|300|integer|real

SELECT sum(column1) FROM (VALUES (9223372036854775807), (1));
-- hata: integer overflow

SELECT total(column1) > 9e18 FROM (VALUES (9223372036854775807), (1));
-- çıktı: 1

SELECT avg(column1) FROM (VALUES ('abc'), (4));
-- çıktı: 2.0

Son sorgu daha az bilinen bir kuralı gösteriyor: avg ve sum, sayıya benzemeyen metin ve BLOB değerlerini 0 olarak yorumlar. 'abc' ile 4'ün ortalaması bu yüzden 2.0 çıktı. Sayısal sütunlarınıza yanlışlıkla metin girilebiliyorsa bu durum sessizce yanlış ortalamalara yol açar; sütun tiplerini sıkı denetlemek için SQLite STRICT tablolar yazısına bakın.

GROUP BY ile gruplama

GROUP BY yazdığınızda SQLite her satır için gruplama ifadelerini hesaplar ve sonuçları aynı olan satırları aynı gruba koyar. Her grup sonuçta tek bir satıra dönüşür. Gruplama ifadelerinin sonuç listesinde görünmesi gerekmez, ancak kendileri özet fonksiyonu olamaz.

SELECT bolge, count(*) AS islem, sum(tutar) AS ciro
  FROM satis
 GROUP BY bolge
 ORDER BY ciro DESC;
-- çıktı:
-- Marmara|3|3000
-- Ege|3|1850
-- Akdeniz|1|300

Metin değerleri gruplarken sütunun harmanlaması kullanılır. Varsayılan BINARY harmanlamada 'Ege' ile 'ege' iki ayrı grup oluşturur; büyük küçük harf ayrımı yapmayan gruplama için sütunu veya ifadeyi COLLATE NOCASE ile tanımlayabilirsiniz. NOCASE'in yalnızca İngilizce harflerde çalıştığını ve Türkçe harflerde beklenen sonucu vermediğini COLLATE ve NOCASE yazısında ayrıntılı olarak görebilirsiniz.

NULL değerler tek bir grup oluşturur

Karşılaştırmalarda NULL hiçbir değere eşit değildir, ama gruplama için belgeler ayrı bir kural koyar: gruplama yapılırken NULL değerler birbirine eşit sayılır. Bu yüzden bir sütundaki bütün NULL'lar tek bir grupta toplanır. İfadelerle de gruplayabilirsiniz; ilk sorgudaki GROUP BY 1, sonuç listesindeki ilk ifadeye göre gruplamak anlamına geliyor.

SELECT adet IS NULL AS adet_yok, count(*) FROM satis GROUP BY 1;
-- çıktı:
-- 0|6
-- 1|1

SELECT a, count(*) FROM (SELECT NULL AS a UNION ALL SELECT NULL UNION ALL SELECT 1) GROUP BY a;
-- çıktı:
-- |2
-- 1|1

HAVING ile grupları süzmek

HAVING bölümü her grup için bir kez, gruplama yapıldıktan sonra değerlendirilir ve sonucu yanlış olan gruplar atılır. Belgelere göre HAVING ifadesi sonuç listesinde bulunmayan özet fonksiyonlarına da başvurabilir. Özet fonksiyonunu WHERE içinde kullanmak ise hatadır, çünkü WHERE çalıştığında henüz grup yoktur:

SELECT bolge, sum(tutar) AS ciro
  FROM satis
 GROUP BY bolge
HAVING sum(tutar) >= 1800
 ORDER BY bolge;
-- çıktı:
-- Ege|1850
-- Marmara|3000

SELECT bolge, sum(tutar) FROM satis WHERE sum(tutar) > 1000 GROUP BY bolge;
-- hata: misuse of aggregate: sum()

Pratik kural basittir: tek tek satırlara ait koşulları (tarih >= '2026-01-01' gibi) WHERE içine, grup toplamlarına ait koşulları (sum(tutar) > 1000 gibi) HAVING içine yazın. Satırları gruplamadan önce elemek, gruplanacak veri miktarını da azaltır.

DISTINCT ile benzersiz değerler

Tek argüman alan her özet fonksiyonunda argümanın önüne DISTINCT yazabilirsiniz; bu durumda yinelenen değerler fonksiyona verilmeden önce ayıklanır. count(DISTINCT temsilci) her bölgedeki farklı temsilci sayısını verir. Kısıt da buradan gelir: DISTINCT yalnızca tek argümanlı çağrılarda kullanılabilir, bu yüzden ayraç parametresi olan group_concat çağrısına eklenemez.

SELECT bolge, count(DISTINCT temsilci) FROM satis GROUP BY bolge ORDER BY bolge;
-- çıktı:
-- Akdeniz|1
-- Ege|2
-- Marmara|2

SELECT group_concat(DISTINCT temsilci, ';') FROM satis;
-- hata: DISTINCT aggregates must have exactly one argument

group_concat, string_agg ve ORDER BY

group_concat(X) gruptaki NULL olmayan değerleri virgülle birleştirir; ikinci argüman verilirse onu ayraç olarak kullanır. string_agg(X, Y) aynı fonksiyonun PostgreSQL ve SQL Server ile uyumlu adıdır ve SQLite 3.44.0 sürüm notu ile eklendi. Aynı sürüm, özet fonksiyonlarının son argümanından sonra ORDER BY yazmayı da getirdi. Sıralama belirtmezseniz birleştirilen öğelerin sırası belgelere göre keyfidir ve bir çalıştırmadan diğerine değişebilir.

SELECT bolge, string_agg(temsilci, ' / ' ORDER BY temsilci DESC)
  FROM satis GROUP BY bolge ORDER BY bolge;
-- çıktı:
-- Akdeniz|Sena
-- Ege|Deniz / Deniz / Arda
-- Marmara|Okan / Lale / Lale

SELECT group_concat(column1) FROM (VALUES (NULL), ('a'), (NULL), ('b'));
-- çıktı: a,b

max() veya count() gibi fonksiyonlarda girdi sırası sonucu etkilemez; sırayı önemli kılan, çıktısı metin veya JSON dizisi olan fonksiyonlardır. JSON çıktısı üreten json_group_array() ve json_group_object() da özet fonksiyonudur ve SQLite JSON fonksiyonları yazısında anlatılıyor. İkinci sorguda görüldüğü gibi NULL değerler birleştirmeye hiç katılmaz.

FILTER ile koşullu özet

Bir özet fonksiyonunun yalnızca belirli satırlar üzerinde çalışmasını istiyorsanız arkasına FILTER (WHERE koşul) ekleyebilirsiniz. Bu sayede tek bir sorguda aynı grup için farklı koşullara göre birden çok özet alırsınız. FILTER SQLite 3.30.0 sürüm notu ile geldi; daha eski sürümlerde aynı etki sum(CASE WHEN koşul THEN tutar END) biçiminde yazılır.

SELECT bolge,
       count(*) FILTER (WHERE tutar >= 500)        AS buyuk_islem,
       sum(tutar) FILTER (WHERE temsilci = 'Lale') AS lale_cirosu
  FROM satis
 GROUP BY bolge ORDER BY bolge;
-- çıktı:
-- Akdeniz|0|
-- Ege|2|
-- Marmara|2|1900

Akdeniz ve Ege bölgelerinde Lale adlı temsilci olmadığı için o gruplardaki sum filtreden hiçbir satır geçirmedi ve NULL döndü; count ise boş kümede 0 döndürdü. Bu, yukarıda anlatılan boş küme kurallarının grup bazında da geçerli olduğunu gösteriyor.

Çıplak sütunlar: SQLite'a özgü davranış

Sonuç listesinde, ne bir özet fonksiyonunun içinde ne de GROUP BY listesinde yer alan bir sütun bulunursa buna "çıplak" (bare) sütun denir. Çoğu veritabanı böyle bir sorguyu hatayla reddeder; SQLite ise kabul eder ve belgeler bunun bilinçli bir özellik olduğunu belirtir. Genel durumda çıplak sütunun değeri gruptaki satırlardan herhangi birinden gelir ve hangisi olduğu öngörülemez.

Sorguda tek bir min() veya max() varsa durum değişir: çıplak sütunlar, en küçük veya en büyük değeri içeren satırdan alınır. Aşağıdaki sorgu bu sayede her bölgenin en yüksek tutarlı satışını hangi temsilcinin yaptığını gösteriyor:

SELECT bolge, temsilci, max(tutar) FROM satis GROUP BY bolge ORDER BY bolge;
-- çıktı:
-- Akdeniz|Sena|300
-- Ege|Deniz|900
-- Marmara|Lale|1500

Belgeler bu özel davranışın sınırlarını da sıralar. En büyük değer birden fazla satırda tekrar ediyorsa çıplak değerin hangi satırdan geleceği keyfidir. Sorguda iki veya daha fazla min() ya da max() varsa hangisinin belirleyici olacağı da keyfidir. Davranış yalnızca yerleşik min ve max için geçerlidir. Boş bir grupta ise çıplak sütun tablolarda hiç bulunmayan bir değer bile alabilir.

median ve percentile fonksiyonları

SQLite 3.54 belgeleri yerleşik özet listesinde median(), percentile(), percentile_cont() ve percentile_disc() fonksiyonlarını da sayar, ancak bir koşulla: bu fonksiyonlar 3.51.0 sürümünden beri kütüphanenin içinde gelir, fakat derleme sırasında SQLITE_ENABLE_PERCENTILE seçeneğiyle açılmaları gerekir; daha önceki sürümlerde yüklenebilir bir eklenti olarak kullanılabilirler (SQLite 3.51.0 sürüm notu). Komut satırı aracı ise bu fonksiyonları SQLite 3.47.0 sürüm notu sürümünden beri kendi içinde taşır. Aşağıdaki sorguyu biz de sqlite3 aracıyla çalıştırdık:

SELECT median(tutar), percentile(tutar, 25) FROM satis;
-- çıktı: 700.0|350.0

median(X), percentile_cont(X, 0.5) ile aynıdır. percentile(Y, P) P değerini 0 ile 100 arasında, percentile_cont ise 0 ile 1 arasında bekler. percentile_disc ara değer hesaplamak yerine her zaman girdi değerlerinden birini döndürür. Uygulamanızın kullandığı SQLite kütüphanesinde bu fonksiyonlar yoksa "no such function" hatası alırsınız; bu durumda komut satırında çalışan bir sorgunun uygulamada çalışmaması şaşırtıcı olmamalı.

Sık yapılan hatalar

• count(*) ile count(sutun) farkını unutmak: dış birleştirmeyle sayım yaparken eşleşmeyen satırları da saymamak için sağ tablonun bir sütununu sayın.
• Boş kümede 0 beklemek: sum() hiç satır yoksa NULL döndürür; total() veya coalesce() kullanın.
• Özet koşulunu WHERE içine yazmak: "misuse of aggregate" hatası alırsınız; koşul HAVING bölümüne gider.
• Çıplak sütuna güvenmek: min/max dışındaki durumlarda değerin hangi satırdan geldiği belirsizdir.
• Sırasız group_concat: sıra önemliyse fonksiyonun içine ORDER BY yazın (3.44.0 ve sonrası).

Sık Sorulan Sorular

group_concat ile string_agg arasında fark var mı?

Yoktur. Belgelere göre string_agg(X, Y), group_concat(X, Y) fonksiyonunun takma adıdır. group_concat MySQL ile, string_agg PostgreSQL ve SQL Server ile uyumludur. Tek fark, string_agg fonksiyonunun ayraç argümanını zorunlu tutmasıdır.

GROUP BY sonucu sıralı gelir mi?

Buna güvenmeyin. Belgeler ORDER BY olmayan sorguların satır sırasını tanımsız kabul eder; gruplanmış sonucu belirli bir sırada görmek istiyorsanız ORDER BY ekleyin.

Aynı sorguda hem satır ayrıntısı hem grup toplamı alabilir miyim?

Evet, bunun için pencere fonksiyonu kullanın: sum(tutar) OVER (PARTITION BY bolge) her satırın yanına kendi bölgesinin toplamını yazar ve satır sayısını azaltmaz. Ayrıntılar SQLite window fonksiyonları yazısında.

Gruplama sorgum yavaş, ne yapabilirim?

Gruplanan sütunlar üzerinde bir indeks, SQLite'ın ayrı bir sıralama yapmadan grupları sırayla okumasına yardım edebilir. Planı EXPLAIN QUERY PLAN ile inceleyip "USE TEMP B-TREE FOR GROUP BY" satırını arayın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite SELECT · SQLite JOIN türleri · Window fonksiyonları · SQLite NULL davranışı · SQLite JSON fonksiyonları