Monatlicher Durchschnitt mit fehlenden Daten

Finde die durchschnittliche Anzahl der Evanston-311 Anfragen, die pro Tag für jeden Monat der Daten erstellt werden.

Ignoriere dieses Mal nicht die Tage ohne Anfragen.

Diese Übung ist Teil des Kurses

<Kurs>Explorative Datenanalyse in SQL</Kurs>

Kurs ansehen

Übungsanweisungen

Erstelle eine Reihe von Datumsangaben vom 2016-01-01 bis 2018-06-30.
Verbinde die Reihe mit einer Unterabfrage, um die Anzahl der pro Tag erstellten Anfragen zu zählen.
Verwende date_trunc(), um die Monate aus date zu erhalten, das alle Datumsangaben enthält, NICHT day.
Verwende coalesce(), um NULL count-Werte durch 0 zu ersetzen. Berechne den Mittelwert dieses Wertes.

Interaktive praktische Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

-- generate series with all days from 2016-01-01 to 2018-06-30
WITH all_days AS 
     (SELECT generate_series(___,
                             ___,
                             ___::___) AS date),
     -- Subquery to compute daily counts
     daily_count AS 
     (SELECT date_trunc('day', date_created) AS day,
             count(*) AS count
        FROM evanston311
       GROUP BY day)
-- Aggregate daily counts by month using date_trunc
SELECT ___ AS month,
       -- Use coalesce to replace NULL count values with 0
       avg(coalesce(___, ___)) AS average
  FROM all_days
       LEFT JOIN daily_count
       -- Joining condition
       ON all_days.___=daily_count.___
 GROUP BY month
 ORDER BY month;

Code bearbeiten und ausführen