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

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

## はじめに

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

## 結果の可視化

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2496939/ea4ad5fc-5c6c-8aa8-6791-c15fae6f2e9a.png)


## データセットの準備

使用するデータセットは`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日）をカレンダーテーブルとして作成します。

```sql
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 |

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


```sql
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|

# 勝敗データの計算
```sql
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の設定については別の記事を参考にしてください。また、ご不明な点があれば、コメント欄でお知らせください。