記事の対象者と解消できるお悩み
【対象者】
プログラミング未経験のビジネス職
【興味関心】
- ランキング分析
- データのグループ分け
- 順位付け
- SQLスキルアップ
- BigQueryの関数活用
- 業務効率化
【解消できるお悩み】
- 売上ランキングや顧客ランキングを作成したいけど、SQLでどう書けばいいか分からない…
- データを特定の基準でグループ分けして分析したい
- BigQueryでデータを順位付けする方法を知りたい
- SQLスキルを向上させて、より高度な分析に挑戦したい
- データ分析をもっと効率化して、時間を節約したい
記事作成者
【人物】株式会社志庵 代表取締役 佐藤 光 起業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)を抽出する」
- SQL構文のクエリ⬇
- ペタバイト
- データの単位 ペタバイト＝1000テラバイト＝約100兆行のデータ
概要
データ分析では、データに順位をつけたり、グループ内で番号を振ったりする場面がよくあります。Google CloudのBigQueryは、このような操作を簡単に行うための番号付け関数を豊富に提供しています。これらの関数を使いこなすことで、ランキングの作成、データの分割、順序に基づいた分析などが効率的に行えます。
この記事では、BigQueryで利用できる基本的な番号付け関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながら番号付け関数をマスターしましょう！
目次
- BigQueryの番号付け関数とは？
- BigQueryの基本番号付け関数一覧
- BigQuery公開データで実践！番号付け関数を使った分析例
- FAQ：BigQueryに関するよくある質問
- まとめ：BigQueryの番号付け関数をマスターして、データ分析をもっと便利に！
1. BigQueryの番号付け関数とは？
BigQueryの番号付け関数は、結果セットの各行に番号を割り当てる関数です。これらの関数は、主にウィンドウ関数内で使用され、指定したグループ内での行の順位や相対的な位置を計算することができます。番号付け関数は、ランキングの作成、データの分割、順序に基づいた分析など、様々な場面で役立ちます。
2. BigQueryの基本番号付け関数一覧
BigQueryで利用できる基本的な番号付け関数を、例とともに紹介します。これらの関数は、ウィンドウ関数内で使用されることを前提としています。
2.1 ROW_NUMBER
ウィンドウ内の各行に、一意の連続した番号を割り当てます。番号は、ウィンドウ内の行の順序に基づいて、1から始まります。
構文：
ROW_NUMBER() OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車日時順に各行に連番を振る
SELECT
ROW_NUMBER() OVER (ORDER BY pickup_datetime) AS row_num,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
2.2 RANK
ウィンドウ内の各行に、順位を割り当てます。順位は、ウィンドウ内の行の順序に基づいて、1から始まります。同じ値を持つ行には同じ順位が割り当てられ、次の順位は、その行数分スキップされます。
構文：
RANK() OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車距離 (trip_distance) の長い順に順位を付ける
SELECT
RANK() OVER (ORDER BY trip_distance DESC) AS trip_rank,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
2.3 DENSE_RANK
ウィンドウ内の各行に、順位を割り当てます。順位は、ウィンドウ内の行の順序に基づいて、1から始まります。同じ値を持つ行には同じ順位が割り当てられますが、次の順位はスキップされません。
構文：
DENSE_RANK() OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車距離 (trip_distance) の長い順に、隙間なく順位を付ける
SELECT
DENSE_RANK() OVER (ORDER BY trip_distance DESC) AS trip_dense_rank,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
2.4 PERCENT_RANK
ウィンドウ内の各行に、百分位順位を割り当てます。百分位順位は、0から1の範囲の値で、ウィンドウ内の行の相対的な位置を表します。
構文：
PERCENT_RANK() OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車距離 (trip_distance) の百分位順位を計算する
SELECT
PERCENT_RANK() OVER (ORDER BY trip_distance DESC) AS trip_percentile_rank,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
2.5 CUME_DIST
ウィンドウ内の各行に、累積分布を割り当てます。累積分布は、0から1の範囲の値で、ウィンドウ内の現在の行以下の行の割合を表します。
構文：
CUME_DIST() OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車距離 (trip_distance) の累積分布を計算する
SELECT
CUME_DIST() OVER (ORDER BY trip_distance DESC) AS trip_cume_dist,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
2.6 NTILE
ウィンドウ内の各行を、指定された数のバケットに分割し、各行にバケット番号を割り当てます。バケット番号は、1から始まります。
構文：
NTILE(n) OVER (partition_clause order_clause)
例： taxi_trips テーブルから、乗車距離 (trip_distance) に基づいて、データを4つのバケットに分割する
SELECT
NTILE(4) OVER (ORDER BY trip_distance DESC) AS trip_ntile,
vendor_id,
pickup_datetime,
trip_distance
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;
3. BigQuery公開データで実践！番号付け関数を使った分析例
ここでは、BigQueryの公開データセット「NYC Taxi Trips」を使って、番号付け関数を使った簡単なSQL分析例を紹介します。このデータセットには、ニューヨーク市のタクシー乗車に関する情報が格納されています。
例：各ベンダーの乗車距離ランキングTOP3を取得する
SELECT
vendor_id,
pickup_datetime,
trip_distance,
RANK() OVER (PARTITION BY vendor_id ORDER BY trip_distance DESC) AS trip_rank
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015
qualify trip_rank <= 3;このクエリでは、`RANK()`関数を使って、各ベンダー(`vendor_id`)内で乗車距離(`trip_distance`)の長い順に順位を付け、`trip_rank`という列に格納しています。そして、`QUALIFY`句で`trip_rank`が3以下の行、つまり各ベンダーの乗車距離ランキングTOP3を抽出しています。
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
