今回は、正規表現を使ってURLからパスを取り出すSQLクエリを紹介する。

SQLクエリ概要

URLごとに何かしらの集計を行いたいが、広告のクリックIDなどのパラメータが付与されており、そのままの状態だと粒度が細かくなりすぎてしまうといった問題が発生することがある。

そのような際に、URLをパスに変換することで各ページ単位での集計をシンプルに行うことができる。

具体的には、REGEXP_REPLACE 関数を使って、URLからパラメータの値と先頭からドメインまで（パス以外の部分）を取り除くことで集計を実現できる（※ここでの正規表現の書き方はあくまで一例である）。

REGEXP_REPLACE関数

正規表現 regexp と一致する value のすべての部分文字列を replacement に置き換えた STRING を返します。

（出典）

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions?hl=ja#regexp_replace

SQLクエリサンプル

クエリサンプルは、以下のようになる。

WITH prep AS (
  SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS referrer,
  REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
  '[?].+', '') AS url -- パラメータを取り除く

  FROM
    `technogram-354804.analytics_291973511.events_intraday_2022*`
  WHERE
    event_name = 'page_view'
)

SELECT
  referrer,
  REGEXP_REPLACE(url, '.+jp', '') AS url_path -- パス以外の部分を取り除く
FROM
  prep

BigQueryでの集計結果

集計結果は、以下のようになる。

（最後におまけ）

正規表現のテストは、こちらのサイトが便利なので、参考にされたい。

【正規表現の学習・構築・テストするためのオンラインツール】

https://regexr.com/