【BigQuery】UDFと文字列関数を使ってGETパラメータを切り出す #データ分析

のページパスから「111」「222」「333」を切り出す

結論

CREATE TEMPORARY FUNCTION get_value(keyword STRING, pagepath STRING)
AS (
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      pagepath, 
      CONCAT(r'.*(\?|&)', keyword, '='), 
      ''), -- 前半文字列を削除
    r'&.*', 
    '') -- 後半文字列を削除
  END
);

SELECT
  get_value('hoge', pagepath) AS hoge_value,
  get_value('fuga', pagepath) AS fuga_value,
  get_value('moge', pagepath) AS moge_value
FROM
  `project_name.dataset_name.table_name`

処理の流れ

①REGEXP_REPLACE関数で値の前半部分を削除
②REGEXP_REPLACE関数で値の後半部分を削除
③UDF(ユーザー定義関数)で①と②を関数化し、SELECT句で呼び出し

REGEXP_REPLACE関数

公式リファレンス

CONCAT関数

公式リファレンス

REGEXP_REPLACE(
  置換元文字列, 
  CONCAT(r'正規表現', ユーザー定義関数の引数), 
  置換先文字列
)

とすることで、正規表現にユーザー定義関数の引数を結合させることが可能

UDF(ユーザー定義関数)

CREATE TEMPORARY FUNCTION 関数名(第一引数名 データ型, 第二引数名 データ型, ...)
AS (
  処理
);