Ежемесячное среднее с учётом пропущенных дат
Найдите среднее количество обращений в службу 311 города Эванстон, созданных в день, для каждого месяца из набора данных.
На этот раз не игнорируйте даты, в которые обращений не поступало.
Это упражнение является частью курса
Разведочный анализ данных в SQL
Инструкции к упражнению
- Сформируйте серию дат с 2016-01-01 по 2018-06-30.
- Соедините серию с подзапросом, который подсчитывает количество обращений, созданных за каждый день.
- Используйте
date_trunc(), чтобы извлечь месяцы изdate— столбца, содержащего все даты, а не изday. - Используйте
coalesce(), чтобы заменить значения NULL вcountна 0. Вычислите среднее по этому значению.
Интерактивное практическое упражнение
Попробуйте выполнить это упражнение, дополнив этот пример кода.
-- 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;