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 keine Tage ohne Anfragen.
Diese Übung ist Teil des Kurses
Explorative Datenanalyse in SQL
Anleitung zur Übung
- 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 ausdatezu erhalten, das alle Datumsangaben enthält, NOTday. - Verwende
coalesce(), um NULLcount-Werte durch 0 zu ersetzen. Berechne den Mittelwert dieses Wertes.
Interaktive Übung zum Anfassen
Probieren Sie diese Übung aus, indem Sie diesen Beispielcode ausführen.
-- 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;