SQL Server 2005: kumulierte Abfrage mit OLAP SQL 99 Erweiterungen - Wie?

[Andreas Möhlenbrock]

Hallo zusammen,

ich nutze den SQL Server 2005 und suche nach einer Möglichkeit, Daten über Monate hinweg kumuliert auszugeben.
Dabei möchte ich nicht über die Analysis Services gehen, sondern den relationalen Weg gehen, aber gern die OLAP-SQL-Erweiterungen nutzen.
Für Oracle habe ich ein sehr schönes Beispiel unter http://www.oracle.com/global/de/community/tipps/analyticFunctions/index.html
gefunden, dabei wird die Query wie folgt verwendet:
<code>select MONAT, UMSATZ_EUR, WERBUNG_TV_EUR, WERBUNG_ZEITG_EUR, ANZAHL_VERKAEUFER, SUM(UMSATZ_EUR) over ( partition by JAHR order by MONAT rows between unbounded preceding and current row ) as KUM_UMSATZ from VERKAUF</code>

Das SQL-Server-2005-Skript habe ich mir angepasst (NUMBER in NUMERIC geändert), ich poste es hier einfach noch einmal rein:
<code>
CREATE table "VERKAUF" (
"JAHR" numeric(4),
"MONAT" numeric(2),
"UMSATZ_EUR" numeric(10,2),
"WERBUNG_TV_EUR" numeric(10,2),
"WERBUNG_ZEITG_EUR" numeric(10,2),
"ANZAHL_VERKAEUFER" numeric(10)
)
insert into verkauf values ('2004','11',130000, 14000, 400, 10)
insert into verkauf values ('2004','12',140000, 13200, 700, 10)
insert into verkauf values ('2005','01',100000, 9000, 1000, 10)
insert into verkauf values ('2005','02',103000, 9500, 900, 10)
insert into verkauf values ('2005','03',112000, 10000, 1500, 10)
insert into verkauf values ('2005','04',90000, 12000, 1000, 8)
insert into verkauf values ('2005','05',98000, 13000, 1000, 8)
insert into verkauf values ('2005','06',70000, 4000, 5000, 9)
insert into verkauf values ('2005','07',109010, 12000, 2000, 15)
insert into verkauf values ('2005','08',120000, 10000, 500, 15)
insert into verkauf values ('2005','09',130000, 9000, 1000, 15)
insert into verkauf values ('2005','10',150000, 15000, 1000, 15)
insert into verkauf values ('2005','11',170000, 18000, 1000, 15)
insert into verkauf values ('2005','12',200000, 18000, 1000, 15)
insert into verkauf values ('2006','01',160000, 10000, 1000, 10)
insert into verkauf values ('2006','02',140000, 10200, 1500, 10)
</code>

Wie kann ich nun im SQL Server 2005 die Umsätze für jedes Jahr mit jedem Monat aufsummiert (kumuliert) erhalten?

Viele Grüße

Andreas Möhlenbrock

[Andreas Kosch]

Hallo,

wenn nur T-SQL (Relationale Datenbankengine) genutzt werden soll, ist folgendes möglich:

<div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">SELECT </span>t.Monat,</p><p style="margin: 0px;">&nbsp; (<span style="color: blue;">SELECT SUM</span>(x.UMSATZ_EUR) </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">FROM </span>dbo.VERKAUF x </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">WHERE </span>x.Monat &lt;= t.Monat) <span style="color: blue;">AS </span><span style="color: #a31515;">'Umsatz '</span>,</p><p style="margin: 0px;">&nbsp; (<span style="color: blue;">SELECT SUM</span>(x.WERBUNG_TV_EUR) </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">FROM </span>dbo.VERKAUF x </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">WHERE </span>x.Monat &lt;= t.Monat) <span style="color: blue;">AS </span><span style="color: #a31515;">'Werbung TV (kumuliert)'</span>,</p><p style="margin: 0px;">&nbsp; (<span style="color: blue;">SELECT SUM</span>(x.WERBUNG_ZEITG_EUR) </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">FROM </span>dbo.VERKAUF x </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">WHERE </span>x.Monat &lt;= t.Monat) <span style="color: blue;">AS </span><span style="color: #a31515;">'Werbung Zeitg (kumuliert)' </span></p><p style="margin: 0px;"><span style="color: blue;">FROM </span>dbo.VERKAUF t</p><p style="margin: 0px;"><span style="color: blue;">GROUP BY </span>t.Monat</p></div>

Wenn nach dem Jahr und Monat gruppiert werden soll, kann die Unterabfrage entsprechend erweitert werden:

<div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">SELECT </span>t.Jahr, t.Monat,</p><p style="margin: 0px;">&nbsp; (<span style="color: blue;">SELECT SUM</span>(x.UMSATZ_EUR) </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">FROM </span>dbo.VERKAUF x </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">WHERE </span>x.Monat &lt;= t.Monat </p><p style="margin: 0px;">&nbsp;&nbsp; <span style="color: blue;">AND </span>x.Jahr = t.Jahr) <span style="color: blue;">AS </span><span style="color: #a31515;">'Umsatz (kumuliert)'</span></p><p style="margin: 0px;"><span style="color: blue;">FROM </span>dbo.VERKAUF t</p><p style="margin: 0px;"><span style="color: blue;">GROUP BY </span>t.Jahr, t.Monat</p></div>

Weitergehende Funktionen sind beim MS SQL Server 2005 über die SQL-Erweiterungen <b>DMX</b> (SQL Data Mining Extension) und <b>MDX</b> (Multidimensional Expression Language) verfügbar, wobei dort allerdings die Analysis Services die ausführende Stelle sind.

[Andreas Möhlenbrock]

Hallo Herr Kosch,

haben Sie wieder einmal vielen Dank für Ihre Hilfe. Das funktioniert ausgezeichnet, ich denke auch, dass ich das gut für meine Bedürfnisse anpassen kann. Vielen Dank noch einmal!

Viele Grüße

Andreas Möhlenbroc