Mode Blog (Deutsch)

Zeitstempel sind aus einem sehr einfachen Grund für die Geschäftsanalyse von entscheidender Bedeutung: Sie sagen Ihnen, wann Dinge passieren. Stellen Sie sich vor, Sie versuchen, Trends in Ihren Daten wie monatlichen Webverkehr, vierteljährliche Einnahmen oder tägliches Auftragsvolumen herauszufinden, ohne zu wissen, wann Ereignisse aufgetreten sind. Es wäre ein Albtraum.

PostgreSQL bietet eine Vielzahl von Datumsfunktionen zum Bearbeiten von Zeitstempeln. Um das Nützliche vom Obskuren zu trennen, teilen wir die Anleitungen für die am häufigsten verwendeten Postgres-Datumsfunktionen und Geschäftsszenarien, in denen sie nützlich sind.,

Die am häufigsten verwendeten Postgres-Datumsfunktionen und Geschäftsszenarien, in denen sie sich als nützlich erweisen:

• Abrunden von Zeitstempeln mit DATE_TRUNC
• Finden von Ereignissen relativ zur aktuellen Zeit mit NOW() und CURRENT_DATE
• Isolieren von Tages-und Wochentag mit EXTRACT
• Berechnen der mit AGE verstrichenen Zeit

Wir haben die Daten für jedes Beispiel verfügbar gemacht im Modus Öffentliches Lager. Probieren Sie jede Datumsfunktion im Modus aus, während Sie sich durch diese Beispiele arbeiten. Melden Sie sich für ein Konto in Mode Studio und öffnen Sie einen neuen Bericht zu beginnen.,

Zeitstempel mit DATE_TRUNC abrunden

Die Funktion DATE_TRUNC rundet einen Zeitstempelwert auf ein bestimmtes Intervall, in dem Sie Ereignisse zählen können. Sie können einen Zeitstempel auf folgende Zeiteinheiten abrunden:

• Mikrosekunde
• Millisekunde
• Sekunde
• Minute
• Stunde
• Tag
• Woche
• Monat
• Quartal
• Jahr
• Jahrzehnt
• Jahrhundert
• millenium

Die DATE_TRUNC Syntax sieht folgendermaßen aus: DATE_TRUNC('interval',timestamp).,

Zum Beispiel würde SELECT DATE_TRUNC('day','2015-04-12 14:44:18') ein Ergebnis von 2015-04-12 00:00:00zurückgeben.

Für eine detailliertere Erklärung von DATE_TRUNC (und eine druckbare Referenz, die Sie an Ihrem Schreibtisch aufbewahren können!), check out this post.

Wie hat sich der Webverkehr im Laufe der Zeit verändert?

Versuchen Sie DATE_TRUNC selbst, indem Sie die Tabelle modeanalytics abfragen.web_events, das Beispieldatensätze von Website-Besuchen enthält, einschließlich einer Spalte occurred_at. Sie können den Monat des Besuchs mit DATE_TRUNCisolieren.,

Um jeden Monat eine Anzahl von Webbesuchen nach Kanälen zurückzugeben, fügen Sie die Spalte channel und eineCOUNT zur AnweisungSELECT hinzu und gruppieren Sie sie dann nachmonth undchannel. (Da Monat und Kanal die ersten beiden Werte in Ihrer SELECT – Anweisung sind, können Sie GROUP BY 1,2 wie folgt verwenden:

Schließlich verwenden Sie ORDER BY 1,2, um Ihre Ergebnisse chronologisch (nach Monat) und alphabetisch (nach Kanal) zu organisieren.,

Im Modus können Sie ein Liniendiagramm erstellen, um die Abfrageergebnisse zu visualisieren.

Mode Analytics

Finden von Ereignissen relativ zur aktuellen Zeit mit NOW () und CURRENT_DATE

Die NOW() date Funktion gibt den aktuellen Zeitstempel in UTC zurück (wenn die Zeitzone nicht angegeben ist). Sie können Intervalle von NOW() subtrahieren, um Ereignisse abzurufen, die innerhalb der letzten Stunde, des letzten Tages, der letzten Woche usw. aufgetreten sind.

Das Ausführen von SELECT NOW() um 9:00 Uhr UTC am Oktober 11th, 2016 würde zu 2016-10-11 09:00:00führen.,

Die Funktion CURRENT_DATE gibt nur das aktuelle Datum und nicht den gesamten Zeitstempel zurück. Wenn Sie SELECT CURRENT_DATE am 11.Oktober 2016 um 9:00 Uhr UTC ausführen, wird 2016-10-11zurückgegeben.

Was ist mit Bestellungen in den letzten 12 Stunden?

Die Tabelle demo.bestellungen enthalten Beispieldatensätze aller Bestellungen, einschließlich eineroccurred_at Zeitstempelspalte in UTC.,

Um Bestellungen der letzten 12 Stunden zu finden, verwenden Sie eine WHERE – Klausel, um nur Bestellungen zurückzugeben, die nach oder genau unter (>=) dem aktuellen Zeitstempel (NOW()) abzüglich eines Intervalls von 12 Stunden platziert wurden.,

SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'

Mode Analytics

Zusätzlich zu hour können Sie eines der folgenden Intervalle verwenden:

• Mikrosekunden
• Millisekunden
• Sekunde
• Minute
• Stunde
• Tag
• woche
• Monat
• Jahr
• Jahrzehnt
• Jahrhundert
• millennium

Sie können auch verschiedene Intervalle in demselben Ausdruck wie folgt kombinieren:
interval '4 hours 3 minutes'

Welche Bestellungen wurden gestern aufgegeben?,

Sie können dieselbe Tabelle verwenden, um die Bestellungen von gestern zu finden, indem Sie die Funktionen DATE_TRUNC und CURRENT_DATE kombinieren.

Beginnen Sie mit einer DATE_TRUNC – Funktion, um Ihre occurred_at – Werte nach Tag zu runden (da wir wissen möchten, ob gestern etwas passiert ist). Verwenden Sie dann eineWHERE Klausel, um nur Werte zurückzugeben, bei denen die occurred_at Tag gleich dem aktuellen Datum ist (mit der CURRENT_DATE Funktion) minus einem Intervall von einem Tag.,

SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'

Mode Analytics

Isolieren von Tages-und Wochentag mit EXTRACT

Mit der EXTRACT date Funktion können Sie Unterfelder wie Jahr oder Stunde von Zeitstempeln isolieren.

Hier ist die syntax: EXTRACT(subfield FROM timestamp). Das Ausführen von EXTRACT(month FROM '2015-02-12') würde ein Ergebnis von 2.

Beachten Sie, dass, während sich das folgende Beispiel auf das Unterfeld Stunde (Stunde des Tages) konzentriert, Ihnen viele andere Unterfelder zur Verfügung stehen, die von millennium bis microsecondreichen., Die umfassende Liste der verfügbaren Unterfelder finden Sie hier.

Wie viele Bestellungen jeder Stunde des Tages?

Ein Unternehmen läuft ein fulfillment-center wollen, könnte der Mitarbeiter mehr Mitarbeiter, wenn der Großteil der Aufträge kommt in. Um herauszufinden, wann Bestellungen den ganzen Tag aufgegeben werden, können Sie die Funktion EXTRACT und das Unterfeld hour verwenden, um die Tagesstunde (von 0 bis 23) zu isolieren, in der eine Bestellung getätigt wurde.,

SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders

Verwenden Sie die COUNT – Funktion, um Bestellungen zu tally, und dann GROUP BY Stunde. (Da hour der erste Wert in Ihrer SELECT Anweisung ist, können Sie GROUP BY 1).

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1

Um Ihre Ergebnisse nacheinander zu organisieren, verwenden Sie ORDER BY 1.

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1

Und hier sind die Ergebnisse! Sieht so aus, als ob es helfen könnte, früh morgens und um die Mittagszeit zusätzliche Arbeiter auf der Uhr zu haben.,

Mode Analytics

Was ist das durchschnittliche Auftragsvolumen an Wochentagen?

Um das durchschnittliche Auftragsvolumen nach Wochentag zu bestimmen, verwenden Sie EXTRACT und das Unterfeld dow, um den Wochentag (von 0-6, wobei 0 Sonntag ist) zu isolieren, an dem eine Bestellung aufgetreten ist.

SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders

Runden Sie als nächstes die Bestellzeitstempel nach Tag mit DATE_TRUNCab., Wenn Sie eine COUNT von Aufträgen verwenden, die nach dow und day gruppiert sind, wird die Anzahl der Bestellungen jeden Tag zusammen mit dem entsprechenden Wochentag zurückgegeben.

SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2

Um das durchschnittliche Auftragsvolumen an Wochentagen zu ermitteln, verwenden Sie die vorherige Abfrage als Unterabfrage (Alias als a). Nehmen Sie den Durchschnitt der Bestellungen (mit der Funktion AVG()) und verwenden Sie dann eine Klausel WHERE, um samstags und sonntags herauszufiltern.

Große Zahlendiagramme eignen sich hervorragend zum Anzeigen aggregierter Metriken., Um das Auftragsvolumen im Auge zu behalten, sammeln Sie Metriken wie diese in einem Dashboard.

Mode Analytics

Berechnen der mit dem ALTER verstrichenen Zeit

DieAGE date Funktion berechnet, wie lange ein Ereignis aufgetreten ist. Die Syntax ist ziemlich einfach: Wenden Sie AGE() auf einen einzelnen Zeitstempel an, und Ihre Abfrage gibt die Zeit zurück, seit dieses Ereignis stattgefunden hat. Das Ausführen von SELECT AGE( '2010-01-01' ) am 1.Januar 2011 würde ein Ergebnis von 1 years 0 months 0 days.,

AGE() kann auch bestimmen, wie viel Zeit zwischen zwei Ereignissen vergangen ist. Anstatt einen einzelnen Zeitstempel in die Klammern einzufügen, fügen Sie beide Zeitstempel ein (beginnend mit dem letzten Zeitstempel) und trennen Sie sie durch ein Komma. Das Ausführen von SELECT AGE( '2012-12-01',‚2010-01-01‘) würde 2 years 11 months 0 days.

Beachten Sie, dass diese Anwendung der Funktion AGE der Subtraktion der Zeitstempel entspricht: SELECT '2012-12-01' - '2010-01-01'.

Wie alt ist ein Kundenkonto?,

Angenommen, Ihr Verkaufsteam möchte Grüße personalisieren, basierend darauf, wie lange der Kunde Ihr Produkt verwendet hat. Mit der Funktion AGE können Sie herausfinden, wie viel Zeit seit der Kontoerstellung vergangen ist.

Die Tabelle modeanalytics.customer_accounts enthält Datensätze der Probe, die Kunden-Konten. Wählen Sie die Spalte mit den Kontonamen aus (name) und wenden Sie die Funktion AGE() auf die Spalte mit den Zeitstempeln an, die beim Erstellen jedes Kontos angezeigt wurden (created).,

SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

Mode Analytics

Wie lange dauert es durchschnittlich, bis Benutzer ihr Profil jeden Monat vervollständigen?

Die Tabelle modeanalytics.profilecreationevents enthält Beispieldaten von Benutzern, die ein Profil erstellt haben, einschließlich Start-und Endzeitstempeln.

Um die durchschnittliche Zeit für das Ausfüllen eines Profils pro Monat zu ermitteln, ermitteln Sie zunächst die Zeit, die jeder Benutzer für das Ausfüllen eines Profils benötigt hat, sowie den Monat, in dem der Profilerstellungsprozess gestartet wurde. Runden Sie zuerst den Zeitstempel started_at mit der Funktion DATE_TRUNC nach Monat ab., Suchen Sie als Nächstes die verstrichene Zeit von started_at bis ended_at für jedes Profil mit der Funktion AGE.

Ermitteln Sie den Durchschnitt für jeden Monat, indem Sie die AVG – Funktion auf den verstrichenen Zeitwert (Ihre AGE – Anweisung) anwenden und nach Monat gruppieren.

Um Werte in einer konsistenten Diagrammeinheit zurückzugeben, wenden Sie die Funktion EXTRACT und das Unterfeld Epoche auf Ihre Werte an, um Ergebnisse als Anzahl von Sekunden zurückzugeben.

Mode Analytics

Empfohlene Artikel

• So beherrschen Sie Anti-Joins und wenden Sie sie auf Geschäftsprobleme an
• Denken in SQL vs Denken in Python
• DATE_TRUNC: Eine SQL-Zeitstempelfunktion, auf die Sie zählen können