BigQueryで日本時間の特定の時間帯のデータを抽出・並べ替える方法

はじめに

Google BigQueryは、大規模なデータセットを短時間で解析できる、高速でスケーラブルなデータウェアハウスです。BigQueryでは、SQL構文を使ってデータをクエリし、データ分析や可視化を行うことができます。BigQueryでは、タイムスタンプ型（TIMESTAMP）の値はUTC（協定世界時）で保存されていることがデフォルトです。そのため、特定のタイムゾーンに合わせたデータの抽出や並べ替えを行いたい場合は、適切なタイムゾーンへの変換を行う必要があります。

日本時間を考慮した特定の時間帯のデータを抽出したり、日本時間の早い/遅い順に並べ替えたりする方法を考えます。まず、BigQueryの基本的な使い方を簡単に説明し、その後、サンプルテーブル sample_table の time 列を使って、日本時間の特定の時間帯のデータを抽出・並べ替える方法を説明します。TIMESTAMP_ADD関数とEXTRACT関数を組み合わせて使用することで、日本時間を考慮したデータの抽出や並べ替えが可能になります。これにより、日本向けのデータ解析やレポーティングが容易になります。

BigQueryの基本的な使い方

BigQueryでは、データをプロジェクト内のデータセットに格納し、データセット内にテーブルを作成してデータを管理します。クエリは、以下のような基本的なSQL構文を使って記述します。

SELECT
  column1,
  column2,
  ...
FROM
  `project_id.dataset_id.table_id`
WHERE
  condition1
  AND condition2
  ...
ORDER BY
  column
LIMIT
  number

サンプルテーブル

以下のようなテーブル構造を持つ sample_table を使用します。

Column Name	Type
message_type	INT64
custom_id1	STRING
custom_id2	STRING
status	STRING
time	TIMESTAMP

日本時間に変換

まず、UTC時間を日本時間（JST）に変換する方法を紹介します。

SELECT
  message_type,
  custom_id1,
  custom_id2,
  status,
  TIMESTAMP_ADD(time, INTERVAL 9 HOUR) AS jst_timestamp
FROM
  `your_project_id.your_dataset.sample_table`

このクエリでは、time 列に9時間を追加してUTCから日本時間 (JST) に変換し、その結果を jst_timestamp として取得しています。

日本時間の特定の時間帯のデータを抽出

例えば、日本時間の15時30分以降のデータを抽出するには、WHERE句で条件を指定するとします。

SELECT
  message_type,
  custom_id1,
  custom_id2,
  status,
  TIMESTAMP_ADD(time, INTERVAL 9 HOUR) AS jst_timestamp
FROM
  `your_project_id.your_dataset.sample_table`
WHERE
  message_type >= 2
  AND DATE(time) = "2023-04-27"
AND (
EXTRACT(HOUR FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) = 15
AND EXTRACT(MINUTE FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) >= 30
)

このクエリでは、WHERE句で日本時間の15時30分以降のデータを抽出する条件を指定しています。

日本時間の早い順に並べ替え

SELECT
  message_type,
  custom_id1,
  custom_id2,
  status,
  TIMESTAMP_ADD(time, INTERVAL 9 HOUR) AS jst_timestamp
FROM
  `your_project_id.your_dataset.sample_table`
WHERE
  message_type >= 2
  AND DATE(time) = "2023-04-27"
  AND (
    EXTRACT(HOUR FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) = 15
    AND EXTRACT(MINUTE FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) >= 30
  )
ORDER BY
  jst_timestamp

このクエリでは、ORDER BY句を使用して、結果をjst_timestamp列の昇順（早い時間から遅い時間へ）に並べています。

日本時間の遅い順に並べ替え

SELECT
  message_type,
  custom_id1,
  custom_id2,
  status,
  TIMESTAMP_ADD(time, INTERVAL 9 HOUR) AS jst_timestamp
FROM
  `your_project_id.your_dataset.sample_table`
WHERE
  message_type >= 2
  AND DATE(time) = "2023-04-27"
  AND (
    EXTRACT(HOUR FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) = 15
    AND EXTRACT(MINUTE FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR)) >= 30
  )
ORDER BY
  jst_timestamp DESC

このクエリでは、ORDER BY句を使用して、結果をjst_timestamp列の降順（遅い時間から早い時間へ）に並べています。DESCキーワードを追加することで、ソートが降順になります。

TIMESTAMP_ADD関数について

TIMESTAMP_ADD関数は、タイムスタンプに指定された間隔を加算または減算するために使用されます。この関数の構文は以下の通りです。

TIMESTAMP_ADD(timestamp_expression, INTERVAL interval_expression unit)

• timestamp_expression: タイムスタンプ値を指定します。
• interval_expression: 加算または減算する間隔の量を指定します。
• unit: 間隔の単位を指定します（例：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR）。

例えば、UTC時間を日本時間（JST）に変換するために、以下のようにTIMESTAMP_ADD関数を使用します。

TIMESTAMP_ADD(time, INTERVAL 9 HOUR)

これにより、time列の値に9時間を加算し、日本時間を取得することができます。

EXTRACT関数

EXTRACT関数は、タイムスタンプや日付から特定の要素（年、月、日、時、分、秒など）を抽出するために使用されます。この関数の構文は以下の通りです。

EXTRACT(unit FROM timestamp_expression)

• unit: 抽出する要素を指定します（例：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND）。
• timestamp_expression: タイムスタンプ値を指定します。

例えば、タイムスタンプから時間を抽出するために、以下のようにEXTRACT関数を使用します。

EXTRACT(HOUR FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR))

これにより、日本時間に変換されたタイムスタンプから、時間を抽出することができます。

まとめ

この記事では、BigQueryの基本的な使い方と、日本時間の特定の時間帯のデータを抽出・並べ替える方法を解説しました。TIMESTAMP_ADD関数とEXTRACT関数を組み合わせて使用することで、日本時間を考慮したデータの抽出や並べ替えが可能になります。これにより、日本向けのデータ解析やレポーティングが容易になります。