NYC タクシーとリムジンの移動記録

BigQuery を使用して一般公開データセットをクエリする方法

BigQuery は、フルマネージドのデータ ウェアハウスおよび分析プラットフォームです。SQL クエリを使用して分析できる一般公開データセットが用意されています。BigQuery の一般公開データセットにアクセスするには、ウェブ UIコマンドライン ツールを使用します。また、Java.NETPython など、さまざまなクライアント ライブラリを使用して BigQuery REST API を呼び出すという方法でもアクセスできます。

現在、BigQuery の一般公開データセットは US マルチリージョン ロケーションに格納されています。一般公開データセットをクエリで取得する場合は、コマンドラインで --location=US フラグを指定するか、BigQuery ウェブ UI で処理ロケーションとして US を選択するか、または API でジョブリソースjobReference セクションにある location プロパティを指定します。一般公開データセットは米国内に格納されているため、一般公開データのクエリ結果を別のリージョンにあるテーブルに書き込むことはできず、一般公開データセットのテーブルを別のリージョンにあるテーブルと結合することもできません。

BigQuery の一般公開データセットの使用を開始するには、プロジェクトを作成または選択します。毎月、最初の 1 テラバイトのデータの処理については課金されないため、課金を有効にせずに一般公開データセットのクエリを開始できます。無料枠を超える可能性がある場合は、課金を有効にする必要があります。

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. GCP プロジェクトを選択または作成します。

    [リソースの管理] ページに移動

  3. プロジェクトに対して課金が有効になっていることを確認します。

    課金を有効にする方法について

  4. 新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にする手順は以下のとおりです。 BigQuery API を有効にする。

    Enable the API

データセットの概要

このデータセットは、NYC Taxi and Limousine Commission(TLC)によって収集されたものです。2009 年から現在までにニューヨーク市内でイエロー タクシーおよびグリーン タクシーが対応したすべての運送サービスの記録と、2015 年から現在までにニューヨーク市内でハイヤー(FHV)が対応したすべての運送サービスの記録が含まれます。記録には、乗車 / 降車の日時と場所、移動距離、項目別料金、レートタイプ、支払いタイプ、運転手が記録した乗客数などがあります。このデータセットの詳細については、TLC の Trip Record Data(移動記録データ)をご覧ください。

BigQuery コンソールで、タクシーとハイヤーについてこのデータを探索してみましょう。

ニューヨーク市のデータセットに移動

サンプルクエリ

BigQuery でこのデータに対して実行できる SQL クエリの例をいくつか示します。

このサンプルでは、BigQuery の標準 SQL サポートを使用します。標準 SQL を使用することを BigQuery に認識させるため、#standardSQL タグを使用します。#standardSQL 接頭辞の詳細については、クエリ接頭辞の設定をご覧ください。

2015 年のイエロー タクシーの毎月の賃走回数は?

このクエリは、月別に集計した 2015 年の全イエロー タクシーの賃走回数を返します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  TIMESTAMP_TRUNC(pickup_datetime,
    MONTH) month,
  COUNT(*) trips
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
GROUP BY
  1
ORDER BY
  1

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  TIMESTAMP_TRUNC(pickup_datetime,
    MONTH) month,
  COUNT(*) trips
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
GROUP BY
  1
ORDER BY
  1'

結果を以下に示します。

+---------------------+----------+
|        month        |  trips   |
+---------------------+----------+
| 2015-01-01 00:00:00 | 12748986 |
| 2015-02-01 00:00:00 | 12450521 |
| 2015-03-01 00:00:00 | 13351609 |
| 2015-04-01 00:00:00 | 13071789 |
| 2015-05-01 00:00:00 | 13158262 |
| 2015-06-01 00:00:00 | 12324935 |
| 2015-07-01 00:00:00 | 11562783 |
| 2015-08-01 00:00:00 | 11130304 |
| 2015-09-01 00:00:00 | 11225063 |
| 2015-10-01 00:00:00 | 12315488 |
| 2015-11-01 00:00:00 | 11312676 |
| 2015-12-01 00:00:00 | 11460573 |
+---------------------+----------+

2015 年のイエロー タクシーの平均賃走速度は?

このクエリは、2015 年のイエロー タクシーの 1 日における平均賃走速度を時間ごとに返します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  EXTRACT(HOUR
  FROM
    pickup_datetime) hour,
  ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime,
        pickup_datetime,
        SECOND))*3600, 1) speed
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE
  trip_distance > 0
  AND fare_amount/trip_distance BETWEEN 2
  AND 10
  AND dropoff_datetime > pickup_datetime
GROUP BY
  1
ORDER BY
  1

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  EXTRACT(HOUR
  FROM
    pickup_datetime) hour,
  ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime,
        pickup_datetime,
        SECOND))*3600, 1) speed
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE
  trip_distance > 0
  AND fare_amount/trip_distance BETWEEN 2
  AND 10
  AND dropoff_datetime > pickup_datetime
GROUP BY
  1
ORDER BY
  1'

日中の平均速度はおよそ 11~12 MPH ですが、午前 5 時の平均速度は、ほぼ倍の 21 MPH になっています。

結果を以下に示します。

+------+-------+
| hour | speed |
+------+-------+
|    0 |  15.8 |
|    1 |  16.3 |
|    2 |  16.8 |
|    3 |  17.5 |
|    4 |  20.0 |
|    5 |  21.6 |
|    6 |  17.6 |
|    7 |  13.7 |
|    8 |  11.6 |
|    9 |  11.4 |
|   10 |  11.5 |
|   11 |  11.3 |
|   12 |  11.2 |
|   13 |  11.3 |
|   14 |  11.2 |
|   15 |  11.0 |
|   16 |  11.5 |
|   17 |  11.2 |
|   18 |  11.1 |
|   19 |  11.8 |
|   20 |  12.9 |
|   21 |  13.6 |
|   22 |  14.1 |
|   23 |  14.9 |
+------+-------+

2015 年のイエロー タクシーの曜日ごとの平均賃走速度は?

このクエリは、2015 年の全イエロー タクシーの平均賃走速度を曜日別に表示します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  EXTRACT(DAYOFWEEK
  FROM
    pickup_datetime) DAYOFWEEK,
  ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime,
        pickup_datetime,
        SECOND))*3600, 1) speed
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE
  trip_distance > 0
  AND fare_amount/trip_distance BETWEEN 2
  AND 10
  AND dropoff_datetime > pickup_datetime
GROUP BY
  1
ORDER BY
  1

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  EXTRACT(DAYOFWEEK
  FROM
    pickup_datetime) DAYOFWEEK,
  ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime,
        pickup_datetime,
        SECOND))*3600, 1) speed
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE
  trip_distance > 0
  AND fare_amount/trip_distance BETWEEN 2
  AND 10
  AND dropoff_datetime > pickup_datetime
GROUP BY
  1
ORDER BY
  1'

最も速いのは日曜日で、続いて土曜日、月曜日となっています。月曜日が他の平日よりも速いのはなぜでしょうか?

結果を以下に示します。

+-----------+-------+
| DAYOFWEEK | speed |
+-----------+-------+
|         1 |  14.7 |
|         2 |  13.4 |
|         3 |  12.3 |
|         4 |  12.2 |
|         5 |  12.2 |
|         6 |  12.3 |
|         7 |  13.1 |
+-----------+-------+

データについて

データセット ソース: NYC オープンデータ

カテゴリ: 地理、交通

使用: このデータセットは、データセット ソース(https://data.cityofnewyork.us/)によって規定されている条件の下ですべてのユーザーに公開されています。Google はこのデータセットを「現状のまま」提供し、明示または黙示を問わず、いかなる保証も行いません。Google は、このデータセットの使用で、直接または間接の損害が発生したとしても一切の責任を負いません。

BigQuery で表示: ニューヨーク市のデータセットに移動

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。