Der SQL Server beherrscht Fenster-Funktionen (Window Functions) mit der charakteristischen Syntax OVER … PARTITION BY schon seit der Version 2005, aber kaum jemand hat sie in der Praxis benutzt. Ein häufiger Grund dafür könnte sein, dass man auf keinen Fall SQL-Statements schreiben möchte, die nicht ANSI-SQL-kompatibel sind, damit man jederzeit den Datenbankserver wechseln kann. Nun gut, die Fenster-Funktionen wurden schon im ISO-Standard SQL:2003 erwähnt, und werden in SQL:2008 näher ausgeführt. Und mit SQL Server 2012 gibt es noch eine Reihe von SQL-Funktionen mehr, die man mit OVER benutzen kann; Grund genug, einen kurzen Blick darauf zu werfen!
Die Fenster-Funktionen unterstützen Konzepte, die die SQL-Sprache normalerweise nicht kennt, wie eine Reihenfolge, ein “vorher/nachher”, und vor allem eine Kombination von einem Detailwert mit einer Aggregation in einer Ergebniszeile! Im Normalfall hat man ja immer ein SELECT für die Detailzeilen und eins mit GROUP BY für die Aggregate; die Fenster-Funktionen können aber beides in derselben Abfrage. Das ist durchaus etwas, was man klassischerweise eher von einem OLAP-Cube erwartet, und die Window-Funktionen könnten durchaus in einigen Fällen eine Alternative sein!
Nehmen wir uns die Bespieldatenbank AdventureWorksDW. In der Tabelle DimReseller steht für jeden Wiederverkäufer sein Jahresumsatz drin (AnnualSales), und in welcher Region er tätig ist (GeographyKey). Wir möchten alle Reseller für die Produktlinie “Mountain”, die in drei Vergleichsgebieten tätig sind; das sind 11 Zeilen.
In der ersten Zeile des Statements zeigt die Funktion ROW_NUMBER() gleich einmal, dass sie Zeilen durchnummerieren kann (sonst in SQL unmöglich!). Damit sie aber weiß, welche Reihenfolge sie dabei nutzen soll, benötigt sie ein OVER mit ORDER BY; sinnvollerweise mit den Spalten, die wir auch später benutzen wollen, GeographyKey und AnnualSales.
SELECT ROW_NUMBER() OVER (ORDER BY GeographyKey, AnnualSales DESC) AS Row, ResellerName, GeographyKey, AnnualSales
, SUM(AnnualSales) OVER () AS ‚TotalSales‘
, SUM(AnnualSales) OVER (PARTITION BY GeographyKey) AS ‚RegionSales‘
, AnnualSales / SUM(AnnualSales)OVER (PARTITION BY GeographyKey) * 100 AS ‚PercentOfRegionSales‘
, RANK() OVER (PARTITION BY GeographyKey ORDER BY AnnualSales DESC) AS‚Rank‘
, FIRST_VALUE(ResellerName)OVER (PARTITION BY GeographyKey ORDER BY AnnualSales DESC) AS ‚Best Seller‘
, COUNT(*) OVER (PARTITION BY GeographyKey) AS ‚NumberOfResellers‘
FROM DimReseller
WHEREProductLine = ‚Mountain‘
AND GeographyKey IN (2101, 2153, 2151)
Diese Abfrage schafft es, die 11 Detailzeilen noch mit jeder Menge Infos anzureichern, also etwa wie viel Umsatz in Summe über alle Zeilen erzielt wurde (TotalSales), und wie viel Umsatz in Summe über jede Region, also jeden eindeutigen GeographyKey (RegionSales). So sieht das Ergebnis aus:
Das “Fenster” besteht hier also bei TotalSales einfach aus allen Zeilen, denn die OVER-Klausel ist leer, aber bei allen anderen Berechnungen aus den Zeilen mit demselben GeographyKey. (PARTITION BY GeographyKey). Innerhalb dieses Fensters kann man eine Teil-Summe einblenden, wie bei RegionSales, und daraus den prozentualen Anteil jeder Zeile an dieser Summe berechnen (PercentOfRegionSales). Mit RANK und seinen Schwesterfunktionen DENSE_RANK, PERCENT_RANK und CUME_DIST (nicht im Bild) lassen sich Rangfolgen innerhalb des Fensters abbilden, und FIRST_VALUE kann sogar innerhalb des Fensters an den Anfang springen (und ja, es gibt auch LAST_VALUE und sogar LAG und LEAD).
Interessant ist auch, dass mit den Fenster-Funktionen typische Aufgaben für OLAP-Cubes gelöst werden können, für die man sich mit Standard-SQL die Finger bricht, etwa Kumulierung oder Vergleiche zum Vormonat. Dazu ein Beispiel aus AdventureWorksDW, für das ich mir erst einmal mit einem allgemeinen Tabellenausdruck (Common Table Expression oder CTE) für jeden Monat und jeden ResellerType aus den Detailzeilen die Umsätze summiere, klassisch mit GROUP BY:
WITH MonthlyResellerTypeSales AS (
— This returns the monthly sales of each reseller type for all years:
SELECT
DimDate.CalendarYear
, DimDate.MonthNumberOfYear
, DimReseller.BusinessType
, SUM(FactResellerSales.SalesAmount) AS SalesAmount
FROM FactResellerSales
INNER JOIN DimDateON FactResellerSales.OrderDateKey = DimDate.DateKey
INNER JOIN DimReseller ON FactResellerSales.ResellerKey = DimReseller.ResellerKey
GROUP BY CalendarYear, MonthNumberOfYear, BusinessType)
Auf dieser virtuellen Ergebnistabelle lassen sich dann weiter unten in der Abfrage ganz schön die Fenster-Funktionen anwenden:
— Add cumulative and deviation information
SELECT CalendarYear, MonthNumberOfYear, BusinessType, SalesAmount
,SalesAmount – LAG(SalesAmount, 1) OVER (PARTITION BY BusinessType ORDER BY CalendarYear, MonthNumberOfYear) AS SalesIncrease
, SUM(SalesAmount) OVER (PARTITION BY BusinessType, CalendarYear ORDER BY CalendarYear, MonthNumberOfYear) AS SalesCum
FROM MonthlyResellerTypeSales
WHERE CalendarYear IN (2005, 2006)
Mithilfe von LAG kann man hier innerhalb des Fensters “Alle Monate eines BusinessTypes” einfach in den SalesAmount des Vormonats “hineingreifen”, sogar über die Jahresgrenze hinweg! Und mit einem nach Monat sortierten SUM über Partitionen aus BusinessType und CalendarYear funktioniert sogar eine Kumulierung in SQL.
Es gäbe über die Fenster-Funktionen noch einiges mehr zu sagen, denn sie können z.B. auch mit ROWS, RANGE und CURRENT ROW nicht nach logischen Inhalten, sondern nach Anzahl von Zeilen gebildet werden, auch sehr untypisch für SQL.
In Zeiten von In-Memory Business Intelligence wie den xVelocity Columnstore-Indizes ist es also durchaus möglich, dass SQL-Funktionen für OLAP wie die hier vorgestellten Fenster-Funktionen an Bedeutung gewinnen, weil sie analytische Abfragen schnell ausführen können, ohne dass man vorher einen Cube erstellen und mit Daten laden muss.