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

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

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


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

```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`）に変換する方法を紹介します。
```sql
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句で条件を指定するとします。
```sql
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分以降のデータを抽出する条件を指定しています。

### 日本時間の早い順に並べ替え
```sql
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`列の昇順（早い時間から遅い時間へ）に並べています。

### 日本時間の遅い順に並べ替え
```sql
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`関数は、タイムスタンプに指定された間隔を加算または減算するために使用されます。この関数の構文は以下の通りです。
```sql
TIMESTAMP_ADD(timestamp_expression, INTERVAL interval_expression unit)
```
- `timestamp_expression`: タイムスタンプ値を指定します。
- `interval_expression`: 加算または減算する間隔の量を指定します。
- `unit`: 間隔の単位を指定します（例：`SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR`）。

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

```sql
TIMESTAMP_ADD(time, INTERVAL 9 HOUR)
```
これにより、`time`列の値に9時間を加算し、日本時間を取得することができます。

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

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

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

```sql
EXTRACT(HOUR FROM TIMESTAMP_ADD(time, INTERVAL 9 HOUR))
```
これにより、日本時間に変換されたタイムスタンプから、時間を抽出することができます。

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

https://cloud.google.com/bigquery/docs/introduction?hl=ja

https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ja#timestamp_add

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#extract