🌊

BigQuery の REGEXP_EXTRACT を使ってURLをバラしてみようぜ【Standard Query】

1 min read

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract

これを使ってみます。

状況設定

売る用の商品とレンタル用の商品を取り扱っているWebサイトがあり、アクセスされたURLを記録しています。URLのパスに Slug などの情報が入っているので、それをばらしてカラムにしたい、という状況です。

例:Products テーブル

id URL
1 https://shop.example.com/waddy/products/power-chaege-buttery
2 https://shop.example.com/kitakyu-man/rentals/hiyoko-senbei

これを以下のようにバラします。

id shop_name product_slug rental_slug
1 waddy power-chaege-buttery null
2 kitakyu-man null hiyoko-senbei

SQL

ポイント:

  • 一時関数を使う
  • REGEXP_EXTRACT を使う
  • REGEXP_EXTRACT はキャプチャグループ()をひとつしか指定できない。OR条件|を使うときは非キャプチャグループ(?:)を使う
  -- 正規表現でURLを展開する一時関数を用意する
CREATE TEMPORARY FUNCTION
  EXTRACT_PRODUCT_SLUG(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/.+?/products/(\w+).*$') );
CREATE TEMPORARY FUNCTION
  EXTRACT_RENTAL_SLUG(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/.+?/rentals/(\w+).*$') );
CREATE TEMPORARY FUNCTION
  EXTRACT_SHOP_NAME(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/(.+?)/(?:products|rentals)/\w+.*$') );

  -- データは WITH句 で用意してしまいます
WITH
  Products AS (
  SELECT
    1 AS id,
    "https://shop.example.com/waddy/products/power-chaege-buttery" AS url
  UNION ALL
  SELECT
    2,
    "https://shop.example.com/kitakyu-man/rentals/hiyoko-senbei" )
SELECT
  id,
  EXTRACT_SHOP_NAME(url) AS shop_name,
  EXTRACT_PRODUCT_SLUG(url) AS product_slug,
  EXTRACT_RENTAL_SLUG(url) AS rental_slug
FROM
  Products

意図どおりの展開ができました。データの一部を取り出して、別のカラムにバラしたいときなどに使えそうです。

Discussion

ログインするとコメントできます