Kurs
Datenbearbeitung in SQL
4 Std.
335K
Einige der nützlichsten Funktionen in Postgres-Implementierungen von SQL (wie Amazon Redshift) sind DATE_DIFF und DATE_TRUNC:
DATE_DIFFliefert die verstrichene Zeit zwischen zwei Datumswerten. Zum Beispiel gibt der folgende Code die Anzahl der Tage zwischendate1unddate2zurück:
DATE_DIFF('day', date1, date2)
DATE_DIFF ist ideal, um etwa die Anzahl der Tage von Anmeldung bis Kündigung oder die Stunden von Login bis Logout zu berechnen.
DATE_TRUNCschneidet ein Datum auf den nächsten Tag, die Woche, den Monat oder das Jahr zu. Zum Beispiel gibt der folgende Code den Montag der betreffenden Woche für den Zeitstempelmy_timestampzurück:
DATE_TRUNC('week', my_timestamp)
DATE_TRUNC eignet sich hervorragend zum Aggregieren von Daten. So lässt sich damit etwa die Zahl der Monthly Active Users (MAU) ermitteln, indem auf den Monatsanfang gekürzt wird.
Aber nicht jede SQL-Implementierung hat diese praktischen Funktionen. Für unseren Learn SQL-from-Scratch-Code nutzen wir SQLite, eine schlanke SQL-Implementierung, die auf einer einzelnen Docker-Instanz laufen kann. SQLite ist großartig für Website-Backends und kleine Projekte, aber es fehlen meine zwei Lieblingsfunktionen. Zum Glück gibt es Workarounds.
Um DATE_DIFF zu emulieren, können wir eine wenig bekannte Funktion namens juliandate verwenden. Laut Wikipedia ist „die Julianische Tageszahl (JDN) die ganze Zahl, die einem vollen Sonnentag in der Zählung der Julianischen Tage zugeordnet ist und um 12:00 Uhr Weltzeit beginnt, wobei der Julianischen Tageszahl 0 der Tag zugeordnet ist, der am Montag, dem 1. Januar 4713 v. Chr., um 12:00 Uhr beginnt“. Indem wir ein Datum in eine Fließkommazahl umwandeln, können wir per Subtraktion die Differenz zwischen zwei Zeitstempeln berechnen.
Wir können das Ergebnis sogar in Stunden umrechnen, indem wir mit 24 multiplizieren, oder in Minuten mit 24 * 60.
Einen Teil der Funktionalität von DATE_TRUNC können wir mit strftime nachbilden. Diese Funktion konvertiert einen Zeitstempel in eine Zeichenkette mit einem gewünschten Format.
%dTag des Monats: 00 %fDezimalstellen der Sekunde: SS.SSS %HStunde: 00-24 %jTag des Jahres: 001-366 %JJulianische Tageszahl %mMonat: 01-12 %MMinute: 00-59 %sSekunden seit 1970-01-01 %SSekunden: 00-59 %wWochentag 0-6, Sonntag==0 %WWoche des Jahres: 00-53 %JJahr: 0000-9999
Normalerweise nutzt man das, um zwischen Zeitstempelformaten wie YYYY-MM-DD und MM-DD-YYYY zu wechseln:
strftime('%M-%D-%Y', mydate)
Wir können das Format aber auch so wählen, dass es auf die gewünschte Granularität kürzt. Wenn wir zum Beispiel auf den Monat kürzen möchten:
strftime('%M/%Y', mydate)
Oder wir kürzen auf die Woche, indem wir Folgendes verwenden:
strftime('%Y-%w', mydate)
Mit diesen zwei einfachen Tricks kannst du mit SQLite viele der gleichen Analysen durchführen wie mit Amazon Redshift!
Wenn du mehr über die Grundlagen von SQL lernen möchtest, besuche DataCamps Kurs Intro to SQL for Data Science und wirf einen Blick auf unser SQL Tutorial for Beginners.
Themen