Rata-rata bulanan dengan tanggal yang hilang
Temukan rata-rata jumlah permintaan Evanston 311 yang dibuat per hari untuk setiap bulan dalam data.
Kali ini, jangan abaikan tanggal tanpa permintaan.
Latihan ini adalah bagian dari kursus
Analisis Data Eksploratif di SQL
Petunjuk latihan
- Hasilkan deret tanggal dari 2016-01-01 hingga 2018-06-30.
- Gabungkan deret tersebut dengan sebuah subkueri untuk menghitung jumlah permintaan yang dibuat per hari.
- Gunakan
date_trunc()untuk mendapatkan bulan daridate, yang memiliki semua tanggal, BUKAN dariday. - Gunakan
coalesce()untuk mengganti nilaicountyang NULL dengan 0. Hitung rata-rata dari nilai ini.
Latihan interaktif praktis
Cobalah latihan ini dengan menyelesaikan kode contoh berikut.
-- 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;