BigQuery入門！無料データでSQLの基本ナビゲーション関数をマスター

2024年9月14日2024年11月9日

記事の対象者と解消できるお悩み

【対象者】

プログラミング未経験のビジネス職

【興味関心】

時系列データ分析
ランキング分析
前後のデータとの比較
SQLスキルアップ
BigQueryの関数活用
業務効率化

【解消できるお悩み】

売上や顧客数の推移を分析したいけど、SQLでどう書けばいいか分からない…
商品や顧客のランキングを作りたい
前の期間と比較して、売上がどれくらい増減したか分析したい
BigQueryで時系列データを効率的に分析する方法を知りたい
データ分析スキルを向上させ、より高度な分析に挑戦したい
データ分析をもっと効率化して、時間を節約したい

記事作成者

【人物】株式会社志庵　代表取締役　佐藤　光　起業6年目

【経歴】株式会社光通信➜光通信同期と起業➜データアナリスト転職➜SaaSで2度目起業

【会社実績】

SaaS 資金調達無しARR2億円

SaaS GMV200億円

【内容】自社AaaSであるStruccle、webスクレイピング、BigQueryに関する記事を作成

各種用語

ARR(Annual Recurring Revenue)
- サブスクリプションサービスの年間売上※当社はSaaS売上
Struccle(ストラクル)
- 株式会社志庵が独自開発しているAaaS
AaaS(アース)
- Data Analytics as a Serviceの意
SaaS(サース)
- Software as a Serviceの意
GoogleCloudPlatform
- Googleが提供するクラウドプラットフォーム。この中に様々なサービスが存在する。
GCP(ジーシーピー)
- GoogleCloudPlatformの略
BigQuery(ビッグクエリ)
- 上記GCPの中に含まれるデータ分析基盤サービス
SQL(エスキューエル)
- データベースを操作するプログラミング言語
クエリ
- SQLで記述した命令文
- クエリ例）
  - SQL構文のクエリ⬇
    - SELECT item_name, price FROM sales_table where price >= 1000;
    - 上記クエリ(命令文)の意味としては「売上テーブル(sales_table)から価格(price)が1000円以上の商品名(item_name)と価格(price)を抽出する」
ペタバイト
- データの単位　ペタバイト＝1000テラバイト＝約100兆行のデータ

概要

階層的なデータや、順序を持つデータを扱う場合、特定のデータの位置や関係性を取得することが重要になります。Google CloudのBigQueryは、そのようなデータ操作を効率的に行うためのナビゲーション関数を提供しています。これらの関数を使いこなすことで、複雑なデータ構造から必要な情報をスムーズに抽出できます。

この記事では、BigQueryで利用できる基本的なナビゲーション関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながらナビゲーション関数をマスターしましょう！

BigQueryのナビゲーション関数とは？
BigQueryの基本ナビゲーション関数一覧
1. FIRST_VALUE
2. LAST_VALUE
3. NTH_VALUE
4. LEAD
5. LAG
6. PERCENTILE_CONT
7. PERCENTILE_DISC
BigQuery公開データで実践！ナビゲーション関数を使った分析例
FAQ：BigQueryに関するよくある質問
まとめ：BigQueryのナビゲーション関数をマスターして、複雑なデータ構造を自在に操ろう！

1. BigQueryのナビゲーション関数とは？

BigQueryのナビゲーション関数は、順序付けられたデータセット内での特定の行の位置に基づいて値を返す関数です。例えば、ウィンドウ関数の中で使用すると、現在の行から見て前の行や後の行の値を取得することができます。これらの関数は、時系列データの分析や、ランキングの計算など、様々な場面で役立ちます。

2. BigQueryの基本ナビゲーション関数一覧

BigQueryで利用できる基本的なナビゲーション関数を、例とともに紹介します。これらの関数は、ウィンドウ関数内で使用されることを前提としています。

2.1 FIRST_VALUE

ウィンドウ内の最初の行の値を返します。

構文：

FIRST_VALUE(expression) OVER (partition_clause order_clause window_clause)

例： taxi_trips テーブルから、各ベンダーID (vendor_id) ごとに、最初に乗車した日付と時刻を取得する

SELECT
    vendor_id,
    pickup_datetime,
    FIRST_VALUE(pickup_datetime) OVER (PARTITION BY vendor_id ORDER BY pickup_datetime ASC) AS first_pickup_datetime
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.2 LAST_VALUE

ウィンドウ内の最後の行の値を返します。

構文：

LAST_VALUE(expression) OVER (partition_clause order_clause window_clause)

例： taxi_trips テーブルから、各ベンダーID (vendor_id) ごとに、最後に乗車した日付と時刻を取得する

SELECT
    vendor_id,
    pickup_datetime,
    LAST_VALUE(pickup_datetime) OVER (PARTITION BY vendor_id ORDER BY pickup_datetime ASC) AS last_pickup_datetime
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.3 NTH_VALUE

ウィンドウ内の指定された順序の行の値を返します。

構文：

NTH_VALUE(expression, n) OVER (partition_clause order_clause window_clause)

例： taxi_trips テーブルから、各ベンダーID (vendor_id) ごとに、3番目に乗車した日付と時刻を取得する

SELECT
    vendor_id,
    pickup_datetime,
    NTH_VALUE(pickup_datetime, 3) OVER (PARTITION BY vendor_id ORDER BY pickup_datetime ASC) AS third_pickup_datetime
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.4 LEAD

現在の行から見て、指定された行数後の行の値を返します。行数を省略した場合は、1行後の値を返します。

構文：

LEAD(expression[, offset[, default_value]]) OVER (partition_clause order_clause window_clause)

例： taxi_trips テーブルから、各ベンダーID (vendor_id) ごとに、現在の乗車から次の乗車までの時間間隔を計算する

SELECT
    vendor_id,
    pickup_datetime,
    DATETIME_DIFF(LEAD(pickup_datetime) OVER (PARTITION BY vendor_id ORDER BY pickup_datetime ASC), pickup_datetime, MINUTE) AS time_diff_minutes
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.5 LAG

現在の行から見て、指定された行数前の行の値を返します。行数を省略した場合は、1行前の値を返します。

構文：

LAG(expression[, offset[, default_value]]) OVER (partition_clause order_clause window_clause)

例： taxi_trips テーブルから、各ベンダーID (vendor_id) ごとに、現在の乗車から前の乗車までの時間間隔を計算する

SELECT
    vendor_id,
    pickup_datetime,
    DATETIME_DIFF(pickup_datetime, LAG(pickup_datetime) OVER (PARTITION BY vendor_id ORDER BY pickup_datetime ASC), MINUTE) AS time_diff_minutes
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.6 PERCENTILE_CONT

指定された百分位数に対応する値を返します。連続的な百分位数を計算します。

構文：

PERCENTILE_CONT(expression, percentile) OVER (partition_clause order_clause)

例： taxi_trips テーブルから、乗車距離 (trip_distance) の中央値を計算する

SELECT
    PERCENTILE_CONT(trip_distance, 0.5) OVER() AS median_trip_distance
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

2.7 PERCENTILE_DISC

指定された百分位数に対応する値を返します。離散的な百分位数を計算します。

構文：

PERCENTILE_DISC(expression, percentile) OVER (partition_clause order_clause)

例： taxi_trips テーブルから、乗車距離 (trip_distance) の第75百分位数を計算する

SELECT
    PERCENTILE_DISC(trip_distance, 0.75) OVER() AS percentile_75_trip_distance
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;

3. BigQuery公開データで実践！ナビゲーション関数を使った分析例

ここでは、BigQueryの公開データセット「NYC Taxi Trips」を使って、ナビゲーション関数を使った簡単なSQL分析例を紹介します。このデータセットには、ニューヨーク市のタクシー乗車に関する情報が格納されています。

例：各タクシーの1日の平均乗車回数を求める

SELECT 
    taxi_trips.vendor_id,
    taxi_trips.pickup_datetime,
    COUNT(*) OVER (PARTITION BY taxi_trips.vendor_id, DATE(taxi_trips.pickup_datetime)) as daily_trip_count 
  FROM 
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015 AS taxi_trips;

このクエリでは、`COUNT(*) OVER (PARTITION BY taxi_trips.vendor_id, DATE(taxi_trips.pickup_datetime))` を使用して、各タクシーの1日の乗車回数を計算しています。`PARTITION BY`句でベンダーIDと乗車日付でデータを分割し、`COUNT(*)`関数で各グループ内の乗車回数を計算しています。

4. FAQ：BigQueryに関するよくある質問

Q1. BigQueryの無料枠を超えて利用すると、どのくらい費用がかかりますか？

A1. 無料枠を超えた場合、従量課金制で料金が発生します。料金は、処理したデータ量やストレージ容量などによって異なります。詳しくは、BigQueryの料金ページをご確認ください。

Q2. BigQueryで使えるSQLは、他のデータベースと同じですか？

A2. BigQueryは標準SQLをサポートしており、他のデータベースで学んだSQLの知識を活かすことができます。ただし、BigQuery独自の関数や構文も存在するため、BigQueryのドキュメントで詳細を確認することをおすすめします。

Q3. SQLの学習には、どのような教材がありますか？

A3. SQLの学習には、オンライン学習サイト、書籍、動画教材など、様々な教材があります。初心者向けの教材から、上級者向けの教材まで、レベルに合わせて選ぶことができます。Googleが提供するBigQueryのチュートリアルもSQLの学習に役立ちます。

5. まとめ：BigQueryのナビゲーション関数をマスターして、複雑なデータ構造を自在に操ろう！

BigQueryのナビゲーション関数は、順序付けられたデータの分析に非常に役立ちます。この記事で紹介した関数は、ウィンドウ関数と組み合わせて使用することで、より複雑な分析が可能になります。BigQueryの公式リファレンスなどを参考に、ナビゲーション関数をマスターし、データ分析スキルを向上させましょう！

BigQuery導入サポート、転職検討中の方はお問い合わせフォームからご相談くださいませ。

誠心誠意精一杯対応いたします。

【参考URL】

GoogleCloudPlatform：https://console.cloud.google.com/welcome/new

BigQuery：https://cloud.google.com/bigquery?hl=ja

BigQueryリリース情報：https://cloud.google.com/bigquery/docs/release-notes

BigQuery料金：https://cloud.google.com/bigquery/pricing

BigQuery料金無料枠：https://cloud.google.com/bigquery/pricing?hl=ja#free-tier

Google Cloud活用事例：https://cloud.google.com/customers/index.html?hl=ja#

Looker Studio：https://cloud.google.com/looker-studio?hl=ja

Looker Studioサンプル：https://cloud.google.com/bigquery/docs/visualize-looker-studio?hl=ja

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！

BigQuery入門！無料データでSQLの基本ナビゲーション関数をマスター

記事の対象者と解消できるお悩み

記事作成者

各種用語

概要

目次

1. BigQueryのナビゲーション関数とは？

2. BigQueryの基本ナビゲーション関数一覧

2.1 FIRST_VALUE

2.2 LAST_VALUE

2.3 NTH_VALUE

2.4 LEAD

2.5 LAG

2.6 PERCENTILE_CONT

2.7 PERCENTILE_DISC

3. BigQuery公開データで実践！ナビゲーション関数を使った分析例

4. FAQ：BigQueryに関するよくある質問

Q1. BigQueryの無料枠を超えて利用すると、どのくらい費用がかかりますか？

Q2. BigQueryで使えるSQLは、他のデータベースと同じですか？

Q3. SQLの学習には、どのような教材がありますか？

5. まとめ：BigQueryのナビゲーション関数をマスターして、複雑なデータ構造を自在に操ろう！

人気記事