BigQueryを用いた野球チーム(MLB)の勝率計算とLookerStudioでの可視化

はじめに

この記事では、Google BigQueryを使用して野球チームの勝率を計算し、その結果をLookerStudioで可視化する方法を解説します。本記事の内容はBigQueryに焦点を当てています。GCPやLookerStudioの設定に関する具体的な手順については今後別の記事で詳しく説明する予定ですので、気長にお待ちいただくか、他の記事を参考にしてください。

結果の可視化

最終結果としての可視化例を以下に示します。

データセットの準備

使用するデータセットはbigquery-public-data.baseball.games_wideです。必要なフィールドを選択して新しいテーブルを作成します。

元のデータのサンプル

gameId	seasonId	startTime	...	homeFinalRuns	awayFinalRuns
ff09a6b3-51fb-40e5-9519-81165dd6e1aa	565de4be-dc80-4849-a7e1-54bc79156cc8	2016-06-21 23:10:00	...	2	1

カレンダーテーブルの作成

まず、MLBのシーズンの日付範囲（例：2016年4月1日から2016年9月30日）をカレンダーテーブルとして作成します。

CREATE OR REPLACE TABLE `your_project.your_dataset.calendar` AS
WITH RECURSIVE calendar AS (
  SELECT DATE('2016-04-01') AS date
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM calendar
  WHERE date < DATE('2016-09-30')
)
SELECT * FROM calendar
ORDER BY date;

生成されるカレンダーテーブルのサンプル

date
2016-04-01
2016-04-02

勝敗データの抽出

次に、ゲーム結果から勝敗データを抽出し、毎日の勝敗数を計算します。

CREATE OR REPLACE TABLE `your_project.your_dataset.game_results` AS
SELECT
  DATE(startTime) AS game_date,
  homeTeamName,
  awayTeamName,
  CASE
    WHEN homeFinalRuns > awayFinalRuns THEN homeTeamName
    ELSE awayTeamName
  END AS winner,
  CASE
    WHEN homeFinalRuns < awayFinalRuns THEN homeTeamName
    ELSE awayTeamName
  END AS loser
FROM
  `bigquery-public-data.baseball.games_wide`
WHERE
  gameStatus = 'closed';

生成される勝敗データのサンプル

以下は、クエリ実行後に得られるテーブルの一例です。

game_date	homeTeamName	awayTeamName	winner	loser
2016-04-01	Angels	Royals	Royals	Angels

勝敗データの計算

WITH games AS (
  SELECT
    DATE(game_date) AS game_date,
    homeTeamName,
    awayTeamName,
    winner,
    loser
  FROM
    `your_project.your_dataset.game_results`
),
teams AS (
  SELECT DISTINCT homeTeamName AS team_name FROM games
  UNION DISTINCT
  SELECT DISTINCT awayTeamName AS team_name FROM games
),
calendar_team AS (
  SELECT
    calendar.date AS game_date,
    team.team_name
  FROM
    `your_project.your_dataset.calendar` AS calendar
  CROSS JOIN teams
  WHERE team.team_name NOT IN ('American League', 'National League')
),
daily_results AS (
  SELECT
    game_date,
    team_name,
    SUM(CASE WHEN winner = team_name THEN 1 ELSE 0 END) AS daily_wins,
    SUM(CASE WHEN loser = team_name THEN 1 ELSE 0 END) AS daily_losses
  FROM
    games,
    UNNEST([homeTeamName, awayTeamName]) AS team_name
  GROUP BY
    game_date,
    team_name
),
cumulative_results AS (
  SELECT
    calendar_team.game_date,
    calendar_team.team_name,
    IFNULL(daily_results.daily_wins, 0) AS daily_wins,
    IFNULL(daily_results.daily_losses, 0) AS daily_losses
  FROM
    calendar_team
  LEFT JOIN daily_results
  ON
    calendar_team.game_date = daily_results.game_date
    AND calendar_team.team_name = daily_results.team_name
),
final_results AS (
  SELECT
    game_date,
    team_name,
    SUM(daily_wins) OVER (PARTITION BY team_name ORDER BY game_date) AS cumulative_wins,
    SUM(daily_losses) OVER (PARTITION BY team_name ORDER BY game_date) AS cumulative_losses,
    SAFE_DIVIDE(SUM(daily_wins) OVER (PARTITION BY team_name ORDER BY game_date),
                SUM(daily_wins) OVER (PARTITION BY team_name ORDER BY game_date) + 
                SUM(daily_losses) OVER (PARTITION BY team_name ORDER BY game_date)) AS win_rate
  FROM
    cumulative_results
)
SELECT
  game_date,
  team_name,
  cumulative_wins,
  cumulative_losses,
  win_rate
FROM
  final_results
ORDER BY
  team_name,
  game_date;

クエリ実行後の結果サンプルテーブル

game_date	team_name	cumulative_wins	cumulative_losses	win_rate
2016-04-01	Angels	0	0	NULL
2016-04-02	Angels	0	0	NULL

まとめ

BigQueryを使って野球チームの勝率を計算する方法を紹介しました。GCPの設定については別の記事を参考にしてください。また、ご不明な点があれば、コメント欄でお知らせください。