2018年ワールドカップの全ゴールを視覚化する

以下は[The 2018 World Cup Visualized: All the Goals So Far](https://medium.freecodecamp.org/world-cup-all-the-goals-in-2018-b52e5ef53e0a)の日本語訳です。

# The 2018 World Cup Visualized: All the Goals So Far

Data Studioを使って、全てのゴールがどこから撃たれたかインタラクティブに可視化した。
データはBigQueryで抽出された。
詳細は以下に記す。


[![Interactive dashboard](https://qiita-image-store.s3.amazonaws.com/0/26088/41b223b8-e554-a1b8-d74e-1e07a025269e.png "Interactive dashboard")](https://datastudio.google.com/u/0/reporting/1-Vyt0ZX4NT0t3c1qd2T4gqRQe7bVF6fU)

各点は、プレイヤーが得点した場所を示しています。

左側：自殺点、自チームのゴールに蹴り込んでしまったことを表します。
右側：気になるチームだけを表示するインタラクティブな操作が可能です。

<iframe width="600" height="259" src="https://datastudio.google.com/embed/reporting/1-Vyt0ZX4NT0t3c1qd2T4gqRQe7bVF6fU/page/ADsT" frameborder="0" style="border:0" allowfullscreen></iframe>
https://datastudio.google.com/u/0/reporting/1-Vyt0ZX4NT0t3c1qd2T4gqRQe7bVF6fU/page/ADsT

さらに詳しい分析については、[Eric Schmidt](https://medium.com/@easchmidt)の[Analyzing the World Cup using Google Cloud](https://medium.com/analyzing-the-world-cup-using-google-cloud)をご覧ください。

## How I Built this Visualization

### Data acquisition

データ収集。
このチャートは、2014年のワールドカップ結果を分析するのに使ったのと同じソースを使用しています。
生データを再共有することはできませんので、必要であれば[Opta Sports](https://www.optasports.com/)とライセンス契約してください。

<blockquote class="twitter-tweet" data-lang="ja"><p lang="en" dir="ltr"><a href="https://t.co/G2R22iR1ZX">https://t.co/G2R22iR1ZX</a><br>Google I/O 2014 - Predicting the future with the Google Cloud Platform</p>&mdash; ラナ・クアール (@rana_kualu) <a href="https://twitter.com/rana_kualu/status/1011563421349769217?ref_src=twsrc%5Etfw">2018年6月26日</a></blockquote>
<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>


### Extracting the goals

得点情報の抜き出し。
チームIDからチーム名を取り出すコードは少々トリッキーでした。
自殺点は自チームがマイナスされるものであって、自チームの得点になるものではありません。

```sql
#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](https://twitter.com/anilopez/status/958382085407883264)を修正して使用しました。
結果をクエリに保存するかわりに同じシートに保存するようにしました。

<blockquote class="twitter-tweet" data-lang="ja"><p lang="en" dir="ltr">Simplest way to schedule <a href="https://twitter.com/hashtag/BigQuery?src=hash&amp;ref_src=twsrc%5Etfw">#BigQuery</a> jobs. GSheet &gt; <a href="https://t.co/xTEbR79PD3">https://t.co/xTEbR79PD3</a><br>Sends Success/Fail email<br>1 Copy the sheet<br>2 Update the query and the details of the table to append results<br>3 Go to Tools &gt; Script Editor<br>4 Create trigger for the day/time you want the query to run<br>Enjoy!</p>&mdash; Ani Lopez (@anilopez) <a href="https://twitter.com/anilopez/status/958382085407883264?ref_src=twsrc%5Etfw">2018年1月30日</a></blockquote>
<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>


### Connect Data Studio to the sheet

BigQueryの結果をData Studioに取り込むかわりに、前述のスプレッドシートを指定します。

### Visualize

Data Studioすげー :)

### Refresh

データセットが更新されるとスプレッドシートのクエリが実行され、そしてData Studioに反映されます。

## Next steps

もっと話をしたいですか？
[Medium](http://medium.com/@hoffa/)と[Twitter](http://twitter.com/felipehoffa)をチェックしてください。
[reddit.com/r/bigquery](https://www.reddit.com/r/bigquery/)を読んで、[BigQueryにチャレンジ](https://www.reddit.com/r/bigquery/comments/3dg9le/analyzing_50_billion_wikipedia_pageviews_in_5/)しましょう。
毎月[1テラバイトまで無料で分析](https://cloud.google.com/blog/big-data/2017/01/how-to-run-a-terabyte-of-google-bigquery-queries-each-month-without-a-credit-card)できます。



# 感想

Googleすげー、Data Studioすげー、って話なのですが、途中が省略されすぎててよくわかりません。

著者の[Felipe Hoffa](https://medium.freecodecamp.org/@hoffa)はGoogleの[Developer Advocate](https://blog.agektmr.com/2013/04/google-developer-advocate.html)で、要するにGoogleを使えばこんなこともできるぞーって紹介する人ですね。
ということでまあ、確かにすごいというのはわかるのですが、具体的な中身が`That’s the Data Studio magic :)`しか書かれてないので実際どうすればいいのかさっぱりだ。

あとサッカー全く見てないので正しいのかどうかわかりません。