SQL’de İleri Düzey Gruplama: ROLLUP ile Tanışın

Benim gibi SQL püristlerinin hoşuna gidecek, hem sorgu performansını arttıran hem de Ad-Hoc talepleri cevaplama sürenizi kısaltabilecek bir komut olan ROLLUP ‘tan bahsedeceğim. ROLLUP tek bir sonuç içerisinde size birden fazla sütunu gruplama imkanı sunarak alt toplamları tek bir sorguda elde etmenizi sağlayan güçlü bir GROUP BY uzantısı. Özellikle cardinality’si düşük sütunlarla kullanıldığında rapor üretme hızınızı ciddi derecede arttırır. Eğer üzerine düşünürseniz kendi OLAP küpü-vari sisteminizi bile kurabilirsiniz.

ROLLUP komutu gruplama yapılan sütunlarda bir hiyerarşi olduğunu varsayar. Önce ROLLUP kullanmadan sorunlarımızı nasıl çözüyoruz ona bir bakalım. Aşağıda 6 satırlık bir tablomuz olsun.

Bize şöyle bir Ad-Hoc talep geldiğini varsayalım:

1. Kampanya kırılımında her OS ne kadar revenue getirmiş
2. Media Source kırılımında her OS ne kadar revenue getirmiş
3. Toplam revenue OS’e göre nasıl dağılmıştır

SELECT os,
       media_source,
       campaign_id,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY 1,2,3
ORDER BY 1,4 DESC

Sonuç:

Sorunun 1. kısmını hallettik. Peki os + media_source özelinde?

SELECT os,
       media_source,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY 1,2
ORDER BY 1,3 DESC

Sonuç:

Ve sadece OS kırılımında:

SELECT os,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY 1
ORDER BY 2 DESC

Sonuç:

Üç hızlı sorguda soruyu cevapladık. Peki bunu tek sorguda yapmak istesek nasıl ilerlerdik? Muhtemelen UNION ALL kullanarak bu üç sorguyu bir araya getirirdik.

-- Seviye 0
SELECT os,
       media_source,
       campaign_id,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY os, media_source, campaign_id

UNION ALL

-- Seviye 1
SELECT os,
       media_source,
       NULL AS campaign_id,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY os, media_source

UNION ALL

-- Seviye 2
SELECT os,
       NULL AS media_source,
       NULL AS campaign_id,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY os

UNION ALL

-- Seviye 3
SELECT NULL AS os,
       NULL AS media_source,
       NULL AS campaign_id,
       SUM(revenue) AS total_revenue
FROM table

ORDER BY 1, 4 DESC

ROLLUP’ı Nasıl Kullanırız

Yukarıda uzun uzadıya yaptığımız her şeyiROLLUP ile hızlıca yapabiliyoruz. Sadece sorguyu hızlı yazmaktan da bahsetmiyorum ROLLUP ile sorgularımız da hızlanıyor. Örneğin UNION ALL yaptığımızda tabloyu 4 kere tarıyoruz. ROLLUP ise sadece 1 kere tarayıp bize sonucu verebiliyor.

SELECT os,
       media_source,
       campaign_id,
       SUM(revenue) AS total_revenue
FROM table
GROUP BY ROLLUP(os, media_source, campaign_id)
ORDER BY 1, 4 DESC

Sonuç:

Özetle ROLLUP Ne Yapar?

Diyelim ki GROUP BY ROLLUP içinde (a, b, c) olmak üzere 3 sütun bulunmakta. ROLLUP parantez içine aldığımız sütunların gruplamalarını hiyerarşik bir şekilde oluşturur. Bu basitçe şu demektir:

• ROLLUP ilk olarak parantez içindeki tüm sütunları kullanarak bir group by yapar. Bu klasik GROUP BY a, b, c ile aynı çalışır.
• Bunu yaptıktan sonra parantezin en sağındaki sütunu atar ve bu sefer de (a, b) sütunlarını gruplar; c sütunundaki değerler NULL hale gelir.
• Ardından b sütununu da atarak sadece a sütununda gruplama yapar ve diğer iki sütun NULL hale gelir.
• Son olarak hiç sütun olmadan tek bir satırda genel toplam’ı verir; üç sütun da NULL olur.

Böylece hiyerarşik bir şekilde alt toplamları ve genel toplamı tek bir sorgu sonucunda görebiliriz.

Not: Sonuçtaki NULL değerler, “bu sütunda gruplama yapılmadı” anlamına gelir.

Peki gerçek NULL Değerleri Nasıl Ayırt Edeceğiz?

Veri setimizde os, media_source ve campaign_id olmak üzere 3 sütun vardı. Peki organik gelen kullanıcıları nasıl gösteririz? media_source olarak organic yazabiliriz ama herhangi bir campaign_id’ye sahip olmayacaklar. Organik gelen kullanıcıları da eklediğimiz veri setimiz şu şekilde olacaktır:

Dikkatsiz bir şekilde ROLLUP kullanırsak eğer campaign_id sütunundaki NULL değerleri alt toplam sanmak gibi bir hataya düşebiliriz. Gelin yanlış yaptığımızda nasıl bir sonuç alıyoruz

SELECT os,
       media_source,
       campaign_id,
       SUM(revenue) AS total_revenue
FROM attribution
GROUP BY ROLLUP(os, media_source, campaign_id)
ORDER BY 1, 2, 3

Peki Sorun ne?

Fark ettiyseniz 3. ve 4. satırlar os, media_source ve campaign_id sütunlarında aynı. Peki total revenue’lar neden farklı? Çünkü NULL campaign_id’ye sahip satırlardan biri sanki bir campaign_id’ varmış gibi muamele görürken diğeri alt toplamı ifade ediyor. Ancak biz hangisi hangisi ayırt edemiyoruz. Burada da imdadımıza GROUPING yetişiyor.

SELECT os,
       media_source,
       campaign_id,
       SUM(revenue) AS total_revenue,
       GROUPING(campaign_id) AS is_subtotal
FROM attribution
GROUP BY ROLLUP(os, media_source, campaign_id)
ORDER BY 1, 2, 3;

Bizim durumumuzda sadece campaign_id NULL olabileceği için GROUPING içine sadece o sütunu ekledik ancak eğer os ve media_source da NULLABLE ise onları da GROUPING içine almak yararlı olacaktır.

is_subtotal sütunu 3. satır için 0 değerini alıyor. Bu da bize bu sütunun bir gruplama sütunu değil NULL değere sahip bir campaign_id sütununa ait olduğunu gösteriyor.

ROLLUP Komutunu Hangi Veritabanlarında Kullanabiliriz?

PostgreSQL ✅ BigQuery ✅ Snowflake ✅ Oracle ✅ SQL Server ✅

Detaylı incelemek için PostgreSQL’ın dökümantasyonuna da göz atabilirsiniz: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS

Sonraki yazılarımda da ROLLUP’ın kardeşi CUBE ve ikisinin atası olan GROUPING SETS ile devam edeceğim. Yorumlarınızı ve önerilerinizi bekliyorum.