ドコモシェアバイクのデータをBigQueryで分析しよう！(データ分析編)

# はじめに
こんにちは、京セラコミュニケーションシステム　川村（@kccs_takahiro-kawamura）です。
ドコモシェアバイクのデータをBigQueryで分析しよう！PART.2の記事になります。  
[前回](https://qiita.com/kccs_takahiro-kawamura/items/0dc075f90b758913a60a)はオープンデータを __BigQuery__ へ定期的に取り込む方法について記載しました。  
本記事では、__BigQuery__ と __Looker Studio__ を活用し、早く！確実に！シェアバイクを利用して会社から帰宅するためのダッシュボードを作成します。


:::note info
本記事は、2022年12月ごろに作成しております。よって、引用している文章などはこの時点での最新となります。ご了承ください。
:::  
:::note info
Looker Studioは、旧データポータルを指します。
:::

## 本記事の対象者
- __BigQuery__ で地理関数を用いた分析がしたい方
- __BigQuery__ のデータを __Looker Studio__ で表示したい方

## ダッシュボードの要件
早く！確実に！シェアバイクを利用して会社から帰宅するためには、次の要件を満たすダッシュボードが欲しいと思いました。  
* 利用可能な自転車の多いステーションがわかる
* 会社から近いステーションがわかる

これらの要件を満たすために、__BigQuery__ でSQLを実行します。

## 利用可能な自転車の多いステーションの検索
まずは、利用可能な自転車の多いステーションを検索します。  
利用可能な自転車の数は、`station_status`テーブルの`num_bikes_available`カラムに格納されています。  
`station_status`テーブルだけではどこのステーションかわからないため、`station_infomation`テーブルと結合し、ステーション名も合わせて出力します。また、`is_renting`カラムで貸し出し有効なステーションかどうか判別できるためWHERE句に含めます。
```sql
SELECT name, num_bikes_available FROM `docomo_cycle_tokyo.station_status` status
LEFT JOIN `docomo_cycle_tokyo.station_information` info ON status.station_id = info.station_id 
WHERE is_renting
```
以下の結果が得られました。
![bigquery_result_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/85d7af07-58df-547e-3cea-495b1e237c43.png)
バッチで定期的にデータを取り込んでいるため、最新のデータだけでなく過去のデータも表示されてしまいました。  
__BigQuery__ は、実行するSQLが検索するデータ量に応じて課金が発生するため、これでは不要なデータの検索を行いムダな課金を発生させてしまいます。__BigQuery__ の課金については[こちらの記事](https://qiita.com/kccs_hiromi-nishida/items/00ddcfe1873df8f5ca60)をご確認ください。   
最新のデータを表示するために検索範囲を絞り込みます。  テーブル作成時にパーティショニングの**取り込み時間による分割**を有効にしているので、`_PARTITIONTIME`というカラムをWHERE句に含めることで検索範囲を絞り込めます。  
```sql
WITH status AS (SELECT * FROM `docomo_cycle_tokyo.station_status` WHERE _PARTITIONTIME = 
(SELECT MAX(_PARTITIONTIME) FROM `docomo_cycle_tokyo.station_status` 
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)))

SELECT name AS Station, num_bikes_available FROM `docomo_cycle_tokyo.station_information` AS info
LEFT JOIN status ON info.station_id = status.station_id WHERE is_renting
```
以下の結果が得られました。  
![bigquery_result_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/cdebcba2-eee0-893d-4f7d-33004a68dbfe.png) 
WITH句を用いて最新のデータのみに絞り込んだ`station_status`テーブルを一時テーブルとして用意しています。WHERE句でサブクエリを用いて`_PARTITIONTIME`カラムを絞り込むことで最新のデータのみを検索しています。  
ただし、 単純に 
```sql
SELECT MAX(_PARTITIONTIME) `docomo_cycle_tokyo.station_status`
```
としてしまうとサブクエリ内で`station_status`テーブルを全件検索してしまうことになるため、サブクエリ内のWHERE句で直近3時間のデータに絞り込むことで全件検索を避けています。  
ここまでで、最新のデータを用いて利用可能な自転車の多いステーションがわかるようになりました。

## 会社から近いステーションの検索
続いて、会社から近いステーションを検索します。 __BigQuery__ には地理関数が用意されており、そのうち今回は**ST_GeogPoint関数**と**ST_DISTANCE関数**を使用します。  
**ST_GeogPoint関数**は、緯度経度からその地点のGEOGRAPHY値を求めます。**ST_DISTANCE関数**は、2点間の最短距離を計算します。これは、ほぼ直線距離に近いような値が求められます。  
私が現在勤務している場所は、東京都港区三田にあるビルです。ここの緯度経度は（139.74194914694743, 35.64165246203347）のため、これを基準に会社から近いステーションを検索します。  
```sql
SELECT name AS Station, lon, lat, 
ST_DISTANCE(ST_GEOGPOINT(lon, lat), ST_GEOGPOINT(139.74194914694743, 35.64165246203347)) as EstimatedDistance_in_Meters
FROM `docomo_cycle_tokyo.station_information` as info
ORDER BY EstimatedDistance_in_Meters LIMIT 10
```
以下の結果が得られました。  
![bigquery_result_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/b272d9a6-d819-7d46-a172-8424287967c4.png) 
`EstimatedDistance_in_Meters`カラムが会社からステーションまでの距離を表しています。  
近いステーションだけで良いのでLIMIT句を用いて10件のみを取得しています。  

:::note warn
LIMIT句は、結果件数を指定するだけです。検索範囲は絞り込まれないため、LIMIT句を用いてもSQLの実行にかかる費用は削減されません。
:::

## ダッシュボード用のビューを作成  
ここまでで必要なデータを用意することができました。2つのSQLを合わせて実行してみます。  
```sql
WITH status AS (SELECT * FROM `docomo_cycle_tokyo.station_status` WHERE _PARTITIONTIME = 
(SELECT MAX(_PARTITIONTIME) FROM `docomo_cycle_tokyo.station_status`
 WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)))

SELECT name AS Station, lon, lat, 
ST_DISTANCE(ST_GEOGPOINT(lon, lat), ST_GEOGPOINT(139.74194914694743, 35.64165246203347)) AS EstimatedDistance_in_Meters,
num_bikes_available,
CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(CAST(last_reported AS INT64)), 'Asia/Tokyo') AS DATETIME) AS last_reported
FROM `docomo_cycle_tokyo.station_information` AS info
LEFT JOIN status ON info.station_id = status.station_id WHERE is_renting
ORDER BY EstimatedDistance_in_Meters LIMIT 10
```
以下の結果が得られました。  
![bigquery_result_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/9045586d-1562-5ee2-5ee0-547077d3b16f.png)
`last_reported`カラムは`station_status`テーブルの更新時間です。ダッシュボードでは、表示しているデータがいつのものなのかわかるようにしたいため追加しておきます。`last_reported`カラムはREST APIで取得したままだとエポック秒になっています。そのため、一度TIMESTAMP型に変換し、さらにタイムゾーンを東京にしたいためDATETIME型へ変換しています。  
このSQLをビューとして保存します。**保存**→**ビューを保存**と選択し、必要な情報を入力し保存します。  
![bigquery_view_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/0e4e132e-be33-a760-fcfa-fd8fa54ab50a.png)
![bigquery_view_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/47234ee5-3101-785b-2428-55b4d8b690bc.png)  
左側のエクスプローラー内に保存したビューが作成されてます。`Looker Studio`からこのビューを参照することで上記のSQLの実行結果を得ることができます。  

## ダッシュボードの作成  
[Looker Studio](https://datastudio.google.com/)にアクセスして**空のレポート**を作成します。  
レポートに追加するデータソースの選択を要求されるので __BigQuery__ を選択します。続けて、先ほど作成したビューを追加できるようにプロジェクト、データセット、表を選択します。  

:::note info
ダッシュボード作成者は、追加するデータソースに対しての読み取り権限が必要になります。
:::
![looker_studio_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/e74e232b-b764-73e8-fcdf-b39ad1430afd.png)
![looker_studio_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/e90d9310-853a-2429-d51b-eb330af24df9.png)
無事にデータを追加できると以下のようなダッシュボードが表示されます。  
![looker_studio_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/76d59aed-4e8d-7ebd-71a1-5af3b9b11970.png) 
今回は地理データを活用しているので直感的にわかりやすい**バブルマップ**を使用します。  
ダッシュボード上の表を選択してから右上にある**グラフ**を選択するとビジュアリゼーションのタイプを選択できます。その中から**バブルマップ**を選択します。以下の画像のように設定するとバブルマップ上にステーションの座標が描画されます。  
![looker_studio_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/f5107105-422d-61f4-8d72-77c6a8ef9dda.png)
![looker_studio_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/0edabfe8-2be5-9dfc-7ba4-4bc9ae42d381.png)  
どのステーションに利用可能な自転車が多いのか一目で把握できるよう`num_bikes_available`が多いステーションほど大きな円で描画されています。  
表示されているデータがいつ時点のデータなのかわかるように最終更新日時を表示します。上部にあるメニューから**グラフを追加**を選択して**スコアカード**を選択します。**期間のディメンション**を`last_reported(日付)`にし、**指標**を`last_reported`にします。**指標**の`last_reported`は**MAX**を使用し、別名で**最終更新日時**とします。また、背景が透明だとわかりづらいので**スタイル**で背景色と文字色を変更します。  
![looker_studio_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/52f8ccf5-5250-2d7e-bca8-c87efee8c5c0.png)  
表でバブルマップの情報も表示しておきます。先ほどと同様に**グラフを追加**を選択して**表**を選択します。**期間のディメンション**を`last_reported(日付)`にし、**ディメンション**を`Station`、**指標**を`num_bikes_available`、**並び替え**を`num_bikes_available`にします。また、**ディメンション**と**指標**はわかりやすいように別名で**ステーション**と**乗車可能バイク数**にします。こちらも**スタイル**で背景色と文字色を変更します。  
以上でダッシュボードの完成です。  
![looker_studio_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/164163/d19d3ccc-775b-c2c3-aaa1-bd1db0cfb66d.png) 

# 最後に  
前回と今回の記事で、オープンデータをREST API経由で定期的に __BigQuery__ へ蓄積し、SQLで分析した結果を __Looker Studio__ を用いてダッシュボードにすることができました。  
__BigQuery__ でデータ分析を行う際は必要なデータの範囲を検討し、パーティションを限定して実行することが重要です。  
今回は最新データを活用したデータ分析でした。次回は、蓄積していることを活かしたデータ分析に取り組みたいと思います。