GROUP BY mit ROLLUP

Anbei ein Thema, welches mir neulich erst wieder aufs Flipchart kam. Wie errechne ich in einem gruppierten Resultset möglichst einfach Zwischensummen?

Nun ja, da sieht der Berater draußen schon recht spannende Konzepte in denen diverse UNION ALL eine entscheidende Rolle spielen! Dabei gibt es dafür in der Abfragesprache SQL extra ein Argument für diese Herausforderung.

Kurze Anmerkung dazu, wenn Ihr zum Beispiel mit den Reporting Services unterwegs seit, dann habt Ihr diese Fragestellung sehr wahrscheinlich gar nicht, da dort Zwischensummen durch die Gruppierungsfunktion im Report abgebildet wird. So, der Rest von uns kann nun weiterlesen.

Ach ja, und das betrifft jetzt nicht nur den SQL Server, wir bewegen uns da durchaus im ANSI Standard! :-)

Wir nehmen unsere gute alte AdventureWorksDW2008. Als Beispiel wollen wir uns den Umsatz aus der FactInternetSales aufgeteilt nach Attributen aus der Dimension Product darstellen lassen.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, SalesAmount
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey

Ok, so im Detail muss es ja man mal gar nicht sein, also bilden wir Gruppen mittels GROUP BY.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

Besser! Aber jetzt fragt doch tatsächlich jemand nach Zwischensummen für Category und Subcategory! Wie bringen wir die nun in das Resultset? Dafür gibt es die GROUP BY Erweiterung ROLLUP!

Und hier jetzt ein Beispiel mit GROUP BY ROLLUP.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by rollup( EnglishProductCategoryName , EnglishProductSubcategoryName, EnglishProductName)
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

Was ist passiert? Wir sehen in der ersten Zeile nun die Umsätze gesamt über alle drei Attribute. Das ist noch keine Kunst, dafür hätten wir kein GROUP BY benötigt, dass hätte auch ein einfaches SUM geschafft. Aber nun bekommen wir auch die Summen je Subcategory und Category dargestellt. Das NULL zeigt jeweils an, dass es sich um eine Summenzeile für das jeweilige Attribut handelt.

Und wer jetzt sagt, dass er das schon alles kennt aber eine andere Syntax dafür verwendet. Der sei gewarnt! Denn die Notation mit “with rollup” ist nicht ANSI-konform und läuft daher aus! Fällt Euch also vielleicht bei einem späteren Update auf die Füße!

Wer sich nun noch an den NULLs stört und diese nicht in seiner Anwendung nutzen möchte, der kann diese natürlich auch direkt in der Abfrage in was Sprechendes ändern .

select
isnull(EnglishProductCategoryName,'Summe') as Produktkategorie,
isnull(EnglishProductSubcategoryName, 'Summe') as Produktunterkategorie,
isnull(EnglishProductName,'Summe') as Produkt,
sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by  rollup( EnglishProductCategoryName , EnglishProductSubcategoryName, EnglishProductName)
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

 

Und noch was. Da AdventureWorksDW2008 ja die Basis des Microsoft Beispiel-Cubes für die Analysis Services ist, können wir die Ergebnisse unserer Query auch mittels Cube überprüfen bzw. den Cube mit unserer Query gegenprüfen. Hey, cool, geht auf! :-)

 

 

Für viele Anwendungsfälle wäre es sehr wahrscheinlich einfacher, wenn wir direkt aus unserer Applikation auf einen OLAP Cube zugreifen würden. Das wird aber noch seltener genutzt als die ROLLUP Erweiterung, dabei ist die Anwendung fast einfacher. Das bringt mich zu ADOMD.NET, aber das beschreibe ich ein anderes mal im Detail.