【SQL基礎】BigQueryを使って実際に分析してみた

はじめに

SQLの学習を最近始めて何かアウトプットしたいと思い、実際にbigqueryにある一般公開データセットを使ってデータを抽出する作業をやってみたので、その内容をまとめます。

SQLの学習は以下の書籍にある巻末の演習問題を解いています。なんと200問もあります。

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析

本記事の概要

1. データの準備
2. データの理解
3. 仮説を立てる
4. クエリを書く
5. 結論

実際の分析

1. データの準備

自分ではデータセットを準備できないので、Googleが無料で提供しているbigqueryの一般公開データセットを使いました。

この中でも、Chicago Taxi Tripsのデータセットを使います。

2. データの理解

Chicago Taxi Tripsのデータセットはどのようなデータが格納されているのかを確認します。

このデータセットには2013年からおよそ10年分のアメリカ・シカゴ市のタクシー利用に関するデータがまとめられています。

タクシーの乗降時刻、乗車時間、乗車距離、運賃、チップの額、タクシー会社など、様々な情報があることがわかります。

3. 仮説を立てる

ずっと前から海外のチップ文化について知りたいと思っていたので、タクシーのデータを使って海外の人（今回では特にアメリカ）はどういう時にチップを払うのかについて調べることにしました。

私はチップの文化を知らないのであくまで想像ですが、利用料金以上に金額を支払うということはチップを払う人の気分が相当良いか、または接客などサービス内容が良かったかの二つに左右されるのではないかと思いました。

そこでいくつか仮説を立てました。

(1) そもそもどれくらいの人がチップを払うのか（単純な疑問）

(2) 払うとしたら気分が上がる週末に払う人が多いのか？

(3) 払うとしたら仕事が終わって気分が上がる夕方以降に払う人が多いのか？

(4) 乗車距離が長いほど払う人が多くなる？

※接客サービスの良さで違いが出るのかを調べるためにタクシー会社ごとでチップの支払い回数に傾向があるかを調べたかったのですが、うまく出力できなかったため割愛します。

この4つの仮説を検証していきたいと思います。

4. クエリを書く

3で立てた仮説を検証するためにデータセットからデータを抽出するためのSQLのクエリを書いていきます。

(1) そもそもどのくらいの人がチップを払うのか？

チップを払ったデータの数を全データ数で割ればチップを払う人の割合がわかります。

SELECT 
  COUNT(*) AS total_trips,
  SUM(IF (tips > 0, 1, 0)) AS trips_with_tips,
  ROUND(100 * SUM(IF (tips > 0, 1, 0)) / COUNT(*), 2) AS tips_rate
FROM
  bigquery-public-data.chicago_taxi_trips.taxi_trips
;

割合は見やすくするためにROUND関数を使って小数点をまとめています。

およそタクシー利用者の約4割の人がチップを払っていることがわかりました。意外と多くてびっくりしました。

今回はタクシーに限った話になりますが、チップを払う割合が大体これでわかりました。

(2) 払うとしたら気分が上がる週末に払う人が多いのか？

自分も含め週末が近づくにつれてテンションが上がる人は多いと思い、曜日ごとのチップの支払い回数を調べてみました。

曜日ごとにグループ化してチップの支払いが発生したデータの数を数えます。

SELECT 
  FORMAT_DATETIME('%A', trip_end_timestamp) AS day_of_week, 
  COUNT(*) AS trips_with_tips,
  ROUND(AVG(tips), 2) AS avg_tips
FROM
  bigquery-public-data.chicago_taxi_trips.taxi_trips
WHERE
  tips > 0
GROUP BY
  day_of_week
ORDER BY
  trips_with_tips DESC;

降車時刻は2017-02-19 05:30:00.000000 UTCのように日時型の値になっているので、FORMAT_DATETIME関数を使ってこれを曜日に変換します。

最後にORDER BYを使ってチップの支払い回数が多い順に並べ替えます。

チップの金額も知りたかったのでavg_tipsでチップの平均金額を出力しています。

木曜金曜にチップを払う人が多いということがわかります。やはり週末の力は大きいということでしょうか。

しかし、土日は他の曜日と比べて少なくなっています。月曜日が近づいていることからみんな気分が下がっているということなのか。はっきりとはわかりません。

チップの額も週末にかけて大きくなるかと思いましたが、そこはあまり関係なさそうです。

(3) 払うとしたら仕事が終わって気分が上がる夕方以降に払う人が多いのか？

曜日だけでなく、時間帯でも違いが出るのか気になったので調べてみることにします。

仕事が終わったタイミングが一番気分がいい人が多いと考え、夕方から夜にかけてがチップを払う人が多いと仮定しました。これを検証していきます。

(2)で日時型の値を曜日に変換しましたが、ここでは時間に変換して時間ごとにチップの支払いが発生した回数を数えます。

SELECT 
  FORMAT_DATETIME('%H', trip_end_timestamp) AS time_period, 
  COUNT(*) AS trips_with_tips
FROM 
  bigquery-public-data.chicago_taxi_trips.taxi_trips
WHERE 
  tips > 0
GROUP BY 
  time_period
ORDER BY 
  trips_with_tips DESC
LIMIT
  5
;

以上の結果から、上位5つの時間帯は全て17時以降という結果になりました。
仕事が終わって気分がいいという要因が全てだとは言い切れないですが、少なくとも夕方以降にチップを払う人が多い傾向があるということはわかりました。

(4) 乗車距離が長いほど払う人が多くなる？

「長時間運転してくれてありがとう」という運転手への感謝の気持ちが高まることでチップを払う人が多いのではないかと考えました。

乗車距離(miles)は最大で3460だったので500ずつで区切り、それぞれの範囲内でのチップを支払った人の割合とチップの平均を比較することにします。

-- 最初にサブクエリで乗車距離の範囲とチップがあったかどうかのフラグを作成しておく
WITH milesdata AS (
  SELECT
    trip_miles,
    -- 乗車距離を範囲ごとに分類
    CASE
      WHEN trip_miles >= 0 AND trip_miles < 500 THEN '0-499'
      WHEN trip_miles >= 500 AND trip_miles < 1000 THEN '500-999'
      WHEN trip_miles >= 1000 AND trip_miles < 1500 THEN '1000-1499'
      WHEN trip_miles >= 1500 AND trip_miles < 2000 THEN '1500-1999'
      WHEN trip_miles >= 2000 AND trip_miles < 2500 THEN '2000-2499'
      WHEN trip_miles >= 2500 AND trip_miles < 3000 THEN '2500-2999'
      WHEN trip_miles >= 3000 THEN '3000-'
    END AS miles_range,
    tips,
    -- チップが発生した場合は1を、なかった場合は0のフラグ
    IF(tips > 0, 1, 0) AS is_tipped
  FROM 
    bigquery-public-data.chicago_taxi_trips.taxi_trips
)
-- サブクエリをもとにデータを抽出
SELECT
  miles_range,
  -- チップの割合を計算
  ROUND(SUM(is_tipped) / COUNT(*) * 100, 2) AS tips_rate,
  --チップの平均を計算
  ROUND(AVG(tips), 2) AS avg_tips
FROM 
  milesdata
WHERE 
  miles_range IS NOT NULL  -- 欠損値の除外
GROUP BY 
  miles_range
ORDER BY
  tips_rate DESC  
;

わかりやすいように最初にWITHを使って乗車距離を500ずつで区切ったデータを作成しておきます。

そのデータから割合や平均を算出します。

結果を見ると乗車距離とチップを支払った人の割合はそこまで相関があるとは言い切れないですが、乗車距離が1000以下と比べるとチップを支払う人は多い傾向にあるようです。

支払ったチップの金額に着目すると割合よりも傾向が強く出ていることがわかります。以下はチップの平均の大きさ順で並べ替えた結果です。

乗車距離が長いほど支払うチップの額が多くなっていることがわかります。

5. 結論

チップを払う人の機嫌の良さ、サービス内容の二つによってチップが支払われるという仮説を立てて実際に検証を行いました。

週末や夕方にチップを払う人が多いのはテンションが上がっていることに起因していると断定することは難しいですが、週末や夕方、乗車距離が長いほどチップを払う人が多いという傾向があるということはわかりました。

まとめ

データセットから自分が取得したいデータを取るためにSQLのクエリを書いていくという作業は非常に勉強になりました。

関数やサブクエリなどどういう時に使うのだろうと勉強しながら疑問に思っていましたが、実践することでそれぞれの役割をしっかりと理解することができました。

問題演習によるアウトプットも重要ですが、実戦に勝るものはないと今回の作業を通して痛感しました。

今後はJOINなどを使って複数のテーブルがあるデータセットからデータを抽出するということにもチャレンジしていこうと思います。