BigQuery入門！無料データでSQLの基本日付関数をマスター

2024年9月13日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. CURRENT_DATE
2. EXTRACT
3. DATE
4. DATE_ADD
5. DATE_SUB
6. DATE_DIFF
7. DATE_TRUNC
8. DATE_FROM_UNIX_DATE
9. UNIX_DATE
10. LAST_DAY
11. PARSE_DATE
12. FORMAT_DATE
BigQuery公開データで実践！日付関数を使った分析例
FAQ：BigQueryに関するよくある質問
まとめ：BigQueryの日付関数をマスターして、データ分析スキルを向上させよう！

1. BigQueryの日付関数とは？

BigQueryの日付関数は、日付や時刻データを操作するための関数です。日付の計算、抽出、フォーマット変換など、様々な操作を行うことができます。これらの関数を使いこなすことで、日付や時刻に基づいた分析を効率的に行うことができます。

2. BigQueryの基本日付関数一覧

BigQueryで利用できる基本的な日付関数を、例とともに紹介します。

2.1 CURRENT_DATE

現在の日付を DATE 型の値として返します。タイムゾーンは UTC となります。

構文：

CURRENT_DATE([time_zone])

例：現在の日付(UTC時間)を取得する

SELECT CURRENT_DATE() AS today;

例：現在の日付(JST時間)を取得する

SELECT CURRENT_DATE("Asia/Tokyo") AS today;

2.2 EXTRACT

DATE または TIMESTAMP の値から、特定の部分（年、月、日など）を抽出します。

構文：

EXTRACT(part FROM date_or_timestamp_expression)

例：注文日から年を抽出する

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

2.3 DATE

TIMESTAMP 値から DATE 値を抽出します。

構文：

DATE(timestamp_expression)

例：イベント発生日時から日付を抽出する

SELECT DATE(event_timestamp) AS event_date FROM events;

2.4 DATE_ADD

DATE 値または DATETIME 値に、指定された期間を加算します。

構文：

DATE_ADD(date_or_datetime_expression, INTERVAL interval_expression date_part)

例：現在の日付に7日を加算する

SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY) AS next_week;

2.5 DATE_SUB

DATE 値または DATETIME 値から、指定された期間を減算します。

構文：

DATE_SUB(date_or_datetime_expression, INTERVAL interval_expression date_part)

例：現在の日付から1ヶ月を減算する

SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AS last_month;

2.6 DATE_DIFF

2つの DATE 値または DATETIME 値の間の期間を、指定された単位で計算します。

構文：

DATE_DIFF(date_or_datetime_expression1, date_or_datetime_expression2, date_part)

例：注文日と発送日の間の日数を計算する

SELECT order_id, DATE_DIFF(shipping_date, order_date, DAY) AS days_to_ship FROM orders;

2.7 DATE_TRUNC

DATE 値または DATETIME 値を、指定された単位で切り捨てます。

構文：

DATE_TRUNC(date_or_datetime_expression, date_part)

例：注文日を週の初めに切り捨てる

SELECT DATE_TRUNC(order_date, WEEK) AS week_start FROM orders;

2.8 DATE_FROM_UNIX_DATE

UNIXタイムスタンプ（1970年1月1日からの日数）を DATE 値に変換します。

構文：

DATE_FROM_UNIX_DATE(int64_expression)

例： UNIXタイムスタンプ 18993 を DATE 値に変換する

SELECT DATE_FROM_UNIX_DATE(18993) AS date_value;

2.9 UNIX_DATE

DATE 値を UNIXタイムスタンプ（1970年1月1日からの日数）に変換します。

構文：

UNIX_DATE(date_expression)

例： DATE 値 ‘2023-12-25’ を UNIXタイムスタンプに変換する

SELECT UNIX_DATE(DATE '2023-12-25') AS unix_timestamp;

2.10 LAST_DAY

指定された DATE 値または DATETIME 値を含む月の最終日を返します。

構文：

LAST_DAY(date_or_datetime_expression[, date_part])

例：現在の日付を含む月の最終日を取得する

SELECT LAST_DAY(CURRENT_DATE()) AS last_day_of_month;

2.11 PARSE_DATE

指定された形式の文字列を DATE 値に変換します。変換できない場合はエラーを返します。

構文：

PARSE_DATE(format_string, string_expression)

例：文字列 ‘2023-12-25’ を DATE 値に変換する

SELECT PARSE_DATE('%Y-%m-%d', '2023-12-25') AS date_value;

2.12 FORMAT_DATE

DATE 値を指定された形式の文字列に変換します。

構文：

FORMAT_DATE(format_string, date_expression)

例： DATE 値 ‘2023-12-25’ を ‘YYYY年MM月DD日’ 形式の文字列に変換する

SELECT FORMAT_DATE('%Y年%m月%d日', DATE '2023-12-25') AS formatted_date;

3. BigQuery公開データで実践！日付関数を使った分析例

ここでは、BigQueryの公開データセット「London Bicycle Hires」を使って、日付関数を使った簡単なSQL分析例を紹介します。このデータセットには、ロンドン市内の自転車レンタルに関する情報が格納されています。

例：曜日別のレンタル回数を集計する

SELECT FORMAT_DATE('%A', start_date) AS day_of_week, COUNT(*) AS rental_count
FROM `bigquery-public-data`.london_bicycles.cycle_hire
GROUP BY day_of_week ORDER BY rental_count DESC;

このクエリを実行すると、曜日別のレンタル回数が降順に表示されます。このクエリでは、`FORMAT_DATE`関数を使って`start_date`カラムから曜日を抽出し、`COUNT(*)`関数で曜日別にレンタル回数を集計しています。

下記エラーが出た場合

Access Denied: Table bigquery-public-data:london_bicycles.cycle_hire: User does not have permission to query table bigquery-public-data:london_bicycles.cycle_hire, or perhaps it does not exist.

下記画像のように「処理を行うロケーション：US」となっているため、下記画像の「×」をクリックして、「処理を行うロケーション：US」表示を消してください。

エラーの理由として、cycle_hireのテーブルロケーションがEUで、下記画像ののようにUSロケーションになっているとロケーション違いで上記エラーが出ます。

クエリが成功した場合の結果は下記のようになる。

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

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

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

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

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

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

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 CURRENT_DATE

2.2 EXTRACT

2.3 DATE

2.4 DATE_ADD

2.5 DATE_SUB

2.6 DATE_DIFF

2.7 DATE_TRUNC

2.8 DATE_FROM_UNIX_DATE

2.9 UNIX_DATE

2.10 LAST_DAY

2.11 PARSE_DATE

2.12 FORMAT_DATE

3. BigQuery公開データで実践！日付関数を使った分析例

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

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

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

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

5. まとめ：BigQueryの日付関数をマスターして、データ分析スキルを向上させよう！

人気記事