Media mensile con date mancanti
Trova il numero medio di richieste 311 di Evanston create al giorno per ogni mese dei dati.
Questa volta, non ignorare le date senza richieste.
Questo esercizio fa parte del corso
Analisi esplorativa dei dati in SQL
Istruzioni dell'esercizio
- Genera una serie di date da 2016-01-01 a 2018-06-30.
- Fai una JOIN della serie con una sottoquery per contare il numero di richieste create per giorno.
- Usa
date_trunc()per ottenere i mesi a partire dadate, che contiene tutte le date, NON daday. - Usa
coalesce()per sostituire i valori NULL dicountcon 0. Calcola la media di questo valore.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- 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;