始める無料で始める

欠損日のある月ごとの平均

Evanston の 311 リクエストについて、データの各月ごとに、1 日あたりに作成されたリクエストの平均数を求めてください。

今回は、リクエストがない日付も無視しないでください。

この演習はコースの一部です

SQLによる探索的データ分析

コースを見る

演習の手順

  • 2016-01-01 から 2018-06-30 までの日付の系列を生成します。
  • その系列を、1 日あたりに作成されたリクエスト数を数えるサブクエリに結合します。
  • すべての日付を含む date から月を得るために date_trunc() を使います。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;
コードを編集して実行