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の正規表現が先読みの機能に対応していないらしく、利用できませんでした。(正規表現の先読み・後読みの機能が良く分かってないので勉強しなきゃ…)
(\\+?)
として+の文字がが先頭に付いている場合も含めているのは、絞り込み部分一致の場合、対象の文字列の先頭に+がつくからです。
また、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が無くて確認できないので寝ます…。また、もっとエレガントな解法があるような気もしています。