記事の対象者と解消できるお悩み
【対象者】
初中級データアナリスト
【興味関心】
- 時系列データ分析
- イベントログ分析
- 特定期間のデータ抽出
- SQLスキルアップ
- BigQueryの関数活用
- 業務効率化
【解消できるお悩み】
- Webサイトのアクセスログや顧客の行動履歴を、時間軸で分析したいけど、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兆行のデータ
概要
データ分析では、イベントが発生した正確な日時を記録した「タイムスタンプデータ」を扱う場面が多くあります。例えば、Webサイトへのアクセス、ECサイトでの購入、センサーデータの記録など、様々なイベントにタイムスタンプが付与されています。Google CloudのBigQueryは、タイムスタンプデータを操作するための豊富なTIMESTAMP関数を備えています。これらの関数を使いこなすことで、時間経過の分析、特定の期間のデータ抽出、タイムスタンプのフォーマット変換など、様々な操作を効率的に行うことができます。
この記事では、BigQueryで利用できる基本的なTIMESTAMP関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながらTIMESTAMP関数をマスターしましょう！
目次
- BigQueryのTIMESTAMP関数とは？
- BigQueryの基本TIMESTAMP関数一覧
- BigQuery公開データで実践！TIMESTAMP関数を使った分析例
- FAQ：BigQueryに関するよくある質問
- まとめ：BigQueryのTIMESTAMP関数をマスターして、時間データを自在に分析しよう！
1. BigQueryのTIMESTAMP関数とは？
BigQueryのTIMESTAMP関数は、TIMESTAMP型のデータを操作するための関数です。TIMESTAMP型は、日付と時刻を組み合わせたデータ型で、YYYY-MM-DD HH:MM:SS.fffffffff UTC の形式で表されます。TIMESTAMP関数を活用することで、特定の日時や期間のデータ抽出、タイムスタンプデータの計算、フォーマット変換など、様々な操作を効率的に行うことができます。
2. BigQueryの基本TIMESTAMP関数一覧
BigQueryで利用できる基本的なTIMESTAMP関数を、例とともに紹介します。
2.1 CURRENT_TIMESTAMP
現在の時刻を表す TIMESTAMP オブジェクトを返します。この関数は、引数を取りません。
構文：
CURRENT_TIMESTAMP()
例： 現在のタイムスタンプを取得する
SELECT CURRENT_TIMESTAMP() AS current_timestamp;2.2 EXTRACT
TIMESTAMP 値から、特定の部分（年、月、日、時、分、秒など）を抽出します。
構文：
EXTRACT(part FROM timestamp_expression)
例： 乗車日時から年を抽出する
SELECT EXTRACT(YEAR FROM pickup_datetime) AS pickup_year FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.3 STRING
TIMESTAMP 値を STRING 値に変換します。オプションでタイムゾーンを指定できます。
構文：
STRING(timestamp_expression[, time_zone])
例： 乗車日時を文字列に変換する
SELECT STRING(pickup_datetime) AS pickup_datetime_string FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.4 TIMESTAMP
DATE 値、DATETIME 値、TIME 値、または文字列を TIMESTAMP 値に変換します。オプションでタイムゾーンを指定できます。
構文：
TIMESTAMP(date_expression[, time_zone])
TIMESTAMP(datetime_expression[, time_zone])
TIMESTAMP(time_expression[, date_expression[, time_zone]])
TIMESTAMP(string_expression[, format_string[, time_zone]])
例： ‘2023-12-25’ という日付文字列を TIMESTAMP 値に変換する
SELECT TIMESTAMP('2023-12-25') AS timestamp_value;2.5 TIMESTAMP_ADD
TIMESTAMP 値に、指定された時間間隔を加算します。
構文：
TIMESTAMP_ADD(timestamp_expression, INTERVAL interval_expression date_part)
例： 乗車日時に1時間加算する
SELECT TIMESTAMP_ADD(pickup_datetime, INTERVAL 1 HOUR) AS pickup_datetime_plus_one_hour FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.6 TIMESTAMP_SUB
TIMESTAMP 値から、指定された時間間隔を減算します。
構文：
TIMESTAMP_SUB(timestamp_expression, INTERVAL interval_expression date_part)
例： 乗車日時から1日減算する
SELECT TIMESTAMP_SUB(pickup_datetime, INTERVAL 1 DAY) AS pickup_datetime_minus_one_day FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.7 TIMESTAMP_DIFF
2 つの TIMESTAMP 値の間の時間間隔を、指定された単位で計算します。
構文：
TIMESTAMP_DIFF(timestamp_expression1, timestamp_expression2, date_part)
例： 乗車日時と降車日時の間の時間差を分で計算する
SELECT TIMESTAMP_DIFF(dropoff_datetime, pickup_datetime, MINUTE) AS trip_duration_minutes FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.8 TIMESTAMP_TRUNC
TIMESTAMP 値を、指定された単位で切り捨てます。
構文：
TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])
例： 乗車日時を日にち単位で切り捨てる
SELECT TIMESTAMP_TRUNC(pickup_datetime, DAY) AS pickup_date FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.9 FORMAT_TIMESTAMP
TIMESTAMP 値を、指定された形式の文字列に変換します。オプションでタイムゾーンを指定できます。
構文：
FORMAT_TIMESTAMP(format_string, timestamp_expression[, time_zone])
例： 乗車日時を ‘YYYY-MM-DD HH:mm:ss’ 形式の文字列に変換する
SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', pickup_datetime) AS formatted_pickup_datetime FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.10 PARSE_TIMESTAMP
指定された形式の文字列を TIMESTAMP 値に変換します。オプションでタイムゾーンを指定できます。
構文：
PARSE_TIMESTAMP(format_string, string_expression[, time_zone])
例： ‘2023-12-25 10:30:00 UTC’ という文字列を TIMESTAMP 値に変換する
SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S %Z', '2023-12-25 10:30:00 UTC') AS timestamp_value;2.11 UNIX_SECONDS
TIMESTAMP 値を、1970-01-01 00:00:00 UTC からの経過秒数に変換します。
構文：
UNIX_SECONDS(timestamp_expression)
例： 乗車日時をUNIXタイムスタンプ（秒）に変換する
SELECT UNIX_SECONDS(pickup_datetime) AS pickup_unix_seconds FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.12 UNIX_MILLIS
TIMESTAMP 値を、1970-01-01 00:00:00 UTC からの経過ミリ秒数に変換します。
構文：
UNIX_MILLIS(timestamp_expression)
例： 乗車日時をUNIXタイムスタンプ（ミリ秒）に変換する
SELECT UNIX_MILLIS(pickup_datetime) AS pickup_unix_millis FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.13 UNIX_MICROS
TIMESTAMP 値を、1970-01-01 00:00:00 UTC からの経過マイクロ秒数に変換します。
構文：
UNIX_MICROS(timestamp_expression)
例： 乗車日時をUNIXタイムスタンプ（マイクロ秒）に変換する
SELECT UNIX_MICROS(pickup_datetime) AS pickup_unix_micros FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015;2.14 TIMESTAMP_SECONDS
1970-01-01 00:00:00 UTC からの経過秒数を TIMESTAMP 値に変換します。
構文：
TIMESTAMP_SECONDS(int64_expression)
例： UNIXタイムスタンプ（秒）1672531200 を TIMESTAMP 値に変換する
SELECT TIMESTAMP_SECONDS(1672531200) AS timestamp_value;2.15 TIMESTAMP_MILLIS
1970-01-01 00:00:00 UTC からの経過ミリ秒数を TIMESTAMP 値に変換します。
構文：
TIMESTAMP_MILLIS(int64_expression)
例： UNIXタイムスタンプ（ミリ秒）1672531200000 を TIMESTAMP 値に変換する
SELECT TIMESTAMP_MILLIS(1672531200000) AS timestamp_value;2.16 TIMESTAMP_MICROS
1970-01-01 00:00:00 UTC からの経過マイクロ秒数を TIMESTAMP 値に変換します。
構文：
TIMESTAMP_MICROS(int64_expression)
例： UNIXタイムスタンプ（マイクロ秒）1672531200000000 を TIMESTAMP 値に変換する
SELECT TIMESTAMP_MICROS(1672531200000000) AS timestamp_value;3. BigQuery公開データで実践！TIMESTAMP関数を使った分析例
ここでは、BigQueryの公開データセット「NYC Taxi Trips」を使って、TIMESTAMP関数を使った簡単なSQL分析例を紹介します。このデータセットには、ニューヨーク市のタクシー乗車に関する情報が格納されています。
例：乗車日時が週末のレコードを抽出する
SELECT
*
FROM
bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015
WHERE EXTRACT(DAYOFWEEK FROM pickup_datetime) IN (1, 7);
このクエリでは、`EXTRACT`関数を使って`pickup_datetime`カラムから曜日を抽出し、曜日が日曜日(1)または土曜日(7)のレコードを抽出しています。`WHERE`句で曜日が1または7のレコードを抽出しています。
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のTIMESTAMP関数をマスターして、時間データを自在に分析しよう！
BigQueryのTIMESTAMP関数は、時間データを扱う分析に非常に役立ちます。この記事で紹介した関数以外にも、様々なTIMESTAMP関数が用意されています。BigQueryの公式リファレンスなどを参考に、TIMESTAMP関数をマスターし、時間データを自在に分析できるようになりましょう！
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
