今回はGoogle BigQueryにおけるEXTRACT関数の意味や使い方について紹介する。
SQLのEXTRACT関数はPostgreSQLやOracleなど様々なリレーショナルデータベース管理システム（RDBMS）で使用することができる。
ただ、使い方が微妙に違う場合があるので、今回はGoogle BigQueryにおけるEXTRACT関数の使い方に絞って解説していきたいと思う。
EXTRACT関数とは？
EXTRACT関数とは、日付や時刻から特定の部分（例：年、月、日、時間など）を抽出するための関数だ。
WHERE句で対象年で絞り込みをしたい場合やGROUP BYで月ごとに集計をする場合などによく使われる。
例えば、下記データのcreated_atカラム（データ型はDATETIME型）の値から年や月を取得したい場合にEXTRACT関数が活用できる。
index | created_at |
|---|---|
1 | 2023-08-17T09:00:00 |
2 | 2023-08-18T10:15:30 |
3 | 2023-08-19T14:45:20 |
4 | 2023-08-20T16:30:45 |
5 | 2023-08-21T18:05:10 |
上記データのcreated_atカラムの各値をEXTRACT関数を使用して特定の部分を取得したクエリとその結果が下記だ。
1 2 3 4 5 6 7 | SELECT created_at ,EXTRACT(YEAR FROM created_at) as year_extract ,EXTRACT(MONTH FROM created_at) as month_extract ,EXTRACT(DAY FROM created_at) as day_extract FROM `sample_table` |
created_atカラムから、それぞれ日付の特定部分を数値（INT64型）で取得できていることがお分かりいただけると思う。
また、カラムの各値ではなく、下記のように値を直接指定して、日付の特定の部分を取得することも可能である。
※直接日付の値をクエリで指定する際はその前にTIMESTAMPやDATEなどデータ型を指定してあげる必要がある。
1 | EXTRACT(HOUR FROM TIMESTAMP "2023-08-22 15:30:00 UTC") |
上記クエリの結果は「15」となり、"2023-08-22 15:30:00 UTC" の時刻部分（15）が数値として抽出されている。
このようにEXTRACT関数は日時から特定の部分を抽出することができる。
そのため、日時データを分析する際に非常に便利な関数だ。
EXTRACT関数の使い方
EXTRACT関数の使い方は下記の通りだ。
- 単位：日付のどの単位を抽出したいかを指定
- 対象カラム：値を抽出したいカラムを指定（日時型のカラムが対象）
具体例に当てはめて確認してみよう。
index | created_at |
|---|---|
1 | 2023-08-17T09:00:00 |
2 | 2023-08-18T10:15:30 |
3 | 2023-08-19T14:45:20 |
4 | 2023-08-20T16:30:45 |
5 | 2023-08-21T18:05:10 |
上記データのcreated_atカラム（DATETIME型）の年をEXTRACT関数で抽出する場合のクエリとその指定の詳細は下記の通りだ。
1 2 3 4 5 | SELECT created_at ,EXTRACT(YEAR FROM created_at) as year_extract FROM `sample_table` |
上記クエリの結果は下記となり、created_atの各値の年がそれぞれ抽出されている。
ちなみにBigQueryのEXTRACT関数で指定できる日付の単位は下記の通りだ。
日付だけでなく、曜日の値を1~7で抽出する等も可能だ。
| 日付の単位 | 説明 |
|---|---|
| MICROSECOND | マイクロ秒を抽出 |
| MILLISECOND | ミリ秒を抽出 |
| SECOND | 秒を抽出 |
| MINUTE | 分を抽出 |
| HOUR | 時を抽出 |
| DAY | 日を抽出 |
| DAYOFWEEK | 曜日を抽出（1=月曜日、7=日曜日） |
| DAYOFYEAR | 年初からの経過日数（1～365を返す） |
| WEEK | 年初からの経過週数（1~53を返す） |
| MONTH | 月を抽出 |
| QUARTER | 四半期 |
| YEAR | 年を抽出 |
| ISOYEAR | ISO 8601形式の年を抽出 |
| ISOWEEK | ISO 8601形式の週を抽出 |
| DATE | 日付を抽出(例：2023-08-31) |
| DATETIME | 日時を抽出（例：2023-08-31T15:30:00） |
このようにEXTRACT関数は対象カラムと抽出したい日付の単位を指定して使うことが出来る。
使用できる単位はEXTRACT関数の対象とする日時を表すデータ型によって変わる。
例えば、DATE型のデータを対象にEXTRACT関数を使用する場合はHOURやMINUTEはそもそも値に時間が含まれていないので使えない。
EXTRACT関数の活用シーン
EXTRACT関数の活用シーンとしては例えば下記が挙げられる。
- 対象期間の絞り込み
- 月次売上分析
- 曜日別のウェブサイト訪問者数分析
例えば、下記sales_dataテーブルのデータにおいて、
「2023年に絞って、月ごとに売上を集計したい」
という場合にEXTRACT関数を活用できる。
| order_date | sales |
|---|---|
| 2022-05-15 | 18,000 |
| 2022-05-20 | 19,000 |
| 2022-06-10 | 20,000 |
| 2022-06-25 | 21,000 |
| 2023-01-10 | 22,000 |
| 2023-01-20 | 23,000 |
| 2023-02-05 | 24,000 |
| 2023-02-20 | 25,000 |
| 2023-03-10 | 26,000 |
クエリとその結果は下記の通りだ。
1 2 3 4 5 6 7 8 | SELECT EXTRACT(MONTH FROM order_date) as month, SUM(sales) as total_sales FROM 'sales_data` WHERE EXTRACT(YEAR FROM order_date) = 2023 GROUP BY month ORDER BY month; |
2023年の月ごとの売上が集計できていることがお分かりいただけると思う
上記クエリのようにEXTRACT関数はWHERE句やGROUP BY句でよく活用される。
EXTRACT関数を使用する場合の注意点
BigQueryにおいてEXTRACT関数を使用する場合に下記の点に注意して欲しい。
- 年月の直接抽出は不可
BigQueryのEXTRACT関数では年月を直接抽出することは出来ない。
PostgreSQLのEXTRACT関数であれば、year_monthを指定することで年月を抽出することはできるが、BigQueryでは出来ない。
もしBigQueryにおいて年月を直接抽出したい場合はFORMAT_DATE関数かFORMAT_TIMESTAMP関数を使用するのがおすすめだ。
まとめ
EXTRACT関数は対象カラムから日付の特定単位の値を抽出する際に使用される関数だ。
EXTRACT 関数は非常に柔軟で強力な関数であり、日付や時刻のデータを扱う際には、様々なシーンで活用できる。
是非使い方をマスターして活用できるようになって欲しい。
BigQueryに関する記事は下記にまとめている。
