Verwendung von DATE_TRUNC für Datenaggregation

Gemeinschaft Daten & Berichterstattung
1

Die Funktion date_trunc ist ein leistungsstarkes Werkzeug in SQL. Sie ermöglicht es, einen TIMESTAMP- oder INTERVAL-Wert basierend auf einem angegebenen Datumsbestandteil zu kürzen, was sie zu einer unschätzbaren Funktion macht, wenn Sie Daten basierend auf einem bestimmten Zeitraum aggregieren oder gruppieren möchten.

Syntax

Die Syntax für die Funktion date_trunc lautet wie folgt:

date_trunc('date_part', field)
  • date_part: Dies ist eine Zeichenkette, die den Teil des Datums oder Zeitstempels angibt, auf den gekürzt werden soll. Es kann einer der folgenden Werte sein:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: Dies ist der Zeitstempel oder das Intervall, das gekürzt werden soll.

Beispielhafte Verwendung in DE-Abfragen

Werfen wir einen Blick auf einige Beispielabfragen, die date_trunc verwenden:

Anzahl neuer Themen pro Monat

Schwierigkeitsgrad: Anfänger

Diese SQL-Abfrage wird verwendet, um die Anzahl der im Discourse-Datenbank erstellten Themen pro Monat zu zählen.

SELECT 
    date_trunc('month', created_at)::DATE AS month,
    count(id)
FROM topics
GROUP BY month
ORDER BY month DESC

In dieser Abfrage kürzt date_trunc('month', created_at)::DATE den Zeitstempel created_at auf den Monat und konvertiert ihn dann in ein Datum, um die Themen effektiv nach dem Monat ihrer Erstellung zu gruppieren.

Die Funktion count(id) zählt dann die Anzahl der in jedem Monat erstellten Themen. Die Ergebnisse werden absteigend nach Monat sortiert, sodass der aktuellste Monat zuerst erscheint.

Beispielhafte Ergebnisse:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
Detaillierte Erklärung mit Inline-Kommentaren 
-- Wähle den Monat, in dem das Thema erstellt wurde, und die Anzahl der Themen
SELECT 
    -- Kürze den Zeitstempel 'created_at' auf den Monat und konvertiere ihn in ein Datum
    -- Dies gruppiert die Themen nach dem Monat ihrer Erstellung
    date_trunc('month', created_at)::DATE AS month,
    -- Zähle die Anzahl der in jedem Monat erstellten Themen
    count(id)
-- Aus der Tabelle 'topics'
FROM topics
-- Gruppiere die Ergebnisse nach Monat
GROUP BY month
-- Sortiere die Ergebnisse absteigend nach Monat
-- Das bedeutet, der aktuellste Monat erscheint zuerst
ORDER BY month DESC

Kumulative Gesamtzahl der Benutzer

Schwierigkeitsgrad: Fortgeschritten

Diese Abfrage liefert einen wöchentlichen Bericht über Benutzeranmeldungen auf einem Discourse-Forum sowie eine laufende Gesamtzahl der Benutzer. Sie verwendet eine WITH-Klausel, um ein temporäres Ergebnisset (daily_signups) zu erstellen, und wählt dann aus diesem Ergebnisset aus.

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

WITH daily_signups AS(
SELECT
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    Count (id) as Signups
FROM users u
WHERE
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

SELECT
    Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
ORDER BY Date Asc

Hier ist eine Aufschlüsselung der Funktionsweise dieser Abfrage:

  • Die WITH-Klausel erstellt ein temporäres Ergebnisset namens daily_signups. Dieses Ergebnisset enthält die Anzahl der Benutzeranmeldungen pro Woche zwischen den durch die Parameter :start_date und :end_date angegebenen Start- und Enddaten.
  • Innerhalb des Ergebnissets daily_signups kürzt date_trunc('week', u.created_at)::date den Zeitstempel created_at auf die Woche und konvertiert ihn dann in ein Datum. Dies gruppiert die Benutzer effektiv nach der Woche, in der sie sich angemeldet haben.
  • Count(id) zählt dann die Anzahl der Benutzer, die sich in jeder Woche angemeldet haben.
  • In der Hauptanweisung SELECT berechnet SUM(Signups) OVER (ORDER BY Date) eine laufende Gesamtzahl der Benutzer. Die Klausel OVER (ORDER BY Date) legt fest, dass die Summe über die nach Datum sortierten Zeilen berechnet werden soll, sodass eine kumulative Summe der Anmeldungen bis zu jedem Datum erhalten wird.
  • Die Ergebnisse werden dann aufsteigend nach Datum sortiert.

Beispielhafte Ergebnisse:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
Detaillierte Erklärung mit Inline-Kommentaren 
-- Definiere Parameter für Start- und Enddatum
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Erstelle einen Common Table Expression (CTE), um die Anzahl der Benutzeranmeldungen pro Woche zu zählen
WITH daily_signups AS(
SELECT
    -- Kürze den Zeitstempel 'created_at' auf die Woche und formatiere ihn als Datumszeichenkette
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- Zähle die Anzahl der angemeldeten Benutzer
    Count (id) as Signups
FROM users u
WHERE
    -- Füge nur Benutzer hinzu, die sich zwischen Start- und Enddatum angemeldet haben
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- Wähle das Datum, die Anzahl der Anmeldungen und die laufende Gesamtzahl der Anmeldungen
SELECT
    Date, 
    Signups, 
    -- Berechne die laufende Gesamtzahl der Anmeldungen
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- Sortiere die Ergebnisse aufsteigend nach Datum
ORDER BY Date Asc

Anzahl gelöster und ungelöster Fragen pro Monat

Schwierigkeitsgrad: Fortgeschritten / Erfordert das Discourse Solved Plugin

Diese Abfrage liefert einen monatlichen Bericht über die Anzahl gelöster und ungelöster Fragen auf einem Discourse-Forum. Diese Abfrage geht davon aus, dass alle Themen auf einer Seite gelöst werden können.

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as total_questions
    FROM topics
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
solved_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as solved
    FROM user_actions
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    AND action_type = 15
    GROUP BY month
)

SELECT
    mq.month, 
    mq.total_questions, 
    COALESCE(sq.solved, 0) as solved,
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
LEFT JOIN solved_questions sq ON mq.month = sq.month
ORDER BY mq.month ASC

In dieser Abfrage zählt der CTE monthly_questions die Gesamtzahl der Fragen (Themen), die jeden Monat erstellt wurden. Der CTE solved_questions zählt die Anzahl der Fragen, die jeden Monat als gelöst markiert wurden, indem die Anzahl der id aus der Tabelle user_actions mit action type = 15 gezählt wird.

Die Hauptanweisung SELECT berechnet dann die Anzahl der ungelösten Fragen, indem sie die Anzahl der gelösten Fragen von der Gesamtzahl der Fragen subtrahiert. Die Ergebnisse werden aufsteigend nach Monat sortiert, sodass der früheste Monat zuerst erscheint.

Beispielhafte Ergebnisse:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
Detaillierte Erklärung mit Inline-Kommentaren 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Erstelle einen CTE, um die Gesamtzahl der Fragen (Themen) zu zählen, die jeden Monat erstellt wurden
WITH monthly_questions AS (
    SELECT
        -- Kürze den Zeitstempel 'created_at' auf den Monat
        date_trunc('month', created_at)::DATE AS month,
        -- Zähle die Anzahl der in jedem Monat erstellten Themen
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- Füge nur Themen hinzu, die zwischen Start- und Enddatum erstellt wurden
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- Erstelle einen CTE, um die Anzahl der Fragen zu zählen, die jeden Monat als gelöst markiert wurden
solved_questions AS (
    SELECT
        -- Kürze den Zeitstempel 'created_at' auf den Monat
        date_trunc('month', created_at)::DATE AS month,
        -- Zähle die Anzahl der gelösten Fragen in jedem Monat
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- Füge nur Aktionen hinzu, die zwischen Start- und Enddatum ausgeführt wurden
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- Berücksichtige nur Aktionen, bei denen der Aktionstyp 15 ist (was eine gelöste Frage anzeigt)
        AND action_type = 15
    GROUP BY month
)

-- Wähle den Monat, die Gesamtzahl der Fragen, die Anzahl der gelösten Fragen und die Anzahl der ungelösten Fragen
SELECT
    mq.month, 
    mq.total_questions, 
    -- Wenn in einem Monat keine gelösten Fragen vorhanden sind, zeige 0 an
    COALESCE(sq.solved, 0) as solved,
    -- Subtrahiere die Anzahl der gelösten Fragen von der Gesamtzahl der Fragen, um die Anzahl der ungelösten Fragen zu erhalten
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Verbinde die CTEs 'monthly_questions' und 'solved_questions' nach Monat
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Sortiere die Ergebnisse aufsteigend nach Monat
ORDER BY mq.month ASC

Statistiken zu Themenantworten

Schwierigkeitsgrad: Experte

Diese komplexe SQL-Abfrage liefert einen wöchentlichen Bericht über die Aktivität von Themen in einem Discourse-Forum. Sie unterteilt die Themendaten in mehrere wichtige Kennzahlen: die Anzahl der Themen mit mindestens einer Antwort, die Anzahl der Themen ohne Antwort, die maximale Anzahl von Tagen, die ein Thema unbeantwortet geblieben ist, und die durchschnittliche Zeit bis zur ersten Antwort.

WITH posts_list AS (
    SELECT 
        t.id topic_id,
        p.post_number,
        p.created_at,
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
    ORDER BY p.topic_id, p.post_number
),
atleast_1_response AS (
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count >= 2
    GROUP BY "week"
),
no_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
max_days_without_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
avg_time_first_response AS (
    SELECT 
        date_trunc('week', pl.created_at::date)::date AS "week",
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1
    GROUP BY "week" 
)

SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
ORDER BY "week" DESC

Hier ist eine Zusammenfassung der Funktionsweise dieser Abfrage:

  • Der Common Table Expression (CTE) posts_list wählt eine Liste aller regulären Beiträge aus den Tabellen posts und topics aus, sortiert nach topic_id und post_number. Er weist auch jeder Antwort innerhalb ihres Themas eine Zeilennummer (post_order) zu.
  • Der CTE atleast_1_response zählt die Anzahl der regulären Themen mit mindestens einer Antwort (d. h. posts_count ist größer oder gleich 2) für jede Woche.
  • Der CTE no_response zählt die Anzahl der regulären Themen ohne Antwort (d. h. posts_count ist gleich 1) für jede Woche.
  • Der CTE max_days_without_response berechnet die maximale Anzahl von Tagen, die ein Thema ohne Antwort unbeantwortet geblieben ist, für jede Woche.
  • Der CTE avg_time_first_response berechnet die durchschnittliche Zeit bis zur ersten Antwort für jedes Thema in Stunden, für jede Woche.
  • Die Hauptanweisung SELECT verbindet dann diese CTEs nach Woche und wählt die relevanten Spalten aus. Die Ergebnisse werden absteigend nach Woche sortiert.
week topics without response max days without response topics with atleast one response avg time first response (h)
2023-09-04 15 2 47 2.6778684519444444
2023-08-28 30 9 138 8.7899938238888889
2023-08-21 22 16 130 9.3280889688888889
Detaillierte Erklärung mit Inline-Kommentaren 
-- Erstelle eine temporäre Tabelle (CTE) aller regulären Beiträge, sortiert nach topic_id und post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- Topic ID
        p.post_number,  -- Post number
        p.created_at,   -- Post creation date
        -- Weise jeder Antwort innerhalb ihres Themas eine Zeilennummer zu
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- Verbinde mit der Tabelle topics, berücksichtige nur reguläre Themen, die nicht gelöscht wurden
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL  -- Gelöschte Beiträge ausschließen
        AND t.deleted_at ISNULL  -- Gelöschte Themen ausschließen
        AND t.archetype = 'regular'  -- Nur reguläre Themen berücksichtigen
    ORDER BY p.topic_id, p.post_number
),
-- Erstelle einen CTE, um die Anzahl der regulären Themen mit mindestens einer Antwort pro Woche zu zählen
atleast_1_response AS (
    SELECT 
        -- Kürze den Zeitstempel created_at auf die Woche
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Nur reguläre Themen berücksichtigen
        AND t.deleted_at ISNULL  -- Gelöschte Themen ausschließen
        AND t.posts_count >= 2  -- Nur Themen mit mindestens einer Antwort berücksichtigen
    GROUP BY "week"
),
-- Erstelle einen CTE, um die Anzahl der regulären Themen ohne Antwort pro Woche zu zählen
no_response AS(
    SELECT 
        -- Kürze den Zeitstempel created_at auf die Woche
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Nur reguläre Themen berücksichtigen
        AND t.deleted_at ISNULL  -- Gelöschte Themen ausschließen
        AND t.posts_count = 1  -- Nur Themen ohne Antwort berücksichtigen
    GROUP BY "week"
),
-- Erstelle einen CTE, um die maximale Anzahl von Tagen zu berechnen, die ein Thema ohne Antwort unbeantwortet geblieben ist, pro Woche
max_days_without_response AS(
    SELECT 
        -- Kürze den Zeitstempel created_at auf die Woche
        date_trunc('week', t.created_at::date)::date AS "week",
        -- Berechne die Anzahl der Tage vom Erstellungsdatum des Themas bis zum aktuellen Datum
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Nur reguläre Themen berücksichtigen
        AND t.deleted_at ISNULL  -- Gelöschte Themen ausschließen
        AND t.posts_count = 1  -- Nur Themen ohne Antwort berücksichtigen
    GROUP BY "week"
),
-- Erstelle einen CTE, um die durchschnittliche Zeit bis zur ersten Antwort für jedes Thema in Stunden zu berechnen, pro Woche
avg_time_first_response AS (
    SELECT 
        -- Kürze den Zeitstempel created_at auf die Woche
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- Berechne die durchschnittliche Zeit bis zur ersten Antwort in Stunden
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- Verbinde mit dem CTE posts_list, berücksichtige nur den zweiten Beitrag in jedem Thema
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- Berücksichtige nur den ersten Beitrag in jedem Thema
    GROUP BY "week" 
)

-- Wähle die Woche, die Anzahl der Themen ohne Antwort, die maximale Anzahl von Tagen ohne Antwort, die Anzahl der Themen mit mindestens einer Antwort und die durchschnittliche Zeit bis zur ersten Antwort
SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
-- Verbinde die CTEs nach Woche
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
-- Sortiere absteigend nach Woche
ORDER BY "week" DESC

Dies sind nur einige Beispiele dafür, wie Sie date_trunc in Ihren Data Explorer-Abfragen verwenden können. Fühlen Sie sich frei, eine dieser Abfragen auf Ihrer Website zu verwenden, und wenn Sie Fragen haben, stellen Sie diese bitte unten. :slight_smile:

5 „Gefällt mir“
Retrospectively pulling number of users each calendar month