記事の対象者と解消できるお悩み
【対象者】
プログラミング未経験のビジネス職
【興味関心】
- 時刻や時間帯を考慮したデータ分析
- 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)を抽出する」
- SQL構文のクエリ⬇
- ペタバイト
- データの単位 ペタバイト＝1000テラバイト＝約100兆行のデータ
概要
データ分析において、日付と時刻を組み合わせた「日時データ」を扱うことは非常に重要です。Webサイトのアクセスログ、ECサイトの購買履歴、センサーデータなど、多くのデータに日時情報が含まれています。Google CloudのBigQueryは、日時データを操作するための豊富な日時関数を備えています。これらの関数を使いこなすことで、日時データに基づいた詳細な分析を効率的に行うことができます。
この記事では、BigQueryで利用できる基本的な日時関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながら日時関数をマスターしましょう！
目次
- BigQueryの日時関数とは？
- BigQueryの基本日時関数一覧
- BigQuery公開データで実践！日時関数を使った分析例
- FAQ：BigQueryに関するよくある質問
- まとめ：BigQueryの日時関数をマスターして、データ分析スキルを向上させよう！
1. BigQueryの日時関数とは？
BigQueryの日時関数は、DATETIME型のデータを操作するための関数です。DATETIME型は、日付と時刻を組み合わせたデータ型で、YYYY-MM-DD HH:MM:SS の形式で表されます。日時関数を活用することで、特定の日時や期間のデータ抽出、日時データの計算、フォーマット変換など、様々な操作を効率的に行うことができます。
2. BigQueryの基本日時関数一覧
BigQueryで利用できる基本的な日時関数を、例とともに紹介します。
2.1 CURRENT_DATETIME
現在の時刻を表す DATETIME を返します。オプションでタイムゾーンを指定できます。指定しない場合、デフォルトのタイムゾーンは UTC です。
構文：
CURRENT_DATETIME([time_zone])
例： 現在の時刻をUTCで取得する
SELECT CURRENT_DATETIME() AS current_datetime_utc;
例： 現在の時刻を’Asia/Tokyo’のタイムゾーンで取得する
SELECT CURRENT_DATETIME('Asia/Tokyo') AS current_datetime_tokyo;
2.2 DATETIME
DATE型とTIME型、または個々の数値を組み合わせてDATETIME型を作成します。
構文：
DATETIME(date_expression, time_expression)
DATETIME(year, month, day, hour, minute, second)
例： DATE値とTIME値からDATETIME値を作成する
SELECT DATETIME(DATE '2023-12-25', TIME '10:30:00') AS datetime_value;
例： 個々の数値からDATETIME値を作成する
SELECT DATETIME(2023, 12, 25, 10, 30, 00) AS datetime_value;
2.3 EXTRACT
DATE、TIME、DATETIME、またはTIMESTAMPの値から、特定の部分（年、月、日、時、分、秒など）を抽出します。
構文：
EXTRACT(part FROM date_or_time_expression)
例： イベント発生日時から時間を抽出する
SELECT EXTRACT(HOUR FROM event_datetime) AS event_hour FROM events;
2.4 DATETIME_ADD
DATETIME値に、指定された期間を加算します。
構文：
DATETIME_ADD(datetime_expression, INTERVAL interval_expression date_part)
例： 現在時刻に3時間加算する
SELECT DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 3 HOUR) AS three_hours_later;
2.5 DATETIME_SUB
DATETIME値から、指定された期間を減算します。
構文：
DATETIME_SUB(datetime_expression, INTERVAL interval_expression date_part)
例： 現在時刻から1日減算する
SELECT DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY) AS yesterday;
2.6 DATETIME_DIFF
2つのDATETIME値の間の期間を、指定された単位で計算します。
構文：
DATETIME_DIFF(datetime_expression1, datetime_expression2, date_part)
例： イベント開始日時と終了日時の間の時間数を計算する
SELECT event_id, DATETIME_DIFF(end_datetime, start_datetime, HOUR) AS event_duration_hours FROM events;
2.7 DATETIME_TRUNC
DATETIME値を、指定された単位で切り捨てます。
構文：
DATETIME_TRUNC(datetime_expression, date_part)
例： イベント発生日時を時間の頭に切り捨てる
SELECT DATETIME_TRUNC(event_datetime, HOUR) AS event_hour_start FROM events;
2.8 FORMAT_DATETIME
DATETIME値を、指定された形式の文字列に変換します。
構文：
FORMAT_DATETIME(format_string, datetime_expression)
例： DATETIME値を ‘YYYY-MM-DD HH:mm:ss’ 形式の文字列に変換する
SELECT FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', DATETIME '2023-12-25 10:30:00') AS formatted_datetime;
2.9 PARSE_DATETIME
指定された形式の文字列を DATETIME 値に変換します。変換できない場合はエラーを返します。
構文：
PARSE_DATETIME(format_string, string_expression)
例： 文字列 ‘2023-12-25 10:30:00’ を DATETIME 値に変換する
SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-12-25 10:30:00') AS datetime_value;
3. BigQuery公開データで実践！日時関数を使った分析例
ここでは、BigQueryの公開データセット「London Bicycle Hires」を使って、日時関数を使った簡単なSQL分析例を紹介します。このデータセットには、ロンドン市内の自転車レンタルに関する情報が格納されています。
例：時間帯別のレンタル回数を集計する
SELECT EXTRACT(HOUR FROM start_date) AS rental_hour, COUNT(*) AS rental_count
FROM `bigquery-public-data`.london_bicycles.cycle_hire
GROUP BY rental_hour ORDER BY rental_count DESC;このクエリを実行すると、時間帯別のレンタル回数が降順に表示されます。このクエリでは、`EXTRACT`関数を使って`start_datetime`カラムから時間を抽出し、`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
