この投稿はアイスタイル Advent Calendar 2023 の24日目の記事です。
始めに
初めまして、istyle で日々抽出業務をしている石崎です。
今回、ブログを書くにあたって
こういう関数を前に使用したけどどう書くんだっけ？どんな関数だっけ？
みたいなのが日付関連でちまちま出てきたのをまとめてみたいと思います。
他、使う頻度はそんなに多くないんだけど、頭の端にやりすぎてパッと出てこないテーブル関連や正規表現関連の関数などを数は少ないですが備忘録的な感じでまとめてみました。
日付関連
型変換は毎回のように使用するので空で書けるけど、ちょっと思い出せないこの辺り。
・CURRENT_DATE
・DATE_DIFF
・PARSE_DATE
・PARSE_TIMESTAMP
・GENERATE_DATE_ARRAY
CURRENT_DATE
今日の日付を出す関数。パッと出てこない。
記法：current_date([time_zone])
※基本は’Asia/Tokyo’
DATA_DIFF (差分関数)
日付間の日数を計算する時に使用。
記法：DATA_DIFF(新しい日付, 古い日付, 比較期間)
比較期間：DAY,WEEK,MONTH,YEAR
注意点：日付を逆にしないようにする
PARSE_DATE
日付フォーマットを変換したいとき、しないといけない時に使用。
記法：PARSE_DATE(変換したい日付フォーマット, 日付)
※日付はカラム指定でも良い
日付フォーマットでよく使用するのは下記
YYYYMMDD ： '%Y%m%d'
YYYY-MM-DD ： '%Y-%m-%d’
PARSE_TIMESTAMP
日時フォーマットを変換したいとき、しないといけない時に使用。
記法：PARSE_TIMESTAMP(変換したい日時フォーマット, 日時)
※日時はカラム指定でも良い
日付フォーマットでよく使用するのは下記
YYYY-MM-DD H:M:S： '%Y-%m-%d %X'
※%Xで%H:%M:%S を表現できる
注意点：UTCと表示されていても実情はJSTの場合があるので定義書を要確認
GENERATE_DATE_AGRRAY
カレンダーを作成するときに使用。日次・月次対応できるが、パッとカレンダーを作成したいときに思い出せずいつもググってしまう関数。
記法：GENERATE_DATE_ARRAY(開始日 , 終了日)
※終了日にCURRENT_DATE()を入れると今日までのカレンダーができる
※月次の場合はGENERATE_DATE_ARRAY(開始日 ,終了日,INTERVAL 1 MONTH)
※開始日はDATE_TRUNC等で月変換が必要
テーブル関連
最近は日付別にシャーディングされたテーブルをあまり使用していなかったのでちょっと忘れ気味だったので忘れないように。
・_TABLE_SUFFIX
_TABLE_SUFFIX
シャーディングされたテーブルなど xxxxx_20231130 と後ろに日付が入っているテーブルなどに使用
記法：TABLE_SUFFIX >= 日付 など
日付指定にBETWEEN も使用できる。
使用の際のテーブル側の表記は xxxxx*
正規表現関連
中々覚えられない正規表現関連。そもそも^ とか [0-9] とか色々組み合わせあってお手上げ！といつも思ってしまう。けど、この辺りの関数は覚えておきたい。
・REGEXP_CONTAINS
・REGEXP_EXTRACT
REGEXP_CONTAINS
文字列が正規表現と一致するかを判定
記法：REGEXP_CONTAINS(カラム , r 'URLやその一部')
ex. REGEXP_CONTAINS(dim1, '.test.');
REGEXP_EXTRACT
指定した正規表現に続く文字等を抽出
記法：REGEXP_EXTRACT(カラム , r 'URLやその一部')
正規表現の記号を使用して制御できる（この辺りはまだ勉強中）
ex. REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+')
| 正規表現 | 説明 |
|---|---|
| ^ | 直後の文字列が冒頭にある場合一致 |
| A|B | ABどちらかに一致 |
| + | 直前の文字の1個以上の連続 |
| \w | すべての半角英数字とアンダースコア |
| [a-z],[0-9] | []内のいずれか１文字に一致 |
つまり、[\w-]+ は英数字,アンダースコア( _ ),ハイフン( - ) のいずれかを少なくとも1文字以上繰り返すということ
終わりに
以上、SQLを書く上で使用する関数はたくさんあって覚えるのが大変ですが、使いこなせると効率的な分析やコーディングができるようになるので今後も活用の幅を広げつつ勉強していこうと思います。