読者です 読者をやめる 読者になる 読者になる

歩いたら休め

If the implementation is easy to explain, it may be a good idea.

【MySQL】DBに入れた検索クエリテーブルから、キーワードの順序に関係なく抽出する

MySQLのデータベースに検索クエリが保存されており、その中から検索クエリを取得したいとします。

具体的には「検索連動広告にガンガン新規キーワードの組み合わせを登録したい」「葉隠れ構造を崩したくないから、同じキーワードの組み合わせを別のキャンペーンに登録したくないからチェックしたい」という要件がありました。

日本語で説明すると、「指定のキーワードをどちらも含んでおり、スペースの数が1つ(キーワードが2つ)のものを抽出する」という処理を走らせています。

こちらのクエリで、「アニメ 配信」または「配信 アニメ」のクエリ(keyword_text)を抽出することができます。

SELECT
  keyword_text,
  LENGTH(keyword_text) - LENGTH(REPLACE(keyword_text, ' ', '')) AS spaces
FROM
  keyword_master_table
HAVING
  keyword_text REGEXP '(^| )(\\+?)アニメ($| )' AND
  keyword_text REGEXP '(^| )(\\+?)配信($| )' AND
  spaces = 1;

正規表現のでAND条件を指定することもできますが、MySQL正規表現が先読みの機能に対応していないらしく、利用できませんでした。(正規表現の先読み・後読みの機能が良く分かってないので勉強しなきゃ…)

qiita.com

(\\+?)として+の文字がが先頭に付いている場合も含めているのは、絞り込み部分一致の場合、対象の文字列の先頭に+がつくからです。

また、WHEREではなくHAVINGを使っているのは、本来はもう少しクエリの組み合わせをORで絞り込んでおり、そのたびにLENGTH(keyword_text) - LENGTH(REPLACE(keyword_text, ' ', ''))と書くのが面倒だったためです。

MySQLであれば、カラムの型を文字列でなくSET(集合)型で入れておけば、自然で、もう少し高速なクエリになっていた気がします。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型

ちなみにGoogleBigQueryでは、SPLITという関数が用意されており、文字列を分割して列に展開するようなこともできます。その際のキーはJOINする際のキーには使えませんが、FLATTENでカラムを指定することで結合キーとして使えるようになるはずです。

SELECT
  master.keyword_text AS keyword_text,
  master.one_keyword
FROM
  (
    FLATTEN(
      (
        SELECT
          keyword_text,
          SPLIT(keyword_text, ' ') AS one_keyword
        FROM
          [project.keyword_master_table]
      ),
      one_keyword
    ) AS master
INNER JOIN
  (
    SELECT
      one_keyword
    FROM
      [project.new_keywords] -- 新しく登録したいキーワードテーブル
  ) new_keyword
ON
  master.one_keyword = new_keyword.one_keyword

ここから、NULLを含まないkeyword_textを抽出すればイケるはずですが、眠くなってきたし、手元にBigQueryが無くて確認できないので寝ます…。また、もっとエレガントな解法があるような気もしています。