Frank Kalis

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.

Man mag darüber denken, wie man will. Man mag sich auch streiten, ob dieses Thema in die Datenbank gehört oder nicht doch besser in die Präsentationsschicht. Ein bißchen ist das wie die Frage, was zuerst da war: Das Huhn oder das Ei. Beide Seiten haben irgendwie Recht und doch gleichzeitig wiederum auch nicht. Von daher überlasse ich es dem jeweiligen Benutzer, für welchen Ansatz er sich entscheidet.

Genug der einleitenden Worte...
Das Problem an sich kennt wahrscheinlich jeder. Man hat eine numerische Spalte in die durch gewisse Logik ein monoton steigender Wert eingegeben wird (oder werden soll). Verändern sich jedoch im Laufe der Zeit die Daten, d.h. Daten werden gelöscht und hinzugefügt, entstehen auf diese Art und Weise Löcher in der Sequenz. Wenn man diese Löcher direkt bei Eingabe stopfen will, kann man folgendes machen:

CREATE TABLE t
(
 k1 INT NOT NULL
 , c1 CHAR NOT NULL
 CONSTRAINT pk_t PRIMARY KEY(k1)
)
GO

ist unsere Augangssituation. Wir wollen direkt beim INSERT über eine DEFAULT Einschränkung entweder die Sequenz fortführen oder aber, die Löcher stopfen, sofern vorhanden. Dazu schreiben wir folgende UDF:

CREATE FUNCTION dbo.CloseMyGaps() RETURNS INT
AS
BEGIN
 RETURN
  CASE
   WHEN EXISTS
        (SELECT * 
           FROM t 
          WHERE k1 = 1)
   THEN (SELECT MIN(t1.k1) + 1
           FROM t t1 
            LEFT OUTER JOIN t t2
            ON t1.k1 = t2.k1 - 1
          WHERE t2.k1 IS NULL)
   ELSE 1
  END
END
GO

Und definieren diese Funktion als DEFAULT für die Spalte k1 in der Tabelle.

ALTER TABLE t ADD CONSTRAINT d_k1 DEFAULT dbo.CloseMyGaps() FOR k1
GO

Jetzt können wir ein bißchen mit Eingaben spielen

INSERT INTO t(c1) VALUES('a')
INSERT INTO t(c1) VALUES('b')
INSERT INTO t(c1) VALUES('c')
INSERT INTO t(c1) VALUES('d')

SELECT * 
  FROM t

k1          c1   
----------- ---- 
1           a
2           b
3           c
4           d

(4 row(s) affected)

Wie man sieht, funktionieren INSERTs tadellos. Aber schließlich haben wir auch bisher nicht die bestehende Sequenz unterbrochen. Was passiert nun bei DELETEs?

DELETE FROM t WHERE k1 IN (2,3)
INSERT INTO t(c1) VALUES('d')
INSERT INTO t(c1) VALUES('d')
DELETE FROM t WHERE k1 =1 
INSERT INTO t(c1) VALUES('f')

k1          c1   
----------- ---- 
1           f
2           d
3           d
4           d

(4 row(s) affected)

Man sieht, die Sequenz bleibt einwandfrei.
Inwieweit sich die UDF auf die Performance auswirkt, muß man im Einzelfall testen. Für ein ausgelastetes System mit vielen Datenänderungen dürfte es eher nicht geeignet sein.

Da sitzt man abends völlig entspannt am Schreibtisch, blättert in alten Unterlagen aus der Studienzeit weil man sich an gewisse mathematische Zusammenhänge nicht mehr so direkt aus dem Kopf erinnern kann, und - zack - stolpert man über Integer Arithmetik. Die (durchaus verworrene) Assoziationskette zu SQL Server und DATETIMEs führt jedenfalls dann dazu, daß das Mathebuch erst einmal Mathebuch ist und bleibt und wir einen Selbstversuch in Datumsarithmetik unternehmen.

Ganze Geschichte »

Im SQL Server 2000 kann eine Stored Procedure bis zu 2.100 Parameter haben. Jeder einzelne dieser Parameter kann entweder Input- oder Output Parameter sein.

Ganze Geschichte »

Die Anforderung, Auswertungen zu erstellen, die die Daten auf stündlicher Basis aufbereiten und auswerten, findet sich in vielen Bereichen. Zum Beispiel, Anzahl der Telefonate pro Mitarbeiter pro Stunde, Durchschnittswerte irgendwelcher Meßwerte pro Stunde usw... Hier ist eine einfache, aber effektive Methode, solche Anforderungen umzusetzen:

Ganze Geschichte »

Microsoft Access bietet die Option "Nullwerte ignorieren" bei Indices an. Wie kann man das auf den SQL Server übertragen?...

Die schlechte Nachricht gleich vorweg: Ein direktes Äquivalent existiert im SQL Server nicht! Erstellt man einen Index als UNIQUE, kann dieser genau einmal einen NULL Marker enthalten. Beispiel:

CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL)
CREATE UNIQUE NONCLUSTERED INDEX ix_test ON Table1(c1)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
DROP TABLE Table1

Server: Msg 2601, Level 14, State 3, Line 4
Cannot insert duplicate key row in object 'Table1' with unique index 'ix_test'.
The statement has been terminated.

Einen Index kann man also nicht nutzen. Auch nicht besser sieht es mit einer UNIQUE Einschränkung aus:

CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL
 CONSTRAINT c_c1 UNIQUE)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
DROP TABLE Table1

Server: Msg 2627, Level 14, State 2, Line 4
Violation of UNIQUE KEY constraint 'c_c1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.

Um es abzukürzen - und nochmals zu wiederholen - eine eingebaute Möglichkeit gibt es nicht. Man muß also selber tätig werden. 4 Möglichkeiten stehen hierfür zur Verfügung.

1. Einen Indexed View benutzen.

SET NOCOUNT ON
CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL)
GO
CREATE VIEW Table1_Unique_Non_NULL
 WITH SCHEMABINDING
 AS
  SELECT c1
   FROM dbo.Table1
   WHERE c1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uc1 ON Table1_Unique_Non_NULL(c1)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
SET NOCOUNT OFF
SELECT * FROM Table1_Unique_Non_NULL
DROP VIEW dbo.Table1_Unique_Non_NULL
DROP TABLE Table1

c1          
----------- 
1

(1 row(s) affected)

Wie man sieht, wird der 2.te NULL Marker widerspruchslos in die Tabelle eingefügt. Versucht man jedoch:

INSERT INTO Table1 SELECT 4,1

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Table1_Unique_Non_NULL' with unique index 'uc1'.
The statement has been terminated.

2. Eine Einschränkung auf eine berechneten Spalte verwenden.

SET NOCOUNT ON
CREATE TABLE Table1 
(cID INTEGER PRIMARY KEY 
 , c1 INTEGER NULL
 , foolme AS (CASE WHEN c1 IS NULL THEN cID END)
 , CONSTRAINT cc1 UNIQUE (c1,foolme)
)

INSERT INTO Table1 
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL 
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1

cID         c1          foolme      
----------- ----------- ----------- 
2           NULL        2
3           NULL        3
1           1           NULL

(3 row(s) affected)

Und auch hier führt:

INSERT INTO Table1 SELECT 4, 1

Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'cc1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.

3. Einen Trigger verwenden:

SET NOCOUNT ON
CREATE TABLE Table1 
(cID INTEGER PRIMARY KEY 
 , c1 INTEGER NULL
)
GO
CREATE TRIGGER IgnoreNulls
ON Table1 FOR UPDATE, INSERT
AS
  IF EXISTS
   (SELECT *
    FROM Inserted AS I
    JOIN Table1 AS t1
     ON I.c1 = t1.c1 AND I.cID<>t1.cID)
   BEGIN
    ROLLBACK TRAN
    RAISERROR('Nene...is nicht!',16,1)
   END
GO
INSERT INTO Table1 
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL 
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1

cID         c1          
----------- ----------- 
1           1
2           NULL
3           NULL

(3 row(s) affected)

Und auch hier der vollständigkeithalber die Probe

INSERT INTO Table1 SELECT 4, 1

Server: Msg 50000, Level 16, State 1, Procedure IgnoreNulls, Line 11
Nene...is nicht!

Vereinzelt findet man auch noch eine vierte Methode erwähnt. Die entsprechende Spalte in eine eigene Tabelle zu überführen, sie zum Primärschlüssel dieser Tabelle machen und eine 1:1 Beziehung zwischen beiden Tabelle einrichten. Diese Methode möchte ich hier nicht weiter besprechen. IMHO ist dies nur ein gangbarer Weg, wenn man dies direkt beim Design der Datenbank und der Applikation berücksichtigt. Eine nachträgliche Änderung, um dieses Indexverhalten zu erzielen, ist mit einem nicht zu unterschätzenden Aufwand verbunden.

Die Reihenfolge, in welcher die verschiedenen Ansätze erwähnt wurden, stellt gleichzeitig eine gewisse Wertung dar. IMHO erscheint der 1. Ansatz mit dem Indexed View am geeignetsten. Der Index sollte das Ganze recht schnell machen und ausserdem läßt sich der View wiederverwenden. Der Index auf der berechneten Spalte sollte ebenfalls noch eine recht gute Performance bringen. Das Schlußlicht wird der Trigger sein.

Unter den diversen Aggregatfunktionen des T-SQL Arsenals nimmt COUNT() einen einmaligen Platz ein, weil dies die einzige Funtion ist, die "NULL-aware" ist. Das heißt, je nach Verwendung werden NULL Marker berücksichtigt oder nicht.

Ganze Geschichte »

Angenommen man hat Integer Daten in der Form 20050623 vorliegen, die ein Datum repräsentieren sollen und deshalb in einen DateTime Wert konvertiert werden sollen. Wie schön einfach wäre es nun, wenn eine direkte Konvertierung funktionieren würde:

DECLARE @i INT
SET @i = 20050623
SELECT CAST(@i AS DATETIME)

Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.

Ein Weg nun doch noch zum gewünschten Ergebnis zu kommen, ist die Konvertierung zuerst in eine Zeichenfolge und dann anschließend in DateTime. Etwas so:

SELECT CAST(CAST(@i AS CHAR(8)) AS DATETIME)
                                                       
------------------------------------------------------ 
2005-06-23 00:00:00.000

(1 row(s) affected)

Funktioniert einwandfrei!

Nun kann man sich aber noch einige Tastaturanschläge sparen, wenn man sich eines kleinen Tricks bedient:

SELECT CAST(LTRIM(@i) AS DATETIME)
                                                       
------------------------------------------------------ 
2005-06-23 00:00:00.000

(1 row(s) affected)

Funktioniert ebenfalls einwandfrei. Aber warum?
Der "Trick" dabei ist, daß man sich den Rückgabetyp mancher Stringfunktionen zunutze macht und diese Funktionen die Konvertierung in eine Zeichenfolge durchführen läßt. So lassen sich, zum Beispiel, LTRIM(), RTRIM() und STR(@i,8) in obiges Statement einbauen und all produzieren den gewünschten Effekt.