また「WHERE 句」は AND や OR を使って条件を組み合わせることができます。
例えば AND を利用する例として、Ichiro という選手が複数いるような場合では以下のように、条件を AND で複数追加することでさらにデータを絞り込むことができます。
SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' AND hitterLastName='Suzuki'
OR を使う場合の例として、イチロー選手がバッター、又は、ダルビッシュ選手がピッチャーとして出ていたデータを取得したい場合は以下のように OR 条件を使うことで一度のクエリで取得することもできます。
（同一のカラムに対して、OR 条件でクエリをしたい場合は IN 句を利用することも可能です。）
SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' OR pitcherLastName='Darvish'
SELECT COUNT(*) FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro'
上の例ではイチロー選手が打席にたった時の投球数をカウントしましたが、試合数ごとの投球数を取得したい場合、試合（gameId）ごとにデータ数をカウントする必要があります。
このような時にグルーピングができる GROUP BY 句が役に立ちます。
以下のようなクエリを使うと、gameId が同じものは一つのグループとして取得することができるため、簡単に各試合ごとの投球数をカウントすることができます。
（GROUP BY 句を利用する場合、そのカラムを SELECT 句で指定する必要があります）
SELECT gameId, COUNT(gameId) FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' GROUP BY gameId
カラム名の変更（AS 句）
取得したデータのカラムの表示名を AS 句を利用することで表示名を変更することができます。
取得した状態のカラム名だとわかりにくい場合や、次項に説明する複雑なクエリを利用していく場合は可読性を向上させるために頻繁に利用されます。
SELECT gameId, COUNT(gameId) as pitchCount FROM `bigquery-public-data.baseball.games_wide` WHERE hitterFirstName='Ichiro' GROUP BY gameId
SELECT pitchTypeDescription, AVG(pitchSpeed) as avgPitchSpeed FROM `bigquery-public-data.baseball.games_wide` WHERE pitcherLastName='Darvish' GROUP BY pitchTypeDescription
SUM
次に、SUM を使って来場者の合計を取得したいと思います。
SELECT SUM(attendance) FROM `bigquery-public-data.baseball.games_wide`
このクエリでこのデータ上に存在する来場者の合計を求めることができました。
しかし、今回扱っているデータは投球ごとに集計されているため、この来場者の合計はあまり有用なデータとして使うことはできません。
そこで、次項で説明するサブクエリを使って、全試合の合計来場者数を計算してみたいと思います。
SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId
この結果の attendance の合計を取得することができれば、全試合の合計来場者数を算出することができることがわかると思います。それでは、サブクエリを使ってこの結果にさらにクエリを実行してみます。
SELECT SUM(attendance) as sumAttendance FROM (SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId)
このように FROM 句の後に先ほどのクエリを付け足すことでサブクエリとして使うことができます。
一方で、サブクエリを使うと、クエリが長くなってしまい可読性が落ちてしまうため、WITH 句を利用して、サブクエリ結果を一時的なテーブルとして扱うことで可読性を高めることができます。
WITH 句を利用したクエリは以下の通りです。
WITH games_attendance AS(SELECT gameId, AVG(attendance) as attendance FROM `bigquery-public-data.baseball.games_wide` GROUP BY gameId) SELECT SUM(attendance) as sumAttendance FROM games_attendance
ここでは、Rogers Centre 球場の来場者数の 5 日分のデータを 1 つのウィンドウフレームとして、移動平均を計算したいと思います。
まず、Rogers Centre 球場での試合の来場者数を日付順に取得します。
SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime
WITH games_attendance AS (SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime) SELECT startTime, AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 4 preceding AND current row) as ma from games_attendance ORDER BY startTime AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 4 preceding AND current row)
少しややこしくなってきましたが、括弧の中から順にみていくと理解しやすいです。
startTime 順のデータ(ORDER BY startTime)に対して、4 行前のデータ(4 preceding)から現在処理しているデータ(current row)までの attendance を平均[AVG(attendance)]しています。これで移動平均を求めることができます。
ここでは WITH 句を利用して、擬似的にもう一つのテーブルを作成し、それらを結合したいと思います。
例として、Rogers Centre 球場の来場者数の移動平均と、各試合の来場者数を結合し、来場者数の移動平均の値との差を確認したいと思います。これにより平均的な来場者数からばらつきの大きい試合を確認することができるようになります。
WITH games_attendance AS (SELECT gameId, AVG(attendance) as attendance , ANY_VALUE(startTime) as startTime FROM `bigquery-public-data.baseball.games_wide` WHERE venueName='Rogers Centre' GROUP BY gameId ORDER BY startTime),
games_ma AS (SELECT gameId, startTime, AVG(attendance)OVER(ORDER BY startTime ROWS BETWEEN 7 preceding AND current row) as ma from games_attendance ORDER BY startTime)
SELECT games_ma.gameId,ANY_VALUE(games_ma.startTime) as startTime, AVG(games_ma.ma) as ma, AVG(games_wide.attendance) as attendance, (AVG(games_ma.ma)-AVG(games_wide.attendance)) as gap FROM games_ma INNER JOIN bigquery-public-data.baseball.games_wide as games_wide ON games_ma.gameId=games_wide.gameId GROUP BY gameId ORDER BY ANY_VALUE(games_ma.startTime)
クエリ結果は BigQuery 以外でも確認することができます。
特に、Looker Studio を利用すると、複数のデータを 1 つの画面に表示し、詳細に確認したりすることができ、他のユーザとの共有なども簡単に行えるため、データを定期的に確認し経営方針を検討するような場面ではとても利用しやすい BI ツールです。
実際に利用してみたいと思います。
クエリ結果の右上の「データを探索」から「Looker Studio で調べる」に遷移すると、Looker Studio が開きます。
遷移すると以下のようなデータが初期で表示されていますが簡単に編集することができます。
