こんにちは！データサイエンティストの青木和也（https://twitter.com/kaizen_oni）です！
今回の記事では、BigQueryのparse_dateが文字列→日付型変換まで対応してくれるのかをまとめてみました！
「この文字列、日付に変換するにはかなり難しそうだけど大丈夫かな、、、」という人はぜひ参考にしてみてください！
parse_dateとは？
parse_dateとはBigQuery上で使用できる関数で、文字列を日付型に変換してくれる便利機能です。
SELECT parse_date('%y/%m/%d', '2012/01/01')
上記のように文字列を日付型に変換してくれるので、BigQuery以外のデータソースからBigQueryにデータを読み込む際に、元の日付風文字列を文字列にしてデータベース内にインサートすることが可能になります！
parse_dateの変換の方法
parse_dateの変換の方法は至ってシンプルで、第2引数に日付型に変換したい文字列を、第1引数に変換したい文字列を%y（=年）、%m（=月）、%d（=日）や記号などで表すとどのように表現できるかを教えてあげることで、parse_dateが文字列がどのような日付であるかを理解して日付型に変換してくれます。
上記の例で言えば、変換したい文字列「01/01/18」が「月/日/年」という構成だよ！ということを、第1引数に「%m/%d/%y」を入れてあげることによってparse_dateに伝えていることがわかります。
なお、「2024」などの4桁の年を読み取って欲しい場合は%Y、「24」を「2024」と解釈して欲しい場合は%yを指定しましょう。
parse_dateで一通り変換してみた結果
それでは、parse_dateはどこまで日付を理解して日付型に変換してくれるのか実験してみましょう。
なお、全てのパターンについてBigQueryの環境上で検証を行っていますが、出力結果についてはGoogle Slidesで作成した擬似出力画面である点についてはご了承ください。
今回実験するのは以下の5つのパターンです。
- 20240214（整数）
- 2024年2月14日
- 2024-02-14
- 14/02/24
- 14.02.2024
20240214の場合（整数）
日付が整数で表されている場合はparse_dateはどのように解釈してくれるでしょうか。
実際に実行をしてみると、以下のようになります
「PARSE_DATEの引数はどっちもSTRING型で指定してくれ」と言われてしまいましたね。
それでは、上記のように整数（INT）を文字列（STRING）に変換してから PARSE_DATEに渡してみましょう。
今度は正しく変換されました！
上記のように、整数をDATE型に変換したい場合は、CASTでSTRING型に変換してからPARSE_DATEに渡してあげましょう。
2024年2月14日（日本/中国表記）
それでは日本/中国表記の年月日がparse_dateに対応できるのか確認してみましょう。
問題なく変換してくれました。
2024-02-14（カナダ表記）
次にカナダ表記を検証してみましょう。
こちらも問題なく変換できています。
ちなみに、元の文字列が「2024-2-14」のように1桁の月日の前に0がついていなくても上記のように作用してくれます。
14/02/24（イギリス/フランス表記）
イギリス/フランス表記も検証してみましょう。
なお、今回の場合は年が短縮されて「24」と記載されているので、「%y」で変換する文字列を指定するという点に注意をしてPARSE_DATEを実行してみましょう。
なお、この状況で誤って「%Y」と指定すると以下のように「0024年」として解釈されて結果が出てしまうので気をつけてください。
14.02.2024（ドイツ表記）
最後にドイツ表記を検証してみましょう。
こちらについても問題なく変換することができました。
まとめ
今回の記事では日付っぽい文字列や整数をparse_dateで変換できるのかの検証をしてみました。
結論から言うと、parse_dateで適切に元の文字列、整数の年月日の場所を指定することができれば、どんな文字列でも整数でも変換できることが分かりました。
また、年については元の文字列で2桁で記載されているのか4桁で記載されているのかによって、「%y」と「%Y」を使い分けるということも検証しました。
SAPなどのグローバルなシステムだと日付が海外の文字列型で登録されている場合もあるので、そのような時はparse_date関数を使ってDATE型に変換しておくと、分析の際に便利かと思います！
ぜひご活用ください！
コメント