以下はThe 2018 World Cup Visualized: All the Goals So Farの日本語訳です。
The 2018 World Cup Visualized: All the Goals So Far
Data Studioを使って、全てのゴールがどこから撃たれたかインタラクティブに可視化した。
データはBigQueryで抽出された。
詳細は以下に記す。
各点は、プレイヤーが得点した場所を示しています。
左側:自殺点、自チームのゴールに蹴り込んでしまったことを表します。
右側:気になるチームだけを表示するインタラクティブな操作が可能です。
さらに詳しい分析については、Eric SchmidtのAnalyzing the World Cup using Google Cloudをご覧ください。
How I Built this Visualization
Data acquisition
データ収集。
このチャートは、2014年のワールドカップ結果を分析するのに使ったのと同じソースを使用しています。
生データを再共有することはできませんので、必要であればOpta Sportsとライセンス契約してください。
https://t.co/G2R22iR1ZX
— ラナ・クアール (@rana_kualu) 2018年6月26日
Google I/O 2014 - Predicting the future with the Google Cloud Platform
Extracting the goals
得点情報の抜き出し。
チームIDからチーム名を取り出すコードは少々トリッキーでした。
自殺点は自チームがマイナスされるものであって、自チームの得点になるものではありません。
# standardSQL
WITH team_names AS (
SELECT team_id, MIN(name) name
FROM (
SELECT away_team_id team_id, away_team_name name
FROM `cloude-sandbox.galacticos.games`
WHERE competition_id = 4
UNION ALL
SELECT home_team_id team_id, home_team_name name
FROM `cloude-sandbox.galacticos.games`
WHERE competition_id = 4
)
GROUP BY 1
)
SELECT a.* EXCEPT(game_id, player_id), b.name versus
, (SELECT MAX(name) FROM `cloude-sandbox.galacticos.sqauds` WHERE player_id = a.player_id) player
FROM (
SELECT id, DATE(event_timestamp) date, x, y, min minute
, (SELECT name FROM team_names WHERE team_id=a.team_id) team
, game_id, CONCAT('p', CAST(player_id AS STRING)) player_id
FROM `cloude-sandbox.galacticos.events` a
WHERE competition_id = 4
AND type_id IN (16)
AND DATE(event_timestamp) > '2018-01-01'
) a
JOIN (
SELECT id, home_team_name name FROM `cloude-sandbox.galacticos.games` UNION ALL SELECT id, away_team_name name FROM `cloude-sandbox.galacticos.games`
) b
ON a.game_id = b.id
AND a.team != b.name
ORDER BY 1, 2
Scheduling result extraction to a Google sheet
抽出結果のGoogleスプレッドシートへの貼り付けのスケジューリング。
私はAni LopezによるBigQuery scheduling sheetを修正して使用しました。
結果をクエリに保存するかわりに同じシートに保存するようにしました。
Simplest way to schedule #BigQuery jobs. GSheet > https://t.co/xTEbR79PD3
— Ani Lopez (@anilopez) 2018年1月30日
Sends Success/Fail email
1 Copy the sheet
2 Update the query and the details of the table to append results
3 Go to Tools > Script Editor
4 Create trigger for the day/time you want the query to run
Enjoy!
Connect Data Studio to the sheet
BigQueryの結果をData Studioに取り込むかわりに、前述のスプレッドシートを指定します。
Visualize
Data Studioすげー :)
Refresh
データセットが更新されるとスプレッドシートのクエリが実行され、そしてData Studioに反映されます。
Next steps
もっと話をしたいですか?
MediumとTwitterをチェックしてください。
reddit.com/r/bigqueryを読んで、BigQueryにチャレンジしましょう。
毎月1テラバイトまで無料で分析できます。
感想
Googleすげー、Data Studioすげー、って話なのですが、途中が省略されすぎててよくわかりません。
著者のFelipe HoffaはGoogleのDeveloper Advocateで、要するにGoogleを使えばこんなこともできるぞーって紹介する人ですね。
ということでまあ、確かにすごいというのはわかるのですが、具体的な中身がThat’s the Data Studio magic :)
しか書かれてないので実際どうすればいいのかさっぱりだ。
あとサッカー全く見てないので正しいのかどうかわかりません。