はてなブログで特に設定せずに記事を書いているとURLが次のような形式になる
/entry/年/月/日/時分秒
この規則的な文字列から日付の情報を取り出すというのをBigQueryのSQLで行う
具体的な例としてはURLのパス/entry/2022/07/22/120000
から2022-07-22
を取り出す
今回は3パターン試してみた
パターン1
愚直にSPLITして特定の日付箇所の添字を指定してCONCATする
WITH sample AS ( SELECT '/entry/2019/07/07/035341' AS pagepath UNION ALL SELECT '/entry/2021/12/07/000000' AS pagepath ) SELECT pagepath, DATE(CONCAT( SPLIT(pagepath,'/')[SAFE_OFFSET(2)], "-", SPLIT(pagepath,'/')[SAFE_OFFSET(3)], "-", SPLIT(pagepath,'/')[SAFE_OFFSET(4)])) AS published_at FROM sample
- 結果
pagepath | published_at |
---|---|
/entry/2019/07/07/035341 | 2019-07-07 |
/entry/2021/12/07/000000 | 2021-12-07 |
パターン2
サブクエリを用いて、配列に分解後対象範囲を抜き出し再度配列化し文字列に変換する
SPLIT
で/
区切りの配列化、UNNESTでフラットにしつつWITH OFFSET
で添字も出力する
特定の添字の範囲を抜き出す
ここまでをサブクエリで行う
ARRAY
で配列化、ARRAY_TO_STRING
で-
を区切り文字として文字列化する
WITH sample AS ( SELECT '/entry/2019/07/07/035341' AS pagepath UNION ALL SELECT '/entry/2021/12/07/000000' AS pagepath ) SELECT pagepath, DATE(ARRAY_TO_STRING(ARRAY(( SELECT part FROM UNNEST(SPLIT(pagepath, '/')) part WITH OFFSET index WHERE index BETWEEN 2 AND 4 ORDER BY pagepath,index)), '-')) AS published_at FROM sample
- 結果
pagepath | published_at |
---|---|
/entry/2019/07/07/035341 | 2019-07-07 |
/entry/2021/12/07/000000 | 2021-12-07 |
配列のスライス
- 参考
How can I get a slice of an array in BigQuery Standard SQL? - Stack Overflow
こういう時、コードで書くならsplitして対象範囲を抜き出してjoinしたいというような場面である
配列のスライスをSQLできないかと調べたらこの方法に行き着いた
SPLIT+UNNESTした状態を見てみる
理解のために途中経過のSQLを載せる
WITH OFFSET
で添字を参照できる
WHERE句で2〜4を指定することで今回求めている部分の範囲を取得する
WHERE句で指定できるので対象が飛び飛びだったとしてもIN
句で指定すれば問題ない(順番もバラバラだとちょっと工夫が必要)
WITH sample AS ( SELECT '/entry/2019/07/07/035341' AS pagepath UNION ALL SELECT '/entry/2021/12/07/000000' AS pagepath ) SELECT pagepath, part, index FROM sample, UNNEST(SPLIT(pagepath, '/')) part WITH OFFSET index WHERE index BETWEEN 2 AND 4
- 結果
pagepath | part | index |
---|---|---|
/entry/2019/07/07/035341 | 2019 | 2 |
/entry/2019/07/07/035341 | 07 | 3 |
/entry/2019/07/07/035341 | 07 | 4 |
/entry/2021/12/07/000000 | 2021 | 2 |
/entry/2021/12/07/000000 | 12 | 3 |
/entry/2021/12/07/000000 | 07 | 4 |
後はpart
だけ抜き出して配列化、文字列化してつなげる
パターン3
サブクエリを用いて、配列に分解後対象範囲を抜き出し文字列化
SPLIT
で文字列を/
区切りの配列化、UNNESTでフラットにしつつWITH OFFSET
で添字も出力する
特定の添字の範囲を抜き出す
抜き出したレコードのpart
をSTRING_AGG
を用いて-
区切りでつなげる
つなげる際の順序はindex
の順に並べてつなげる
WITH sample AS ( SELECT '/entry/2019/07/07/035341' AS pagepath UNION ALL SELECT '/entry/2021/12/07/000000' AS pagepath ) SELECT pagepath, DATE(( SELECT STRING_AGG(part, '-' ORDER BY index) FROM UNNEST(SPLIT(pagepath, '/')) part WITH OFFSET index WHERE index BETWEEN 2 AND 4 )) published_at FROM sample
- 結果
pagepath | published_at |
---|---|
/entry/2019/07/07/035341 | 2019-07-07 |
/entry/2021/12/07/000000 | 2021-12-07 |
サブクエリ
蛇足になるがサブクエリについて
変換した日付文字列をDATE型にして日付計算しようとすると次のように怒られた
Each function argument is an expression, not a query; to use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression at [5:5]
書いてあるとおりだが、スカラサブクエリ式にしてからにしてねとのこと
()
で囲いスカラサブクエリ式にしてからDATE
に渡す
DATEの()
と合わせて二重に囲っているのはスカラサブクエリ式に変換しているから
WITH sample AS ( SELECT '/entry/2019/07/07/035341' AS pagepath UNION ALL SELECT '/entry/2021/12/07/000000' AS pagepath ) SELECT pagepath, DATE_ADD(DATE(( SELECT STRING_AGG(part, '-' ORDER BY index) FROM UNNEST(SPLIT(pagepath, '/')) part WITH OFFSET index WHERE index BETWEEN 2 AND 4 )), INTERVAL 1 DAY) published_after_1day FROM sample
- 結果
pagepath | published_after_1day |
---|---|
/entry/2019/07/07/035341 | 2019-07-08 |
/entry/2021/12/07/000000 | 2021-12-08 |
DATE型に変換しさらに日付計算まで行ってみた
まとめ
- はてなブログのURLパターンから日付の情報を抜き出した
- SPLITで配列に分解後添字指定+CONCATでつなげるパターン
- 配列に分解後対象範囲を抜き出し再度配列化、特定文字列でつなげるパターン
- 配列に分解後対象範囲を抜き出し特定文字列でつなげるパターン
色々試してみたが結局パターン3を使うことにした
ただ、抽出対象が順番ばらばらな場合はサブクエリが結構膨れそうなのでパターン1にすると思う
たとえば、3,2,4の順に表示したい、特定の箇所に任意の文字列を挟みたいなど
配列を文字列化したり文字列を配列化したりとさまざまな変換方法を試せた
勉強になりました