BigQuery分析のGoogle SKillsをやってみた

Google SkillsのBigQueryに関する学習をきっかけに、公開データを使った集計と時系列分析を試した。

公開にあたって: この記事は個人の学習メモです。教材の設問、指定値、解答、採点条件や画面は再現せず、内容をぼかして独自の分析例に置き換えています。以下のSQLはラボの解答ではなく、実行結果や採点結果も確認していません。

今回試したこと

BigQueryの公開データを題材に、次の点を確認した。

• 国単位と地域単位の行を区別して集計する
• 累積値から直前の記録との差分を計算する
• ウィンドウ関数を使って順位や移動平均を求める
• 時系列グラフに載せる数値の意味を整理する

ここではCOVID-19 Open Dataを例にする。国と下位地域の行を混ぜて集計すると、意図した単位の数値にならない可能性があるため、まず対象行を決める。

例1：週ごとの記録を比較する

各週に含まれる記録のうち、最後の日付の行を取り出す。週内の累積値を足し合わせるのではなく、週ごとのスナップショットとして見るための例。

WITH country_rows AS (
  SELECT
    date,
    country_code,
    cumulative_confirmed,
    DATE_TRUNC(date, WEEK(MONDAY)) AS week_start
  FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE country_code IN ('JP', 'CA', 'AU')
    AND subregion1_name IS NULL
    AND date BETWEEN DATE '2020-06-01' AND DATE '2020-08-31'
)
SELECT
  week_start,
  country_code,
  date AS observation_date,
  cumulative_confirmed AS reported_total
FROM country_rows
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY country_code, week_start
  ORDER BY date DESC
) = 1
ORDER BY week_start, country_code;

week_startは比較用の週区分、observation_dateは実際に選ばれた記録日。対象期間の端にある週は、週全体ではなく期間内の記録から選ばれる。

例2：累積値から差分と移動平均を作る

直前の記録との差分をLAG()で求め、その差分について直近7行の平均を計算する。

WITH observations AS (
  SELECT
    date,
    cumulative_confirmed AS reported_total
  FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE country_code = 'JP'
    AND subregion1_name IS NULL
    AND date BETWEEN DATE '2020-06-30' AND DATE '2020-07-31'
),
changes AS (
  SELECT
    date,
    reported_total,
    reported_total
      - LAG(reported_total) OVER (ORDER BY date) AS change_from_previous
  FROM observations
)
SELECT
  date,
  reported_total,
  change_from_previous,
  AVG(change_from_previous) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS recent_7_row_average
FROM changes
WHERE date >= DATE '2020-07-01'
ORDER BY date;

6月30日の行も取得しているのは、7月1日の差分を計算するため。ただし、LAG()が参照するのは厳密な「前日」ではなく直前の行。欠損日があれば、移動平均も厳密な「7日間」の平均にはならない。累積値が修正されると差分が負になる可能性もある。

例3：地域ごとの値に順位を付ける

今度は国単位ではなく、一次地域の行を対象にする。順位付けの練習例であり、地域間の状況を評価する指標として使うものではない。

SELECT
  subregion1_name AS region,
  cumulative_confirmed AS reported_total,
  DENSE_RANK() OVER (
    ORDER BY cumulative_confirmed DESC
  ) AS value_rank
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE country_code = 'JP'
  AND date = DATE '2020-07-15'
  AND subregion1_name IS NOT NULL
  AND subregion2_name IS NULL
  AND cumulative_confirmed IS NOT NULL
ORDER BY value_rank, region;

同じ列を使っていても、国単位の行と地域単位の行では集計対象が異なる。後から結果を読み直せるよう、対象行を選んだ条件も残しておきたい。

可視化するなら

日付を横軸にして累積値や差分の推移を確認できる。ただし、累積値と差分の移動平均は意味も値の規模も異なるため、別々のグラフに分けた方が読み取りやすい。ここでは教材の画面や操作手順は掲載しない。

まとめ

今回の学習で意識したのは、SQLを書く前に集計対象の行と数値の意味を決めること。累積値、ある時点の値、直前の記録との差分は別の指標になる。クエリと一緒に地理階層、対象期間、欠損値の扱いを記録しておく。

※ このメモは教材の利用条件や画像の権利について判断するものではありません。公開する場合は、教材の転載やスクリーンショットの扱いを別途確認してください。