Miesięczna średnia z uwzględnieniem brakujących dat
Znajdź średnią liczbę zgłoszeń do systemu Evanston 311 tworzonych dziennie dla każdego miesiąca w zbiorze danych.
Tym razem nie pomijaj dat, dla których nie ma żadnych zgłoszeń.
To ćwiczenie jest częścią kursu
Eksploracyjna analiza danych w SQL
Instrukcje do ćwiczenia
- Wygeneruj serię dat od 2016-01-01 do 2018-06-30.
- Połącz tę serię z podzapytaniem zliczającym liczbę zgłoszeń utworzonych każdego dnia.
- Użyj
date_trunc(), aby wyodrębnić miesiące z kolumnydate, która zawiera wszystkie daty — a nie z kolumnyday. - Użyj
coalesce(), aby zastąpić wartości NULL w kolumniecountzerami. Następnie oblicz średnią z tej wartości.
Interaktywne ćwiczenie praktyczne
Spróbuj tego ćwiczenia, uzupełniając ten przykładowy kod.
-- 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;