code it

Martins Tech Blog

SQL Server 2012 und neue analytische Funktionen

Dieser Post zum Thema Neuerungen im SQL Server 2012 widmet sich einigen neu hinzugekommenen analytischen Funktionen. Damit sind Auswertungen der Datenbestände möglich. Und ganz besonders zum Jahresende sind auch Performanceberichte und Vorjahresvergleiche immer von besonderer Bedeutung. Aus diesem Grund möchte ich mich heute etwas näher damit beschäftigen, wie man aktuelle Zahlen - seien es nun Verkaufszahlen, Umsatzzahlen, Gewinne, Arbeitslosenanzahl oder was auch immer mit anderen Zeiträumen vergleicht.

Der SQL Server 2012 bietet hier ein paar wesentliche Verbesserungen, dass sich der Datenbankentwickler nicht mehr zwingend die Finger brechen muss um mit Verrenkungen an diese Werte zu kommen. Mit Hilfe zweier neuer Funktionen und einer neuen Klausel sind Vorzeitraum- bzw. Folgezeitraumbetrachtungen oder Auswertungen mit einem gleitenden Fenster kein Problem mehr. Schauen wir uns das gemeinsam an einigen Beispielen an:

Mehr...

SQL Server 2012 und neue Datumsfunktionen

Über KonvertierungsfunktionenFormatierungsmöglichkeiten und Logikfunktionen in SQL Server 2012 habe ich schon berichtet. Heute soll es um neue Datumsfunktionen gehen.

*FROMPARTS-Funktionen

Eine Möglichkeit Datums- und Zeitwerte zu erstellen ist, die Werte aus Textwerten zu parsen bzw. zu konvertieren  (z.B. mit PARSE oder mit CONVERT). Wenn der Wert nur als Text zur Verfügung steht, dann ist das durchaus ein akzeptabler Weg. In der Vergangenheit wurde dieser Weg jedoch auch häufig gewählt, wenn man Datumsteile vorliegen hatte. Oft wurde dann ein Text im passenden Format per Stringverkettung erstellt und dieser dann mit Hilfe von CONVERT in einen Datumswert konvertiert. Dieser Weg ist aber recht umständlich und fehleranfällig, da die Culture beim Format eine wesentliche Rolle spielt.

Neu im SQL Server 2012 ist sind nun Funktionen wie DATEFROMPARTS oder DATETIMEFROMPARTS, in der man Datumsfraktale angeben kann und hier typsicherer Datumswerte erzeugen kann.

DECLARE @year int, @month int, @day int;
SET @year = 2012;
SET @month = 1;
SET @day = 12;

-- alt
SELECT CONVERT(datetime, LTRIM(@day) + '.' + LTRIM(@month) + '.' + LTRIM(@year), 104)
-- neu
SELECT DATETIMEFROMPARTS(@year, @month, @day, 0, 0, 0, 0)

Genau wie DATETIMEFROMPARTS gibt es Funktionen für so ziemlich alle Datums- und Zeitdatentypen des SQL Servers.

EOMONTH 

Welches Datum hat der Monatsletzte? Besonders wenn man Zeitraumberechnungen oder Ultimoberechnungen durchführen möchte, spielt diese Frage eine relevante Rolle. Wie löst man dieses Problem bisher?

DECLARE @currentdate datetime = GETDATE();
SELECT DATEADD(dd,-DATEPART(dd,DATEADD(MM,1,@currentdate)),DATEADD(MM,1,@currentdate))

Man addiert einen Monat zum aktuellen Datum hinzu und zieht dann die Anzahl von Tagen ab, die im aktuellen Monat schon vergangen sind.

Damit man nicht mehr solche Verrenkungen machen muss, gibt es nun die Funktion EOMONTH, die den Monatsletzten des Monats ermittelt.

SELECT EOMONTH(GETDATE())

Wer mag, kann im zweiten Parameter noch einen zusätzlichen Offset hinzufügen, um so zum Beispiel den Monatsletzten des Folgemonats zu ermitteln.

SQL Server 2012 und neue Konvertierungsfuktionen

Mit SQL Server 2012 kommen auch neue Konvertierungsmöglichkeiten hinzu. Bisher bekannt sind ja schon die Möglichkeiten CAST und CONVERT.

TRY_CONVERT

CONVERT führt zu einem Fehler, wenn die Konvertierung nicht funktioniert. Kleines Beispiel gefällig?

SELECT ISNUMERIC('1e1')
SELECT CONVERT(int, '1e1')

Obwohl ISNUMERIC den Wert 1 zurückliefert, schlägt der Aufruf von CONVERT fehl. Eine Konvertierung in den float-Datentyp hingegen wäre erfolgreich. Hier hilft TRY_CONVERT.

SELECT TRY_CONVERT(int, '100')
SELECT TRY_CONVERT(int, '1e1')

Diese Funktion gibt NULL zurück, wenn die Konvertierung nicht durchgeführt werden kann, oder im Erfolgsfall eben den Wert.

PARSE und TRY_PARSE

Um Datums- oder Zahlenwerte aus einem Text in die jeweiligen Datentypen zu konvertieren gibt es nun die Funktionen PARSE und TRY_PARSE. Unterschied zwischen beiden Funktionen ist - ähnlich wie bei CONVERT und TRY_CONVERT auch, dass PARSE eine Exception wirft, wenn die Konvertierung nicht durchgeführt werden kann, TRY_PARSE hingegen NULL.

SELECT TRY_PARSE('01.02.2012' AS datetime USING 'de-DE')
SELECT PARSE('01.02.2012' AS datetime USING 'en-US')
SELECT PARSE('1234567' AS datetime USING 'en-US')
SELECT TRY_PARSE('1234567' AS datetime USING 'en-US')

Wird keine Culture angegeben, so verwendet PARSE die Culture, die mit Hilfe von SET LANGUAGE gesetzt wurde. Sowohl PARSE als auch TRY_PARSE basieren auf der CLR, was zur Folge hat, dass alle Cultures angegeben werden können, die die CLR kennt - nicht nur jene, die dem SQL Server bekannt sind.

SQL Server 2012 und neue Logikfunktionen

Der SQL-Server 2012 bringt unter anderem auch ein paar neue Funktionen mit sich, die im SQL verwendet werden können. Über die Format-Funktion habe ich ja bereits berichtet. Heute soll es um neue Logik-Funktionen gehen.

IIF (Inline IF)

Vor vielen Jahren als ich von Access zu SQL-Server gewechselt bin war es eine ziemliche Umstellung, dass ich die von dort bekannte IIF-Funktion nicht mehr hatte und alle Abfragen angepasst werden mussten. Klar konnte man mit ähnlichen Konstrukten das gleiche erreichen.

SELECT LastName, FirstName, 
CASE WHEN Gender = 1 THEN 'männlich' ELSE 'weiblich' END Gender 
FROM Person

Solche einfachen CASE-WHEN-Konstrukte kann man jetzt (etwas) kürzer schreiben, indem man die IIF-Funktion verwendet.

SELECT LastName, FirstName, 
IIF(Gender = 1, 'männlich', 'weiblich') Gender 
FROM Person

Der Unterschied in der Syntax ist marginal, kann aber dazu führen, dass die Statements einfacher zu lesen sind - besonders dann wenn man die Argumente schachtelt kommt man in meinen Augen bei der Klammern-Schreibweise weniger schnell durcheinander als beim Suchen der Wörter CASE, WHEN, THEN und des jeweils passenden END. Bei der Ausführung wird IIF zu CASE WHEN übersetzt - es handelt sich hier also um eine Art von syntactic sugar. Daraus abgeleitet erlaubt IIF ebenso wie CASE WHEN eine Verschachtelungstiefe von 10.

CHOOSE

Ebenso wie IIF sollte auch CHOOSE allen Access-Jüngern bekannt vorkommen. CHOOSE ermöglicht es, indexbasiert aus Werten auszuwählen. Dazu zunächst ein Beispiel in der bisherigen Syntax:

SELECT LastName, FirstName, 
CASE MaritalStatus 
WHEN 1 THEN 'ledig'
WHEN 2 THEN 'verheiratet'
WHEN 3 THEN 'geschieden'
WHEN 4 THEN 'verwitwet'
WHEN 5 THEN 'getrennt lebend'
WHEN 6 THEN 'verpartnert'
ELSE NULL
END MaritalStatus 
FROM Person

Dieses Konstrukt kann mit CHOOSE anders geschrieben werden:

SELECT LastName, FirstName, 
CHOOSE(MaritalStatus, 'ledig', 'verheiratet', 'geschieden', 'verwitwet', 'getrennt lebend', 'verpartnert') MaritalStatus 
FROM Person

Wichtige Anmerkung für alle C#-Entwickler... der Index ist 1-basiert.

Split-Funktion für SQL-Server

Im SQL-Server sucht man vergeblich nach einer eingebauten Funktion, die es ermöglicht, Textdaten zu splitten - analog z.B. der string.Split-Funktion in C#. Das mag unter anderem daran liegen, dass es keinen Array-Datentyp gibt. Als Problemlösung dient eine UDF, die eine Tabelle mit den Split-Werten zurückgibt. Für die Implementierung gibt es sehr viele Lösungen im Web. Das folgende Beispiel soll eine mögliche Implementierung aufzeigen, die ich als simpel und funktional genug einschätze, um das Problem zu lösen.

CREATE FUNCTION dbo.fn_Split (@String nvarchar(max), @Delimiter nchar(1))
RETURNS @Results Table (Items nvarchar(max))
AS
BEGIN
DECLARE @Index int
DECLARE @Slice nvarchar(max)

SET @Index = 1
IF @String IS NULL RETURN

    WHILE @Index != 0
    BEGIN
        SELECT @Index = CHARINDEX(@Delimiter, @String)
        IF @Index != 0
            SELECT @Slice = LEFT(@String, @Index - 1)
        ELSE
            SELECT @Slice = @String

        INSERT INTO @Results(Items) VALUES (LTRIM(RTRIM(@Slice)))
        SELECT @String = RIGHT(@String, LEN(@String) - @Index)
        
        IF Len(@String) = 0 BREAK
    END
RETURN
END
GO

Der Aufruf ist dann sehr simpel: