Moyenne mensuelle avec des dates manquantes

Trouvez le nombre moyen de demandes 311 d'Evanston créées par jour pour chaque mois des données.

Cette fois, ne tenez pas compte des dates sans demandes.

Cette activité fait partie du cours

Analyse exploratoire des données en SQL

Voir le cours

Instructions de l’exercice

Générez une série de dates du 2016-01-01 au 2018-06-30.
Faites une jointure entre la série et une sous-requête pour compter le nombre de demandes créées par jour.
Utilisez date_trunc() pour obtenir les mois à partir de date, qui contient toutes les dates, et NON à partir de day.
Utilisez coalesce() pour remplacer les valeurs count NULL par 0. Calculez la moyenne de cette valeur.

Exercice interactif pratique

Essayez cet exercice en complétant ce code d’exemple.

-- 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;

Modifier et exécuter le code