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

2024年9月16日2024年11月9日

記事の対象者と解消できるお悩み

【対象者】

初中級データアナリスト

【興味関心】

時系列データ分析
イベントログ分析
特定期間のデータ抽出
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)を抽出する」
ペタバイト
- データの単位　ペタバイト＝1000テラバイト＝約100兆行のデータ

概要

データ分析では、イベントが発生した正確な日時を記録した「タイムスタンプデータ」を扱う場面が多くあります。例えば、Webサイトへのアクセス、ECサイトでの購入、センサーデータの記録など、様々なイベントにタイムスタンプが付与されています。Google CloudのBigQueryは、タイムスタンプデータを操作するための豊富なTIMESTAMP関数を備えています。これらの関数を使いこなすことで、時間経過の分析、特定の期間のデータ抽出、タイムスタンプのフォーマット変換など、様々な操作を効率的に行うことができます。

この記事では、BigQueryで利用できる基本的なTIMESTAMP関数を、分かりやすい例とともに紹介します。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

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！

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

記事の対象者と解消できるお悩み

記事作成者

各種用語

概要

目次

1. BigQueryのTIMESTAMP関数とは？

2. BigQueryの基本TIMESTAMP関数一覧

2.1 CURRENT_TIMESTAMP

2.2 EXTRACT

2.3 STRING

2.4 TIMESTAMP

2.5 TIMESTAMP_ADD

2.6 TIMESTAMP_SUB

2.7 TIMESTAMP_DIFF

2.8 TIMESTAMP_TRUNC

2.9 FORMAT_TIMESTAMP

2.10 PARSE_TIMESTAMP

2.11 UNIX_SECONDS

2.12 UNIX_MILLIS

2.13 UNIX_MICROS

2.14 TIMESTAMP_SECONDS

2.15 TIMESTAMP_MILLIS

2.16 TIMESTAMP_MICROS

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

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

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

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

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

5. まとめ：BigQueryのTIMESTAMP関数をマスターして、時間データを自在に分析しよう！

人気記事