Kategorie: "SQL Server"
Auf volle Stunden skalieren
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:
Access Indexverhalten imitieren
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.
Kleines Beispiel über COUNT()
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.
Integer in Datum umwandeln
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.
Vorsicht bei der Verwendung von ISNUMERIC()
Liest man sich die Onlinehilfe von SQL Server durch und gelangt an das Thema ISNUMERIC(), erhält man den Eindruck, daß dies eine einfache, schnelle und sichere Methode ist, um zu überprüfen, ob ein gegebener Ausdruck in einen von SQL Server unterstützten numerischen Datentypen umgewandelt werden kann. Also, in einen der Datentypen: INTEGER, FLOAT (REAL), DECIMAL (NUMERIC) und MONEY.
Present und Future Value einer Einmalanlage
Der Spatz in der Hand ist mehr wert als die Taube auf dem Dach. So ähnlich lautet ein beliebtes Sprichwort. Übertragen auf Geld würde man wahrscheinlich sagen, daß 1 Euro in der Hand heute mehr wert ist als 1 Euro, den man irgendwann in der Zukunft erhält. Warum? Nun, den Euro, den ich heute habe, kann ich investieren, erhalte dafür z.B. Zinsen und habe so schließlich mehr Geld als diesen Euro in der Zukunft.
Den Vorgang von Present zu Future Value nennt man "Compounding". Damit ist ein arithmetischer Prozess definiert, der den Endwert eines Cash Flows oder einer Serie von Cash Flows feststellt, wenn man "Compounded Interest" unterstellt. Auf Deutsch würde man hier Zinseszinsrechnung sagen.
So, genug der Einleitung. Skizzieren wir einmal ein Beispielszenario zu unserer Fragestellung:
Angenommen, ich habe 1.000€ und kann diese zu einem Zinssatz von 6,00% investieren. Wieviel Geld habe ich nach einem Jahr?
Formelmäßig könnte man das folgendermaßen notieren:
FV=PV+PV*i
=> FV=PV*(1+i)^1
=> 1.000*(1+0,06)^1
=> 1.060
Dies ist einfach nachzuvollziehen und auch einfach in T-SQL nachzubauen:
DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @n=1
SELECT
ROUND(@pv*POWER(1+@i, @n),2)
-----------------------------------------------------
1060.0
(1 row(s) affected)
Hier an diesem Beispiel wird bereits eine weitere "Besonderheit" deutlich. Ich verwende idR FLOAT Daten und Datentypen. Ich habe hier die Erfahrung gemacht, daß FLOAT gerade bei komplexeren Berechnungen "genauer" rechnet als z.B. DECIMAL. Zumindest ist der Grad der "Abweichung vom exakten Ergebnis" für mich und die Zwecke dieser Berechnungen akzeptabel. Ich möchte aber darauf hinweisen, daß der geneigte Leser sich sein eigenes Urteil bilden sollte und, abhängig davon, wofür er diese Berechnungen einsetzt, beide Alternativen (FLOAT und DECIMAL) anhand seiner eigenen Daten durchspielen sollte. Ich vermute, die Antwort darauf, was der "besser" geeignete Datentyp ist, lautet: "Das hängt davon ab...".
So, zurück zu unserer Einführung. Wie wir gesehen haben, werden nach einem Jahr aus 1.000€ bei 6,00% Zins 1.060€. Angenommen ich lasse diese 1.000 aber nun 5 Jahre zu diesem Zinssatz angelegt und kann gleichzeitig die erhaltenen Zinsen ebenfalls zu diesem Zinssatz investieren. Wieviel Geld habe ich nach 5 Jahren?
FV=PV*(1+i)(1+i)(1+i)(1+i)(1+i)
=> PV*(1+i)^5
=> 1.000*(1+0.06)^5
=> 1.338,23
Wenn man sich jetzt die T-SQL Implementation ansieht, erkennt man, daß man eigentlich "nur" die Variable @n auf den neuen Wert anpassen muß:
DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @n=5
SELECT
ROUND(@pv*POWER(1+@i, @n),2)
-----------------------------------------------------
1338.23
(1 row(s) affected)
Einfach.
Bis jetzt sind wir aber davon ausgegangen, daß unser Investment einmal im Jahr Zinsen ausschüttet. Gerade aber im amerikanischen Finanzraum ist eine häufigere Ausschüttung (halb- oder sogar vierteljährlich) üblich.
Unterstellen wir, daß in unserem 5 Jahres Beispiel, die Zinsschüttung halbjährlich erfolgt. Was müssen wir jetzt berücksichtigen?
Als Erstes rechnen wir den jährlichen Zinssatz in einen "periodengerechten" um. Also 6.00% / 2 = 3,00%.
Danach rechnen wir die Laufzeit in Jahren in Laufzeit pro Zinsperiode um. Also: 5 (Jahre) * 2 (x jährlich Zinsausschüttung) = 10 Zinsperioden.
So, in einer Formel ausgedrückt, hat sich damit nichts verändert.
DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.03, @n=10
SELECT
ROUND(@pv*POWER(1+@i, @n),2)
-----------------------------------------------------
1343.9200000000001
(1 row(s) affected)
Jetzt kann man die Umrechnung in periodengerechte Werte vorher vornehmen. Oder, was vielleicht geschickter und generischer ist, die Formel umstellen, indem einfach ein weiterer Parameter @m eingebaut wird. @m nimmt die Anzahl der Zinszahlungen pro Jahr auf. Damit sieht unsere Formel jetzt folgendermaßen aus:
DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @m FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @m=2, @n=5
SELECT
ROUND(@pv*POWER(1+@i/@m,@m*@n),2)
-----------------------------------------------------
1343.9200000000001
(1 row(s) affected)
Offensichtlich wird aus 1.000€ bei halbjährlicher Zinszahlung mehr als bei jährlicher Zinszahlung. Dies ist Schulmathematik und bedarf keiner weiteren Erklärung hier. Interessant aber ist die Frage nach der Effektivverzinsung meines Investments. Diese läßt sich durch die folgende Formel ermitteln:
DECLARE @i FLOAT
DECLARE @m FLOAT
SELECT
@i=0.06, @m=2
SELECT
(POWER(1+@i/@m,@m)-1)*100
-----------------------------------------------------
6.0899999999999954
(1 row(s) affected)
Die Multiplikation *100 ist nicht zwingend notwendig. Ich habe sie hier nur eingebaut, um einen direkten Vergleich mit Excel's EFFEKTIV() Funktion zu erleichtern. Im Grunde ist dies nicht anderes als die T-SQL Adaption dieser Excel Funktion. Vorsicht! An dieser Stelle ein ROUND() einzubauen, wäre nicht sehr klug. Gerade wenn man auf die Multiplikation mit 100 verzichten will:
DECLARE @i FLOAT
DECLARE @m FLOAT
SELECT
@i=0.06, @m=2
SELECT
ROUND((POWER(1+@i/@m,@m)-1),2)
-----------------------------------------------------
5.9999999999999998E-2
(1 row(s) affected)
Was nichts anderes ist als der ursprüngliche Eingangszinssatz.
Die bisherigen Berechnungen haben stets einen Future Value aus einem Present Value errechnet. Natürlich funktioniert dies auch in entgegengesetzter Richtung. Dann lauten die Fragestellung eher so:
"Wieviel muß ich heute investieren, um in Zukunft eine Summe x zu haben?"
Bezogen auf unser erstes Beispiel oben haben wir 1.060€ Euro nach einem Jahr. Wieviel muß ich also heute investieren, um diese Summe bei einem Zinssatz von 6,00% nach einem Jahr zu haben?
Formelmäßig könnte man das folgendermaßen notieren:
PV=FV/(1+i)^1
=> PV=FV*(1/1+i)^n
=> 1.060*(1/1+0,06)^1
=> 1.000
Hier ist die entsprechende T-SQL Formel:
DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@fv=1060, @i=0.06, @n=1
SELECT
ROUND(@fv*POWER(1+@i,-@n),2)
-----------------------------------------------------
1000.0
(1 row(s) affected)
Wie man hier bereits sieht, läßt sich diese Formel auch unverändert zur Berechnung anderer Laufzeiten verwenden.
DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@fv=1338.23, @i=0.06, @n=5
SELECT
ROUND(@fv*POWER(1+@i,-@n),2)
-----------------------------------------------------
1000.0
(1 row(s) affected)
Mathematisch ist es äquivalent, ob ich die n-te Wurzel ziehe oder ^1/n rechne.
Und selbstverständlich kann man auch den Present Value bei häufigerer Anzahl der Zinszahlungen pro Jahr ermitteln.
DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @m FLOAT
DECLARE @n FLOAT
SELECT
@fv=1343.92, @i=0.06, @m=2, @n=5
SELECT
ROUND(@fv*POWER(1+@i/@m,-@m*@n),2)
-----------------------------------------------------
1000.0
(1 row(s) affected)
So, jetzt können wir Present und Future Values hin und zurück berechnen
Zum Abschluß dieser kleinen Einführung wollen wir uns noch einer in der Praxis recht häufig auftretenden Fragestellung widmen. Der leichteren Nachvollziehbarkeit bleiben wir bei unserem Beispiel und den bereits bekannten Ergebnissen.
Angenommen, wir wollen wissen, nach welcher Zeit aus 1.000€ 1.343,92€ werden bei einer jährlichen Verzinsung von 6,00%.
Mathematisch lautet diese Formel:
n= (ln(FV)-ln(PV)/ln(1+i))
DECLARE @pv FLOAT
DECLARE @fv FLOAT
DECLARE @i FLOAT
SELECT
@pv=1000, @fv=1338.23, @i=0.06
SELECT
ROUND((LOG(@fv)-LOG(@pv))/LOG(1+@i),2)
-----------------------------------------------------
5.0
(1 row(s) affected)
Die wahrscheinlich noch interessantere Frage nach der Verzinsung eines Papieres, das ich heute zu 1.000 kaufe und welches in 5 Jahren zu 1.338,23 zurückgezahlt wird, stellen wir hier zurück. Sie ist nicht ganz so trivial zu lösen und wird im einem eigenen Beitrag betrachtet. Diese Frage fällt auch insofern hier aus dem Rahmen, als das die hier gezeigten Formeln ohne weiteres in setbasierten SELECTs eingebaut werden können, während die Frage nach der Vezinsung hingegen einen iterativen Trial- and-Error Prozeß beschreibt.
So, Ende dieser kurzen Einführung in das Thema dieser Kategorie. Mathematik ist nicht jedermanns Sache aber vielleicht hat der eine oder andere Leser ja doch mal Verwendung für die hier beschriebenen Verfahren. :-)
Werte in aufeinanderfolgenden Zeilen subtrahieren
Zunächst vielleicht erst einmal eine Erklärung, was hier überhaupt beschrieben werden soll. Mal angenommen, wir haben eine Tabelle, in der Kurse einer Aktie erfaßt werden. Der Einfachheithalber konzentrieren wir uns hier nur auf die entscheidenden Spalten Datum und Kurs und ignorieren alles weitere hier.
BCP Ausgabe mit Spaltenüberschriften
Schon mal darüber geärgert, daß es keine Möglichkeit gibt, die Spaltenüberschriften im BCP Utility mit auszugeben...?
