パイプ構文を使用してデータを分析する

このチュートリアルでは、パイプ構文を使用してデータを分析するクエリを作成する方法について説明します。

パイプ構文は、クエリの読み取り、書き込み、メンテナンスが容易になるように設計されたクエリの線形構造をサポートする GoogleSQL の拡張機能です。 パイプ構文は、パイプ記号 |>、パイプ演算子名、引数で構成されます。詳しくは、次のリソースをご覧ください。

• パイプ構文の概要については、パイプクエリの構文を使用するをご覧ください。
• 構文の詳細については、パイプクエリの構文のリファレンス ドキュメントをご覧ください。

このチュートリアルでは、自転車のレンタル時間に関するデータを含む一般公開の bigquery-public-data.austin_bikeshare.bikeshare_trips テーブルを使用して、パイプ構文で複雑なクエリを作成します。

目標

• FROM 句でクエリを開始して、テーブルデータを表示します。
• EXTEND パイプ演算子を使用して列を追加します。
• AGGREGATE パイプ演算子を使用して、日単位と週単位でデータを集計します。
• CROSS JOIN パイプ演算子を使用して、スライディング ウィンドウでデータを集計します。
• WHERE パイプ演算子を使用してデータをィルタリングします。
• 複数レベルの集計を実行するときに、パイプ構文の線形クエリ構造と標準構文のネストされたクエリ構造を比較します。

始める前に

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

1. In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.

   Go to project selector

2. Verify that billing is enabled for your Trusted Cloud project.

3. 新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にするには:

   Enable the BigQuery API.

   Enable the API

クエリを実行するさまざまな方法の詳細については、クエリを実行するをご覧ください。

テーブルデータを表示する

bikeshare_trips テーブルからすべてのデータを取得するには、次のクエリを実行します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

標準構文

SELECT *
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

パイプ構文では、SELECT 句なしで FROM 句で始まるクエリで、テーブルの結果を返すことが可能です。

次のような結果になります。

+----------+-----------------+---------+-----------+-------------------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

列を追加する

bikeshare_trips テーブルの start_time 列はタイムスタンプですが、旅行の日付のみを表示する列を追加することもできます。パイプ構文で列を追加するには、EXTEND パイプ演算子を使用します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date;

標準構文

SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

次のような結果になります。

+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | date       | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | 2023-02-12 | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | 2023-01-13 | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | 2023-04-20 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

日次データを集計する

日付別にグループ化すると、1 日あたりのルート数と自転車数の合計を確認できます。

• AGGREGATE パイプ演算子と COUNT 関数を使用して、ルート数と使用された自転車の総数を取得します。

• GROUP BY 句を使用して、結果を日付でグループ化します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date;

標準構文

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date;

次のような結果になります。

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2023-04-20 | 841   | 197            |
| 2023-01-27 | 763   | 148            |
| 2023-06-12 | 562   | 202            |
| ...        | ...   | ...            |
+------------+-------+----------------+

結果を並べ替える

結果を date 列で降順に並べ替えるには、GROUP BY 句に DESC 接尾辞を追加します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date DESC;

標準構文

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

次のような結果になります。

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-29 | 395   | 123            |
| 2024-06-28 | 437   | 137            |
| ...        | ...   | ...            |
+------------+-------+----------------+

パイプ構文では、ORDER BY パイプ演算子を使用せずに、並べ替え接尾辞を GROUP BY 句に直接追加できます。GROUP BY 句に接尾辞を追加することは、パイプ構文がサポートするいくつかのオプションの AGGREGATE を使用した短縮形順序付け機能の 1 つです。標準構文ではこれは不可能です。並べ替えには ORDER BY 句を使用する必要があります。

週単位のデータを集計する

1 日あたりの使用された自転車の数に関するデータが得られたので、クエリを構築して、7 日間の期間ごとに使用された個別の自転車の数を確認できます。

日単位ではなく週単位で表示するようにテーブル内の行を更新するには、GROUP BY 句で DATE_TRUNC 関数を使用して、粒度を WEEK に設定します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
    COUNT(*) AS trips,
    COUNT(DISTINCT bike_id) AS distinct_bikes,
GROUP BY DATE_TRUNC(date, WEEK) AS date DESC;

標準構文

SELECT
  DATE_TRUNC(CAST(start_time AS DATE), WEEK) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes,
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

次のような結果になります。

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-23 | 3206  | 213            |
| 2024-06-16 | 3441  | 212            |
| ...        | ...   | ...            |
+------------+-------+----------------+

スライディング ウィンドウで集計する

前のセクションの結果は、開始日と終了日の間にある固定ウィンドウ内のルート（2024-06-23～2024-06-29 など）を示しています。代わりに、7 日間のスライディング ウィンドウ内のルートを表示するとします。このウィンドウは、新しい日が来るたびに時間が前方に移動します。つまり、翌週の特定の日付について、ルート数と使用された自転車数を調べます。

データにスライディング ウィンドウを適用するには、まず各ルートを開始日からのアクティブな 6 日間にコピーします。次に、DATE_ADD 関数を使用して、アクティブな日の日付を計算します。最後に、アクティブな日ごとにルート ID と自転車 ID を集計します。

1. データを前方にコピーするには、GENERATE_ARRAY 関数とクロス結合を使用します。

   パイプ構文

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
   

   標準構文

   SELECT *, CAST(start_time AS DATE) AS date
   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
   

   GENERATE_ARRAY 関数は、7 つの要素（0～6）を含む配列を作成します。CROSS JOIN UNNEST 演算では、各行の 7 つのコピーが作成され、新しい diff_days 列に、各行の 0～6 の配列要素値のいずれかが含まれます。diff_days 値を元の日付に対する調整として使用して、ウィンドウをその日数分（元の日付から最大 7 日間）前方にスライドできます。

2. ルートに対して計算されたアクティブな日数を確認するには、EXTEND パイプ演算子と DATE_ADD 関数を使用して、開始日と diff_days 列内の値を含む active_date という列を作成します。

   パイプ構文

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
   |> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date;
   

   標準構文

   SELECT *, DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
   FROM (
     SELECT *, CAST(start_time AS DATE) AS date
     FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
     CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
   

   たとえば、2024-05-20 に開始するルートは、2024-05-26 までの各日もアクティブと見なされます。

3. 最後に、ルート ID と自転車 ID を集計し、active_date でグループ化します。

   パイプ構文

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
   |> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
   |> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
               COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;
   

   標準構文

   SELECT
     DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
     COUNT(DISTINCT bike_id) AS active_7d_bikes,
     COUNT(trip_id) AS active_7d_trips
   FROM (
     SELECT *, CAST(start_time AS DATE) AS date
     FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
     CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
   GROUP BY active_date
   ORDER BY active_date DESC;
   

   次のような結果になります。

   +-------------+-----------------+-----------------+
   | active_date | active_7d_bikes | active_7d_trips |
   +-------------+-----------------+-----------------+
   | 2024-07-06  | 90              | 331             |
   | 2024-07-05  | 142             | 726             |
   | 2024-07-04  | 186             | 1163            |
   | ...         | ...             | ...             |
   +-------------+-----------------+-----------------+
   

将来の日付をフィルタリングする

上のクエリでは、日付はデータの最終日より最大 6 日後まで延長されます。データの終了日を超える日付を除外するには、クエリで最大日付を設定します。

1. OVER 句を含むウィンドウ関数を使用する別の EXTEND パイプ演算子を追加して、テーブル内の最大日付を計算します。
2. WHERE パイプ演算子を使用して、最大日付を超える生成された行を除外します。

パイプ構文

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> EXTEND MAX(date) OVER () AS max_date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> WHERE active_date <= max_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
             COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;

標準構文

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM(
  SELECT *
  FROM (
    SELECT *,
      DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
      MAX(date) OVER () AS max_date
    FROM(
      SELECT *, CAST(start_time AS DATE) AS date,
      FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
      CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days))
  WHERE active_date <= max_date)
GROUP BY active_date
ORDER BY active_date DESC;

次のような結果になります。

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-06-30  | 212             | 3031            |
| 2024-06-29  | 213             | 3206            |
| 2024-06-28  | 219             | 3476            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

次のステップ

• パイプ構文の動作の詳細については、パイプクエリの構文を使用するをご覧ください。
• 技術的な詳細については、パイプクエリの構文のリファレンス ドキュメントをご覧ください。