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

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

【対象者】

初中級データアナリスト

【興味関心】

1. 時間帯別分析
2. 時間データの計算
3. SQLスキルアップ
4. BigQueryの関数活用
5. 業務効率化
6. 顧客行動分析

【解消できるお悩み】

1. 時間帯別の売上やアクセス数を分析したいけど、SQLの書き方がわからない…
2. イベント発生時間の間隔を計算したい
3. BigQueryで時間データを効率的に扱う方法を知りたい
4. データ分析をもっと効率化して、時間を節約したい
5. 顧客の行動を時間軸で分析して、サービス改善に活かしたい

記事作成者

【人物】株式会社志庵　代表取締役　佐藤　光　起業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は、時間データを操作するための豊富なTIME関数を備えています。これらの関数を使いこなすことで、時間データの計算、抽出、フォーマット変換など、様々な操作を効率的に行うことができます。

この記事では、BigQueryで利用できる基本的なTIME関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながらTIME関数をマスターしましょう！

目次

1. BigQueryのTIME関数とは？
2. BigQueryの基本TIME関数一覧
   1. CURRENT_TIME
   2. EXTRACT
   3. TIME
   4. TIME_ADD
   5. TIME_SUB
   6. TIME_DIFF
   7. TIME_TRUNC
   8. FORMAT_TIME
   9. PARSE_TIME
3. BigQuery公開データで実践！TIME関数を使った分析例
4. FAQ：BigQueryに関するよくある質問
5. まとめ：BigQueryのTIME関数をマスターして、時間データを自在に分析しよう！

1. BigQueryのTIME関数とは？

BigQueryのTIME関数は、TIME型のデータを操作するための関数です。TIME型は、時刻を表すデータ型で、HH:MM:SS の形式で表されます。TIME関数を活用することで、特定の時間帯のデータ抽出、時間データの計算、フォーマット変換など、様々な操作を効率的に行うことができます。

2. BigQueryの基本TIME関数一覧

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

2.1 CURRENT_TIME

現在の時刻を TIME 値として返します。オプションでタイムゾーンを指定できます。指定しない場合、デフォルトのタイムゾーンは UTC です。

構文：

CURRENT_TIME([time_zone])

例： 現在の時刻をUTCで取得する

SELECT CURRENT_TIME() AS current_time_utc;

例： 現在の時刻を’Asia/Tokyo’のタイムゾーンで取得する

SELECT CURRENT_TIME('Asia/Tokyo') AS current_time_tokyo;

2.2 EXTRACT

TIME 値から、特定の部分（HOUR、MINUTE、SECOND）を抽出します。

構文：

EXTRACT(part FROM time_expression)

例： ’10:30:00′ から時間を抽出する

SELECT EXTRACT(HOUR FROM TIME '10:30:00') AS hour;

2.3 TIME

数値から TIME 値を作成します。

構文：

TIME(hour, minute, second)

例： 10時30分00秒の TIME 値を作成する

SELECT TIME(10, 30, 00) AS time_value;

2.4 TIME_ADD

TIME 値に、指定された時間間隔を加算します。

構文：

TIME_ADD(time_expression, INTERVAL interval_expression time_part)

例： ’10:30:00′ に 2 時間を加算する

SELECT TIME_ADD(TIME '10:30:00', INTERVAL 2 HOUR) AS time_added;

2.5 TIME_SUB

TIME 値から、指定された時間間隔を減算します。

構文：

TIME_SUB(time_expression, INTERVAL interval_expression time_part)

例： ’10:30:00′ から 1 時間を減算する

SELECT TIME_SUB(TIME '10:30:00', INTERVAL 1 HOUR) AS time_subtracted;

2.6 TIME_DIFF

2 つの TIME 値の間の時間間隔を、指定された単位で計算します。

構文：

TIME_DIFF(time_expression1, time_expression2, time_part)

例： ’12:00:00′ と ’10:30:00′ の間の時間差を分(MINUTE)で計算する

SELECT TIME_DIFF(TIME '12:00:00', TIME '10:30:00', MINUTE) AS time_difference_minutes;

2.7 TIME_TRUNC

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

構文：

TIME_TRUNC(time_expression, time_part)

例： ’10:30:45′ を分に切り捨てる

SELECT TIME_TRUNC(TIME '10:30:45', MINUTE) AS truncated_time;

2.8 FORMAT_TIME

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

構文：

FORMAT_TIME(format_string, time_expression)

例： ’10:30:00′ を ‘HH時mm分ss秒’ 形式の文字列に変換する

SELECT FORMAT_TIME('%H時%M分%S秒', TIME '10:30:00') AS formatted_time;

2.9 PARSE_TIME

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

構文：

PARSE_TIME(format_string, string_expression)

例： 文字列 ’10:30:00′ を TIME 値に変換する

SELECT PARSE_TIME('%H:%M:%S', '10:30:00') AS time_value;

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

ここでは、BigQueryの公開データセット「NYC Taxi Trips」を使って、TIME関数を使った簡単なSQL分析例を紹介します。このデータセットには、ニューヨーク市のタクシー乗車に関する情報が格納されています。

例：乗車時刻の分布を調べる

SELECT
    FORMAT_TIME('%H', CAST(pickup_datetime AS TIME)) AS pickup_hour,
    COUNT(*) AS trip_count
  FROM
    `bigquery-public-data`.new_york_taxi_trips.tlc_yellow_trips_2015
  GROUP BY
    pickup_hour
  ORDER BY
    pickup_hour;

このクエリでは、`FORMAT_TIME`関数を使って`pickup_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のTIME関数をマスターして、時間データを自在に分析しよう！

BigQueryのTIME関数は、時間データを扱う分析に非常に役立ちます。この記事で紹介した関数以外にも、様々なTIME関数が用意されています。BigQueryの公式リファレンスなどを参考に、TIME関数をマスターし、時間データを自在に分析できるようになりましょう！

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