Frank Kalis

Heute ist der 05.12.2007. Was muss ich nun machen, um den letzten Sonntag davor in T-SQL zu ermitteln?
Ein Blick in den Kalendar zeigt, daß als Ergebnis der 02.12.2007 herauskommen muss.

Ganze Geschichte »

Stark vereinfacht gesagt befindet sich ein Login auf Server-Ebene und erlaubt die Verbindung zu SQL Server, während sich ein User auf Datenbank-Ebene befindet und den Zugriff auf die Datenbank erlaubt.

In diesem Beitrag haben wir uns mit der Rückgabe von Werten einer Spalte als separierte Liste beschäftigt. Vielfach sieht man aber auch die Anforderung, diese Liste auf der Basis eines bestimmten Schlüsselwertes zu generieren.

Ganze Geschichte »

In SQL Server 2000 und früher funktioniert folgendes:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME,'IsIdentity')=1;

SELECT su.name AS TABLE_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM dbo.syscolumns AS sc
  JOIN dbo.sysobjects AS so
    ON sc.id = so.id
    JOIN dbo.sysusers AS su
      ON so.uid = su.uid
 WHERE sc.status & 0x80 = 0x80;

Im SQL Server 2005 sollte man folgendes verwenden:

SELECT su.name AS SCHEMA_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM sys.columns AS sc
  JOIN sys.objects AS so
    ON sc.object_id = so.object_id
    JOIN sys.schemas su
      ON su.schema_id = so.schema_id
 WHERE sc.is_identity = 1; 

Jein! Die direkte Syntax SELECT … FROM EXEC myProcedure ist ungültig und liefert einen Fehler. Es gibt aber einen Umweg über einen View und OPENQUERY:

Ganze Geschichte »

Typisches Einsatzgebiet ist die Implementierung einer eigenen Protokollierfunktionalität, die Änderungen an Tabellen mitschneidet. Die Arbeitsweise ist stets die gleiche, nur die Tabelle ist immer eine andere.

Ganze Geschichte »

Vor einiger Zeit kam eine interessante Frage in der Newsgroup auf. Gesucht wurde der am häufigsten vorkommende Preis pro Artikelgruppe. Also der Modus je Artikelgruppe. Gegeben ist das folgende Ausgangsszenario:

CREATE TABLE #t (ArtikelGruppe CHAR(2), Preis DECIMAL(8,2))
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 2.1)
INSERT INTO #t VALUES('GR', 2.2)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)

Gemäß der Aufgabenstellung soll nun

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

als Ergebnis erscheinen. Doch diese an und für sich scheinbar einfache Frage kann sich jedoch zu einem mittelschweren Brainteaser entwickeln. Den am häufigsten vorkommenden Preis über sämtliche Artikel kann man noch relativ leicht und intuitiv bestimmen mittels:

SELECT TOP 1 WITH TIES ArtikelGruppe, Preis, COUNT(*) Anzahl
  FROM #t
 GROUP BY ArtikelGruppe, Preis
  ORDER BY Anzahl DESC

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
GR            2.30       3

(1 row(s) affected)

Doch wie kommt nun die Gruppierung nach Artikelgruppen hier ins Spiel? Christoph Muthmann kam zu folgendem Lösungsansatz:

--Christoph's Ansatz
SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) >= 
   (SELECT MAX(Anzahl) 
      FROM 
      (SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
         FROM  #t b
        WHERE b.artikelgruppe = a.artikelgruppe
         GROUP BY artikelgruppe, preis) c)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

während mein ursprünglicher Ansatz folgendermaßen aussah:

--Mein ursprünglicher Ansatz
SELECT ArtikelGruppe,Preis, COUNT(*) Anzahl
  FROM #t a 
 GROUP BY ArtikelGruppe,Preis 
  HAVING NOT EXISTS 
   (SELECT *
      FROM #t b 
     WHERE b.ArtikelGruppe = a.ArtikelGruppe 
     GROUP BY b.Preis
     HAVING COUNT(b.ArtikelGruppe) > COUNT(a.ArtikelGruppe)) 

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)     

Vergleicht man nun die Ausführungspläne wird man bei meinem Ansatz einen "Lazy Spool" entdecken. Für gewöhnlich ist dies ein schlechtes Omen und man sollte versuchen, die Abfrage so umzuformulieren, daß dieser logische Operator nicht mehr auftaucht. Da ich aber Christoph's Ansatz sowieso für eleganter halte, formuliere ich lieber diesen ein bißchen um, um einen deutlich schlankeren und damit wahrscheinlich effektiveren Ausführungsplan zu erhalten:

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT MAX(Anzahl)
     FROM
      (SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis) x
         WHERE ArtikelGruppe = t.ArtikelGruppe)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

Christoph hat dann die drei obigen Statements mal an einem repräsentativen Datenbestand ausprobiert. Die Tabelle hat 250.000 Zeilen und umgesetzt auf das Beispiel 8 Artikelgruppen mit 358 verschiedenen Preisen. Ohne Verwendung von Indexes kommen dabei folgende Laufzeiten heraus:

• Christoph's Ansatz: 5.390 Millisekunden
• Mein Ansatz: 19.033 Millisekunden
• Neuer Ansatz: 436 Millisekunden

Erstellt man jetzt einen Index auf (ArtikelGruppe, Preis) kommt es zu folgenden Laufzeiten:

• Christoph's Ansatz: 153 Millisekunden
• Mein Ansatz: 500 Millisekunden
• Neuer Ansatz: 110 Millisekunden

Beeindruckend kann man erkennen, wie stark manche Abfragen durch geeignete Indexes beschleunigt werden können.

Der Vollständigkeithalber kann man alternativ zu MAX() zwar auch diverse TOP Varianten einsetzen:

SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) = 
   (SELECT TOP 1 COUNT(*) 
      FROM #t
     WHERE a.ArtikelGruppe = ArtikelGruppe
     GROUP BY ArtikelGruppe, Preis
     ORDER BY COUNT(*) DESC) 

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT TOP 1 Anzahl
     FROM
      (SELECT TOP 100 PERCENT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis 
         ORDER BY Anzahl DESC) x
        WHERE ArtikelGruppe = t.ArtikelGruppe)

die ebenfalls das gewünschte Ergebnis bringen, aber auch keine entscheidende Verbesserung im Vergleich zu den beiden in der Newsgroup geposteten Statements bzgl. der Ausführung bringen.

Wie in einem der letzten Beiträge dieser Kategorie angekündigt, möchte ich hier auf der Website meine Implementierung der Berechnung der Yield to maturity vorstellen. Andere Begriffe für Yield to maturity sind auch arithmetische Rendite oder Effektivzins. Sämtliche Begriffe sind durchaus gebräuchlich und werden verwendet, um ein und diesselbe Sache zu beschreiben. Und so werden auch in diesem Beitrag diese Begriffe synonym verwendet.
Einige Prämissen gleich vorweg: Wir betrachten hier in diesem Beitrag nur den einfachsten Fall der Effektivzinsberechnung und beschränken uns auf die Diskussion eines festverzinslichen Wertpapieres mit jährlicher Zinszahlung ohne eingebettete Derivate mit einem Rückzahlungskurs von 100%. Aus Gründen der Vereinfachung liegen die erwarteten Cash Flows bereits vor. In der Praxis würde man diese wahrscheinlich erst zur Laufzeit generieren. Auch beschränken wir uns auf die am deutschen Kapitalmarkt mittlerweile eher unübliche 30/360 Zinsmethode. Die Erweiterung für die diversen Tageskonventionen überlasse ich dem geneigten Leser, dem ich mit dem hier vorgestellten Skript schon genug Basisarbeit abgenommen habe. ;-)

Das fundamentale Prinzip jeglicher Bewertung ist, daß der Wert einer Kapitalanlage dem Present Value (oder Barwert) der erwarteten Zahlungsströme entspricht. Dabei spielt es keinerlei Rolle, welcher Art diese Kapitalanlage ist. Von daher setzt sich jeder Valuierungsprozeß aus folgenden 3 Schritten zusammen:

1. Bestimmung der (erwarteten) Cash Flows.
2. Festlegung eines angemessenenen Diskontsatzes (bzw. angemessener Diskontsätze).
3. Berechnung des Present Values der Zahlungsströme aus 1. anhand der Werte aus 2.

So, zunächst einmal hört sich das nicht weiter schwer an. Ist es eigentlich auch nicht. Aber man muß sich verdeutlichen, daß dies ein iterativer Prozeß ist, der nur bedingt mit dem mengenbasierten Ansatz relationaler Datenbanken zu vereinbaren ist. Man berechnet die Rendite jedes einzelnen Wertpapieres indem man solange eine Schleife durchläuft, bis das Ergebnis hinreichend genau ist. Jawohl, hinreichend genau. Das Ganze ist ein Trial-and-Error Prinzip.

Genug der Worte. Ein Beispiel: Gegeben sei ein Wertpapier mit einem jährlichen Zinskupon von 5,00% und einer Endfälligkeit in genau 4 Jahren. Der Preis für das Papier beträgt 95,92. Frage: Welche Yield-to-maturity hat dieses Papier? Antwort: ca.6,1824% Lösung: Gemäß unserem obigen Schema, benötigen wir zuerst die Cash Flows.

CREATE TABLE Cash_Flows 
( 
 cashflow DECIMAL(8,2), 
 valuta DATETIME 
) 
INSERT INTO Cash_Flows VALUES (-95.92,'20060115') 
INSERT INTO Cash_Flows VALUES (5,'20070115') 
INSERT INTO Cash_Flows VALUES (5,'20080115') 
INSERT INTO Cash_Flows VALUES (5,'20090115') 
INSERT INTO Cash_Flows VALUES (105,'20100115')

So, am Anfang steht ein negativer Cash Flow (d.h. eine Auslage), da wir diesen Preis bezahlen müssen, um das Papier zu erwerben. Danach erhalten wir 4 Jahre lang einmal pro Jahr die Kuponzahlung iHv 5 und zusätzlich im letzten Jahr den Nominalbetrag (= 5 + 100 = 105). Den eigentlichen Code wird man zweckmäßigerweise in eine UDF packen. Diese könnte folgendermaßen aussehen:

CREATE FUNCTION yield_to_maturity(@issue_date SMALLDATETIME)
RETURNS DECIMAL(15,14) 
AS 
BEGIN 
 DECLARE @ytm_tmp FLOAT
 DECLARE @ytm FLOAT
 DECLARE @pv_tmp FLOAT
 DECLARE @pv FLOAT

 SET @ytm_tmp = 0
 SET @ytm = 0.1
 SELECT @pv_tmp = SUM(cashflow) FROM Cash_Flows
 SET @pv = 
 (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta)* 30 + DAY(valuta)-DAY(@issue_date) 
  - 
  CASE 
   WHEN(@issue_date)>=30 AND DAY(valuta) = 31 
   THEN 1 
   ELSE 0 
  END)/  360.0 ))
    FROM Cash_Flows)

 WHILE ABS(@pv) >= 0.000001
 BEGIN
  DECLARE @t FLOAT
  SET @t = @ytm_tmp
  SET @ytm_tmp = @ytm
  SET @ytm = @ytm + (@t-@ytm)*@pv/(@pv-@pv_tmp)
  SET @pv_tmp = @pv
  SET @pv =
  (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta) * 30 + DAY(valuta)-DAY(@issue_date) 
   -
   CASE
    WHEN(@issue_date)>=30 AND DAY(valuta) = 31 
    THEN 1 
    ELSE 0 
   END) /360.0))
     FROM Cash_Flows)
 END
 RETURN @ytm 
END
GO

Da das Ganze ein Näherungsverfahren ist, hat man einen gewissen Spielraum bei der Wahl der oberen Grenze bei der Initialisierung der Variablen. 0,1 (=10%) ist idR ein guter Startwert. Danach läuft man solange durch die WHILE Schleife, bis die Abbruchbedingung erfüllt ist. Und hier erkennt man dann auch sehr schön den iterativen Ansatz dieses Verfahrens, für das dann andere Programmiersprachen wie C++ oder VB wesentlich besser geeignet sind. Aufgerufen wird die UDF dann mit

SELECT dbo.yield_to_maturity('20060115')
                  
----------------- 
.06182374295818

Rechnet man unser Beispiel mit einer spezialisierten Software nach wird man unter Umständen ab irgendwelchen Nachkommastellen Abweichungen bemerken. In der Regel betrifft dies die 6.te bis 8.te Nachkommastelle. Aber die fallen dann auch nicht mehr wirklich dramatisch ins Gewicht. Differenzen ab der 6.ten Nachkommastelle ignoriere ich einfach. Obwohl ich nicht wirklich weiß, woran dies liegt. Ich schiebe dies dann gerne auf die Implementierung des FLOAT Datentypen in SQL Server.

< rant mode on >
So, zum Schluß ein Wort in eigener Sache: Direkt in den Tagen nachdem ich in diesem Beitrag angekündigt hatte, eine T-SQL basierte Yield-to-maturity Formel zu veröffentlichen, habe ich gleich mehrere Mails von Leuten erhalten, die entweder genau wissen wollten, wann es denn soweit sei oder ob diese oder jene Besonderheit eingebaut sei und ob ich auch beabsichtige, noch weitere Renditemaße zu veröffentlichen. Ich habe zwar geahnt, daß diese Formel für viele vielleicht interessant sein könnte, war aber vollkommen von der teilweisen Dreistigkeit dieser Mails überrascht.
Ich möchte deshalb an dieser Stelle eines ganz klar stellen:

• Ich habe lange gezögert, ob ich nun diesen Beitrag veröffentlichen soll oder nicht, aber nun mache ich meine damals publizierte Ankündigung wahr. Nicht mehr, aber auch nicht weniger. Versprochen ist versprochen ... usw...
• Ich habe meinen Originalalgorithmus aber nach diesen Mails absichtlich noch weiter verschlankt, damit diejenigen Leute, die es offensichtlich selber nicht fertigbringen, diese Formel abzubilden, wenigstens noch einiges an eigenem Gehirnschmalz investieren müssen, um die Funktion tatsächlich praxistauglich zu machen.
• Ich werde keine weiteren Renditemaße veröffentlichen (was ich bis zu diesen Mails tatsächlich vorhatte).

Ich sehe nicht ein, daß ich hier über das hinaus, was ich eh schon mache, kostenlos Wissen vermittle, womit andere Leute potentiell eine Menge Geld verdienen können. Ich habe kein Problem damit, hier Algorithmen zu veröffentlichen, die Anderen Zeit, Nerven und Geld sparen. Ganz im Gegenteil! Aber diese Mails gaben mir das Gefühl, nur ein Ideenlieferant zu sein, den man ausnutzen kann. Aus diesem Grund schiebe ich dem einen Riegel vor, und behalte mein Wissen in diesem Bereich ab sofort für mich.

Sorry, aber mal wieder haben einige schwarze Schafe der Gesamtheit die Suppe versalzen!
< rant mode off >