記事の対象者と解消できるお悩み
【対象者】
プログラミング未経験のビジネス職
【興味関心】
- データ分析の効率化
- 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 ARRAY
サブクエリから配列を作成します。
構文：
ARRAY(subquery)
例： 各部門の従業員名を配列として取得する
SELECT department, ARRAY(SELECT employee_name FROM employees WHERE department = t1.department) AS employee_names FROM departments AS t1;
2.2 ARRAY_CONCAT
複数の配列を連結して、1つの配列を作成します。
構文：
ARRAY_CONCAT(array_expression[, ...])
例： 2つの配列を連結する
SELECT ARRAY_CONCAT(['apple', 'banana'], ['orange', 'grape']) AS fruits;
2.3 ARRAY_LENGTH
配列の要素数を返します。
構文：
ARRAY_LENGTH(array_expression)
例： fruits配列の要素数を取得する
SELECT ARRAY_LENGTH(['apple', 'banana', 'orange']) AS number_of_fruits;
2.4 ARRAY_TO_STRING
配列の要素を連結して、1つの文字列を作成します。区切り文字を指定することもできます。
構文：
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
例： fruits配列の要素をカンマ区切りで連結する
SELECT ARRAY_TO_STRING(['apple', 'banana', 'orange'], ', ') AS fruit_string;
2.5 GENERATE_ARRAY
開始値、終了値、ステップ値を指定して、数値の配列を作成します。
構文：
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
例： 1から10までの数値の配列を作成する
SELECT GENERATE_ARRAY(1, 10) AS numbers;
2.6 GENERATE_DATE_ARRAY
開始日、終了日、ステップ値を指定して、日付の配列を作成します。
構文：
GENERATE_DATE_ARRAY(start_date, end_date[, step_interval])
例： 2023年1月1日から2023年12月31日までの日付の配列を作成する
SELECT GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31') AS dates;
2.7 GENERATE_TIMESTAMP_ARRAY
開始日時、終了日時、ステップ値を指定して、タイムスタンプの配列を作成します。
構文：
GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp[, step_interval])
例： 2023年1月1日0時から2023年1月1日23時までのタイムスタンプの配列を作成する
SELECT GENERATE_TIMESTAMP_ARRAY('2023-01-01 00:00:00', '2023-01-01 23:00:00', INTERVAL 1 HOUR) AS timestamps;
2.8 OFFSET
配列内の指定された位置にある要素を返します。位置は0から始まります。
構文：
OFFSET(array_expression, offset)
例： fruits配列の2番目の要素を取得する
SELECT OFFSET(['apple', 'banana', 'orange'], 1) AS second_fruit;
2.9 ORDINAL
配列内の指定された順序にある要素を返します。順序は1から始まります。
構文：
ORDINAL(array_expression, ordinal)
例： fruits配列の3番目の要素を取得する
SELECT ORDINAL(['apple', 'banana', 'orange'], 3) AS third_fruit;
2.10 SAFE_OFFSET
配列内の指定された位置にある要素を返します。位置は0から始まります。指定された位置に要素が存在しない場合は、NULLを返します。
構文：
SAFE_OFFSET(array_expression, offset)
例： fruits配列の4番目の要素を取得する（要素が存在しないためNULLが返る）
SELECT SAFE_OFFSET(['apple', 'banana', 'orange'], 3) AS fourth_fruit;
2.11 SAFE_ORDINAL
配列内の指定された順序にある要素を返します。順序は1から始まります。指定された順序に要素が存在しない場合は、NULLを返します。
構文：
SAFE_ORDINAL(array_expression, ordinal)
例： fruits配列の5番目の要素を取得する（要素が存在しないためNULLが返る）
SELECT SAFE_ORDINAL(['apple', 'banana', 'orange'], 5) AS fifth_fruit;
3. BigQuery公開データで実践！配列関数を使った分析例
ここでは、BigQueryの公開データセット「GitHub」を使って、配列関数を使った簡単なSQL分析例を紹介します。このデータセットには、GitHub上のリポジトリやユーザーに関する情報が格納されています。
例：各リポジトリで使用されている利用プログラミング言語数を多い順TOP10
SELECT repo_name, ARRAY_LENGTH(language) AS num_languages
FROM bigquery-public-data.github_repos.languages
order by ARRAY_LENGTH(language) desc
limit 10このクエリを実行すると、各リポジトリで使用されているプログラミング言語の数が表示されます。このクエリでは、配列関数 `ARRAY_LENGTH` を使用して、`languages` カラム（配列型）の要素数を取得しています。
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
