oder die Frage, wie berechne ich das Produkt einer Spalte einer Tabelle und warum gibt es eigentlich keine PROD() Aggregatfunktion...

Die Vorgeschichte zu diesem Beitrag:
Wir erhalten immer wieder Anrufe von irgendwelchen Vertrieblern, die uns in den buntesten Farben ausmalen, welche tolle Investmentangebote sie haben, welche astronomische Rendite erzielbar sind und warum wir unbedingt in diese Produkte investieren sollen. Meist erledigen sich solche Anrufe recht schnell von ganz alleine, aber hin und wieder ist es ganz amüsant, mal näher hinzuhören. Neulich pries so Jemand ein Produkt an, welches "wie eine Rakete" in den letzten 6 Monaten 40% zugelegt hätte und in den letzten 5 Jahren immerhin noch eine bemerkenswerte Rendite von 8% per annum aufweisen könne. Als auf meine Frage, ob diese Performance gemäß anerkannter Performance Presentation Standards ermittelt wurde, keine zufriedenstellende Antwort zurückkam, war auch dieses Gespräch schnell beendet. Dennoch blieb irgendwie bei mir die Frage hängen, wie würde man die Performance einer Geldanlage im SQL Server ermitteln? Und, vielleicht noch wichtiger, wie würde man es richtig machen?

Ausgangsszenario:
Wir investieren Anfang 2000 100,- €. In den nachfolgenden Jahren entwickelt sich der Wert unserer Anlage gemäß folgender Übersicht

Wenn man jetzt das 5-Jahres-Mittel errechnet, ergibt sich (-0,5+0,6+0,15+0,4+-0,25)/5 = 0,08 => 8,0% pro Jahr innerhalb dieser 5 Jahre. Ein lukratives Investment könnte man meinen. Und mathematisch fehlerfrei nachgewiesen. ;-)
Stimmt, nur verschleiert die Verwendung des arithmetischen Mittels bei der Angabe der Performance hier die Tatsache, daß wir Geld verloren hätten, wenn wir in diese Anlage investiert hätten. Beweis? Okay!

So, aus unserem Startkapital von 100 € sind in 5 Jahren 96,6 € geworden. Dies ist die traurige Wirklichkeit hinter der Hochglanzfassade. Und genau diese Wirklichkeit wird durch das arithmetische Mittel verschleiert. Zum Einsatz kommen muß hier das geometrische Mittel. Dieses bezogen auf unser Beispiel ergibt sich als ((0.5 * 1,6*1,15*1,4*0,75) ^ 1/5) - 1 = -0,0069...
Die Rendite über die gesamte Laufzeit daraus beträgt ~ -3,4%.
So, genug der Finanzmathematik. Wie könnte eine Lösung in T-SQL aussehen?
Hm, zunächst mal mag man sich an dieser Stelle beklagen, warum es keine PROD() Aggregatfunktion gibt. Alles wäre so einfach, wenn man, ähnlich wie SUM(), eine eingebaute Funktion hätte, welche die Werte einer Spalte einer Tabelle miteinander multipliziert. Aber nein...

Also muß man selber tätig werden.

IF OBJECT_ID('tempdb.#t') >0 
 DROP TABLE #t

CREATE TABLE #t
(
 fiscal_year INT
 , fund_return FLOAT
)
INSERT INTO #t VALUES(2000, -.5)
INSERT INTO #t VALUES(2001, 0.6)
INSERT INTO #t VALUES(2002, .15)
INSERT INTO #t VALUES(2003, 0.4)
INSERT INTO #t VALUES(2004, -.25)

ist die Ausgangsbasis. Die erste Lösung, die naheliegen könnte, ist der Einsatz eines Cursor, der durch die einzelnen Zeilen läuft und die Multiplikation vornimmt.

DECLARE @fund_return FLOAT
DECLARE @result FLOAT
SELECT @fund_return = 1, @result = 1

DECLARE slash_cursors CURSOR FOR 
 SELECT fund_return
   FROM #t

OPEN slash_cursors
FETCH NEXT FROM slash_cursors  INTO @fund_return
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @result = (1+@fund_return) * @result
 FETCH NEXT FROM slash_cursors  INTO @fund_return
END

SELECT 
 (POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + ((POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100) *
 (SELECT COUNT(*) FROM #t) AS [Was aus 100 € in 5 Jahren wurde]

CLOSE slash_cursors
DEALLOCATE slash_cursors
GO

Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

So, nun haben wir erst einmal unser Ergebnis und können direkt eine Performanceoptimierung durch Eliminierung des Cursors angehen. Dazu transformieren wir die Tabelle und machen so aus 5 Zeilen und 1 Spalte 1 Zeile mit 5 Spalten.

SELECT 
 (POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1) 
 * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + ((POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1) * 100) * 5
   AS [Was aus 100 € in 5 Jahren wurde]
 FROM
 (SELECT
  MAX(CASE fiscal_year WHEN 2000 THEN 1+fund_return ELSE NULL END) AS t1
  , MAX(CASE fiscal_year WHEN 2001 THEN 1+fund_return ELSE NULL END) AS t2
  , MAX(CASE fiscal_year WHEN 2002 THEN 1+fund_return ELSE NULL END) AS t3
  , MAX(CASE fiscal_year WHEN 2003 THEN 1+fund_return ELSE NULL END) AS t4
  , MAX(CASE fiscal_year WHEN 2004 THEN 1+fund_return ELSE NULL END) AS t5
  FROM #t) x

Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

So, den Cursor sind wir los, aber wirklich zufriedenstellen kam diese Lösung auch nicht, da sie zu unflexibel und starr ist. Mit dieser Konstruktion eine Performance über 30 Jahre zu ermitteln, ergibt ein ziemlich monströses CASE. Und auch in der Handhabung des Zeitraumes ist man starr und unflexibel, da dieser hartkodiert ist. Dies kann man zwar durch den Einsatz einer Variablen anstelle der fest eingegebenen Jahreszahlen vermeiden, ist aber, im Vergleich zu nächsten Variante, nicht wirklich prickelnd. Die letzte Variante profitiert von einer Eigenschaft des geometrischen Mittels. Und zwar kann man das geometrische Mittel auch darstellen, als e potenziert mit dem Durchschnitt der logarithmierten Einzelwerte.

SELECT (EXP(AVG(LOG(1+fund_return)))-1)  * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + (100 * (EXP(AVG(LOG(1+fund_return)))-1)  * COUNT(*)) AS [Was aus 100 € in 5 Jahren wurde]
  FROM #t
  
Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

Was noch erwähnenswert ist, ist die Tatsache, daß der natürliche Logarithmus nur für Zahlen größer 0 definiert ist. Der Versuch, 0 oder eine negative Zahl zu verwenden, resultiert in einen Domänenfehler. Warum dies so ist, steht unter anderem hier.
Für unser Beispiel würde ein Domänenfehler genau dann auftreten, wenn ein Wert -1 wäre. Dies wäre dann auch gleichbedeutend mit einem Totalverlust des eingesetzten Kapital. Ist zwar sehr unwahrscheinlich, aber eine gute Implementierung sollte dies berücksichtigen und abfangen.