notebook

都内でWEB系エンジニアやってます。

BigQueryで特定の規則に沿った文字列から日付を取得する

はてなブログで特に設定せずに記事を書いていると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で添字も出力する

特定の添字の範囲を抜き出す

抜き出したレコードのpartSTRING_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の順に表示したい、特定の箇所に任意の文字列を挟みたいなど

配列を文字列化したり文字列を配列化したりとさまざまな変換方法を試せた

勉強になりました