SQLBlog Nederland Business Intelligence & Datawarehousing
Op internet is er al veel over geschreven maar ik vond het de moeite waard om een artikel te posten over Running Totals / Cumulatieven berekenen binnen Microsoft SQL Server 2005 / 2008. Er zijn diverse manieren om dit te doen. De meest snelle manier wordt in dit artikel besproken.
SQL Server 2005 + heeft zelf geen goede manier om running totals te berekenen. Hij kan namelijk niet uit zichzelf de vorige rij bij de huidige rij optellen. Er moet een manier worden gevonden om de vorige rij bij de huidige rij op te tellen.
De meest gebruikte methode en het eenvoudigste te begrijpen is de self-join. Bij deze methode “Join” je de dataset op zichzelf om iedere keer het resultaat van vorige records op te kunnen tellen. Dit is alleen niet de meest effectieve methode!
De meest snelle methode is door gebruik te maken van een table variable of tijdelijke tabel (@ of #). Laat ik dit uitleggen met een voorbeeld. We gebruiken hiervoor AdventureWorksDW. Stel dat we per klant de internetsales cumulatieven willen uitrekenen over de tijd.
Eerst maken we een table variable (@) aan en vullen deze met alle internetsales per klant per week. We sorteren de data bij het invoegen. Dit is absoluut noodzakelijk omdat we daarna de records een voor een bijwerken met cumulatieven.
USE AdventureWorksDW GO DECLARE @RunningTotalAantal INT DECLARE @RunningTotalBedrag MONEY DECLARE @KlantNaam AS VARCHAR(128) DECLARE @Sales TABLE (WeekNaam VARCHAR(128), KlantNaam VARCHAR(128), Aantal INT, Bedrag MONEY, AantalCumulatief INT, BedragCumulatief MONEY) INSERT INTO @Sales SELECTDT.CalendarYear * 100 + DT.WeekNumberOfYear AS WeekNaam , DC.FirstName + ' ' + DC.LastName+ ' (' + CAST(DC.CustomerKey AS VARCHAR(16)) + ')' AS KlantNaam , SUM(FIS.OrderQuantity) AS Aantal , SUM(FIS.SalesAmount) AS Bedrag , 0 , 0 FROM FactInternetSales AS FIS INNER JOIN DimProduct AS DP ON FIS.ProductKey = DP.ProductKey INNER JOIN DimCustomer AS DC ON FIS.CustomerKey = DC.CustomerKey INNER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey GROUP BY DC.FirstName,DC.LastName, DC.CustomerKey , DT.CalendarYear,DT.WeekNumberOfYear -- Sorting is very important here! ORDER BY DC.FirstName,DC.LastName , DT.CalendarYear,DT.WeekNumberOfYear |
Zoals in bovenstaand voorbeeld is te zien sommeren we per klant per week de aantallen verkochte artikelen en het orderbedrag. De waarden AantalCumulatief en BedragCumulatief zetten we op 0.
De tijdelijke tabel is nu gevuld met alle internet sales per klant. Vervolgens gaan we de laatste twee kolommen berekenen, hier gaat het om. Dit is een heel grappig truukje en zeer effectief!
UPDATE @Sales SET @RunningTotalAantal = AantalCumulatief = (CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalAantal + Aantal ELSE AantalEND) ,@RunningTotalBedrag = BedragCumulatief = (CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalBedrag + Bedrag ELSE BedragEND) ,@KlantNaam = KlantNaam FROM @Sales AS S SELECT * FROM @Sales |
Wat je hier doet is de variable RunningTotalAantal vullen met AantalCumulatief. Deze is bij het eerste record leeg. Deze “Aantal Cumulatief” wordt vervolgens gevuld met het veld Aantal. Als we het eerste records pakken is klantnaam <> @Klantnaam, deze variable is immers nog niet gevuld. RunningTotalAantal en AantalCumulatief worden nu gevuld met de waarde Aantal. Hetzelfde gebeurt er bij de kolom BedragCumulatief. Nu komt de truuk, @KlantNaam = Klantnaam. De variabele wordt gevuld met de huidige klantnaam. Dit doen we om te bepalen of we bij een nieuwe klant zijn, dan moet er namelijk weer opnieuw worden begonnen met tellen. Je moet even goed naar de SQL kijken om het te begrijpen.
Deze methode kun je dus toepassen om statistiekkolommen in je Datawarehouse uit te rekenen. Ik had het nodig om aantal gewerkte uren bij klanten uit te rekenen per medewerker. Nu zul je op AdventureWorksDW niet veel performancewinst boeken t.o.v.self joins of cursors maar als je grote tabellen hebt met een paar miljoen rijen zal dit echt snel gaan. Ik heb dit getest op een tabel met 5 miljoen records. Het uitrekenen van de running totals duurde 1 minuut. Het bijwerken van de FactTable duurde ook 1 minuut.
Voor vragen, leave a message 🙂
