【SQL】BigQueryにおけるEXTRACT関数とは?使い方をわかりやすく解説!

今回は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関数を使用して特定の部分を取得したクエリとその結果が下記だ。

EXTRACT関数_0

created_atカラムから、それぞれ日付の特定部分を数値(INT64型)で取得できていることがお分かりいただけると思う。

また、カラムの各値ではなく、下記のように値を直接指定して、日付の特定の部分を取得することも可能である。
※直接日付の値をクエリで指定する際はその前にTIMESTAMPやDATEなどデータ型を指定してあげる必要がある。

上記クエリの結果は「15」となり、"2023-08-22 15:30:00 UTC" の時刻部分(15)が数値として抽出されている。

このようにEXTRACT関数は日時から特定の部分を抽出することができる。

そのため、日時データを分析する際に非常に便利な関数だ。

スポンサーリンク

EXTRACT関数の使い方

EXTRACT関数の使い方は下記の通りだ。

  • 単位:日付のどの単位を抽出したいかを指定
  • 対象カラム:値を抽出したいカラムを指定(日時型のカラムが対象)

EXTRACT関数_0具体例に当てはめて確認してみよう。

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関数で抽出する場合のクエリとその指定の詳細は下記の通りだ。

EXTRACT関数_2

上記クエリの結果は下記となり、created_atの各値の年がそれぞれ抽出されている。

ちなみにBigQueryのEXTRACT関数で指定できる日付の単位は下記の通りだ。

日付だけでなく、曜日の値を1~7で抽出する等も可能だ。

日付の単位説明
MICROSECONDマイクロ秒を抽出
MILLISECONDミリ秒を抽出
SECOND秒を抽出
MINUTE分を抽出
HOUR時を抽出
DAY日を抽出
DAYOFWEEK曜日を抽出(1=月曜日、7=日曜日)
DAYOFYEAR年初からの経過日数(1~365を返す)
WEEK年初からの経過週数(1~53を返す)
MONTH月を抽出
QUARTER四半期
YEAR年を抽出
ISOYEARISO 8601形式の年を抽出
ISOWEEKISO 8601形式の週を抽出
DATE日付を抽出(例:2023-08-31)
DATETIME日時を抽出(例:2023-08-31T15:30:00

このようにEXTRACT関数は対象カラムと抽出したい日付の単位を指定して使うことが出来る。

使用できる単位はEXTRACT関数の対象とする日時を表すデータ型によって変わる。

例えば、DATE型のデータを対象にEXTRACT関数を使用する場合はHOURやMINUTEはそもそも値に時間が含まれていないので使えない。

スポンサーリンク

EXTRACT関数の活用シーン

EXTRACT関数の活用シーンとしては例えば下記が挙げられる。

  • 対象期間の絞り込み
  • 月次売上分析
  • 曜日別のウェブサイト訪問者数分析

例えば、下記sales_dataテーブルのデータにおいて、

「2023年に絞って、月ごとに売上を集計したい」

という場合にEXTRACT関数を活用できる。

order_datesales
2022-05-1518,000
2022-05-2019,000
2022-06-1020,000
2022-06-2521,000
2023-01-1022,000
2023-01-2023,000
2023-02-0524,000
2023-02-2025,000
2023-03-1026,000

クエリとその結果は下記の通りだ。

EXTRACT活用結果

2023年の月ごとの売上が集計できていることがお分かりいただけると思う

上記クエリのようにEXTRACT関数はWHERE句やGROUP BY句でよく活用される。

EXTRACT関数を使用する場合の注意点

BigQueryにおいてEXTRACT関数を使用する場合に下記の点に注意して欲しい。

  • 年月の直接抽出は不可

BigQueryのEXTRACT関数では年月を直接抽出することは出来ない。

PostgreSQLのEXTRACT関数であれば、year_monthを指定することで年月を抽出することはできるが、BigQueryでは出来ない。

もしBigQueryにおいて年月を直接抽出したい場合はFORMAT_DATE関数かFORMAT_TIMESTAMP関数を使用するのがおすすめだ。

まとめ

EXTRACT関数は対象カラムから日付の特定単位の値を抽出する際に使用される関数だ。

EXTRACT 関数は非常に柔軟で強力な関数であり、日付や時刻のデータを扱う際には、様々なシーンで活用できる。

是非使い方をマスターして活用できるようになって欲しい。

おすすめの記事