ROLLUP ve CUBE ile Tek Sorguda Alt Toplam ve Genel Toplam

Alt toplam sorunu ve çözümü

Bir satış raporunda bölge ve ürün bazında toplamların yanında bölge bazında alt toplamları ve en sonda genel toplamı görmek istersiniz. Klasik yöntem, farklı GROUP BY düzeylerine sahip birkaç sorguyu UNION ALL ile birleştirmektir. Çalışır, ama sorgu uzar, aynı tablo tekrar tekrar taranır ve bir filtreyi değiştirmek için her parçaya dokunmanız gerekir.

GROUP BY içindeki ROLLUP ve CUBE bu ihtiyaç için tasarlanmış genişletmelerdir. Bunlar GROUPING SETS adlı daha genel bir yapının kısayoludur: her biri, tek bir sorguda birden çok gruplama düzeyini hesaplar ve sonuçları birlikte döndürür. PostgreSQL belgesi bu yapıları, FROM ve WHERE sonucunun her gruplama kümesi için ayrı ayrı gruplanıp sonuçların birleştirilmesi olarak tanımlar.

ROLLUP: hiyerarşik alt toplamlar

ROLLUP, verdiğiniz sütun listesini ve listenin tüm önek kısımlarını gruplar. Belgede verilen eşitliğe göre ROLLUP (a, b, c), şu kümeler için GROUPING SETS ile aynıdır: (a, b, c), (a, b), (a) ve boş küme. Boş küme, bütün satırları tek grupta toplayan genel toplamdır.

Bu yüzden ROLLUP, doğal bir hiyerarşisi olan veriler için uygundur: ülke, şehir, mağaza ya da yıl, ay, gün gibi. Sütun sırası önemlidir: önce en genel düzeyi yazarsınız.

SELECT bolge, product, SUM(tutar) AS toplam FROM satislar GROUP BY ROLLUP (bolge, product) ORDER BY bolge, product;

Sonuçta her bölge-ürün çifti için satırlar, her bölge için ürün sütunu NULL olan bir alt toplam satırı ve tüm sütunların NULL olduğu bir genel toplam satırı gelir. NULL, o sütun için "tümü" anlamına gelen bir yer tutucudur.

CUBE: tüm kombinasyonlar

CUBE, verilen sütunların bütün alt kümelerini gruplar; belgede bunun kuvvet kümesi olduğu belirtilir. Örneğin bölge ve ürün sütunlarında bölge ile ürünün birlikte, yalnız bölgenin, yalnız ürünün ve genel toplamın hesaplandığını düşünebilirsiniz.

SELECT bolge, product, SUM(tutar) AS toplam FROM satislar GROUP BY CUBE (bolge, product);

ROLLUP'tan farkı, hiyerarşi gerektirmemesidir: aynı sorguda hem bölge bazında hem ürün bazında toplamlar gelir. Çapraz tablo (crosstab) tarzı raporların veri kaynağı olarak kullanışlıdır. Maliyeti ise sütun sayısı arttıkça gruplama kümesi sayısının hızla büyümesidir; çok sütunla CUBE yazmak gereksiz yere büyük sonuç ve uzun çalışma süresi doğurabilir. Gerçekten hangi kombinasyonlara ihtiyacınız olduğunu belirleyip gerekirse GROUPING SETS ile yalnızca onları isteyin.

GROUPING SETS ile kombinasyonları elle seçmek

ROLLUP ve CUBE yetmiyorsa GROUPING SETS ile istediğiniz kümeleri tek tek yazarsınız:

SELECT bolge, product, kanal, SUM(tutar) AS toplam FROM satislar GROUP BY GROUPING SETS ((bolge, product), (kanal), ());

Boş parantez genel toplamı verir. Belgeye göre parantez içindeki öğeler tek birim olarak değerlendirilir; örneğin ROLLUP (a, (b, c), d) ifadesinde b ile c birlikte ele alınır ve ayrı ayrı çözülmez. Bu, ürün ile alt ürün gibi sürekli birlikte hareket eden sütunlar için faydalıdır.

Birden fazla gruplama öğesini aynı GROUP BY'da yan yana yazarsanız sonuç, kümelerin kartezyen çarpımı olur. Bu bazen istenir, bazen tekrar eden gruplara yol açar; PostgreSQL'de bu tekrarları gidermek için GROUP BY DISTINCT yazımı vardır. Başka veritabanlarında bu özelliğin varlığı değişir.

NULL yer tutucusu ile gerçek NULL'u ayırmak: GROUPING

Alt toplam satırlarında NULL görmek kafa karıştırıcıdır, çünkü veride gerçekten NULL değer taşıyan satırlar da olabilir. PostgreSQL belgesi de bunu not eder: gruplanmayan bir sütundaki NULL, gruplanan sütunun yer tutucu NULL'undan ayırt edilemez. Çözüm GROUPING fonksiyonudur. Bu fonksiyon, argüman olarak verdiğiniz sütunların o satırda toplulaştırılıp toplulaştırılmadığını bildirir.

SELECT bolge, product, GROUPING(bolge) AS bolge_toplam_mi, GROUPING(product) AS is_product_total, SUM(tutar) AS toplam FROM satislar GROUP BY ROLLUP (bolge, product);

Sıfır dışındaki değer, o sütunun satırda toplulaştırıldığını, yani satırın bir alt toplam ya da genel toplam olduğunu gösterir. Sıfır ise sütunun gerçek bir değer taşıdığı anlamına gelir. Bu yardımcı sütunları bir dış sorguda CASE ile "Tüm bölgeler" ya da "Tüm ürünler" gibi etiketlere çevirebilirsiniz.

Bu kalıpla okunur etiketler üretir, gerçek NULL değerlerini ise ayrı bırakırsınız. Eğer bölge sütununda bazı satırlar boşsa, bu satırlar ayrı bir grup olarak kalır ve yanlış alt toplam satırıyla karışmaz.

Örnek sonuç ve raporda kullanımı

Örneğin satislar tablosunda Marmara ve Ege bölgeleri ile kalem ve defter ürünleri olsun. ROLLUP (bolge, product) şu satırları üretir: Marmara-kalem, Marmara-defter, Marmara için ürün sütunu boş alt toplam, Ege için aynı türde satırlar ve en sonda sütunların tamamı boş genel toplam. Rapor okuyucusu için bunlar tek tabloda hem ayrıntıyı hem özeti verir.

Uygulamada bu sorguyu bir görünüm (view) olarak saklamak yaygındır. BI aracı görünümü okur, GROUPING sütunlarına göre satırları biçimlendirir: genel toplam kalın, alt toplam girintisiz, ayrıntı girintili. Böylece toplam hesabı tek yerde kalır ve rapor tasarımcıları toplamı elle hesaplamaz.

Sıralama, filtreleme ve sık hatalar

• ORDER BY'da NULL'ların konumuna güvenmeyin: genel toplam satırının raporun sonunda çıkması için GROUPING değerine göre açıkça sıralayın.
• Alt toplamları filtrelemek için HAVING kullanın; WHERE gruplamadan önce çalışır ve toplam satırlarını etkilemez.
• Aynı sorgudaki COUNT(DISTINCT ...) ve ortalama gibi toplulaştırmalar, alt toplam düzeyinde de kendi mantıklarıyla hesaplanır. Veritabanı her düzeyde ilk veriden hesaplar; siz sonuçları sonradan ortalarsanız yanlış çıkar, ortalamaların ortalamasını almayın.
• Toplam satırlarını birden fazla kez saymak: alt toplamları elle toplayan bir BI araç hesabı, zaten gelen toplam satırlarını da dahil ederse sonuç şişer.
• Veritabanı desteğini kontrol edin: ROLLUP, CUBE ve GROUPING SETS her sistemde aynı biçimde bulunmaz, söz dizimi de farklı olabilir.

Sonuç doğrulaması için basit bir kontrol: ürün düzeyindeki satırların toplamı bölge alt toplamına, bölge alt toplamlarının toplamı da genel toplama eşit olmalıdır.

Bu beceriyi Mindset programlarında nerede çalışırsınız

Alt toplam sorguları toplulaştırma ve raporlama mantığının ileri bir uygulamasıdır. Mindset'in SQL ile Veri Sorgulama (İleri) programında analitik modeller ve ileri vaka çalışmaları, SQL ile İleri Veri Dönüşümü ve Modelleme programında ise olgu tablolarında toplulaştırma bir modül olarak yer alır; ROLLUP ve CUBE bu çerçevenin doğal parçasıdır.

Finans raporlaması, satış operasyonu ve BI geliştirme ekipleri için faydalıdır: paylaşılan bir rapor sorgusunun alt toplamlarını tek yerde üretmek, her raporun kendi toplamını farklı hesaplaması riskini azaltır.