データアナリティクス事業本部 インテグレーション部 機械学習チームの貞松です。
今回はSQLのTipsというか、ちょっと込み入った検索を実現するための一例を紹介します。
最終的なSQLクエリを掲載して終わり、でも良いのですが、パッと見少々理解しづらいところもあるので、処理を分解して一つずつ解説します。
実行環境とユースケース
実行環境は以下の通りです。
- Amazon Athena
- Athena engine version 3
- AWSマネジメントコンソールのAthena Query Editor上で実行
ユースケースとしてはBIダッシュボードの検索フォームで入力されたスペース区切りの複数検索キーワードをSQLクエリに渡して、検索結果を返すことを想定しています。
実現したいロジックは以下の通りです。
- スペース区切り(半角でも全角でも可)で複数の検索キーワードの入力を受け付ける
- 入力されたキーワード全てが、検索対象の文字列内に含まれる (AND検索)
- 検索キーワードの出現順序は、入力された順序になっていなくても良い
- 検索キーワードが3つ入力されていたとして、検索対象の文字列内での出現順序が「3番目の検索キーワード」「１番目の検索キーワード」「2番目の検索キーワード」のようになっていても抽出されること
- 検索キーワードが検索対象内で複数回出現しても正しく抽出されること
- 検索キーワード自体が重複していても正しく抽出されること
最終的なSQLクエリ
最終的に作成したSQLクエリは以下の通りです。
実際のユースケースでは <ここにスペース区切りで複数検索キーワードを入れる想定> の部分にBIダッシュボードの検索フォームから渡された文字列が埋め込まれる記述になる想定です。
with search_word AS (
SELECT replace('<ここにスペース区切りで複数検索キーワードを入れる想定>', ' ', ' ') AS search_word
),
split_search_word as (
select
word
from
search_word
cross join
unnest(split(search_word, ' ')) as t(word)
),
search_result as (
select
q10,
case
when count(distinct(word)) filter(where q10 LIKE '%' || word || '%') = (select count(distinct(word)) from split_search_word)
then 0 -- 検索ワードが全てヒットした場合
else 1 -- 検索ワードが一つでもヒットしなかった場合
end as match_result
from
enquete
cross join
split_search_word
group by
q10
)
select
q10
from
search_result
where
match_result = 0
;以下、実行結果の例です。アンケートのフリーテキスト回答欄のデータ列(q10)に対して、検索キーワードとしては「ありがとう 説明 部署 説明」を入力して実行しています(半角・全角スペースを混ぜつつ、キーワード重複もある状態にしました)
検索キーワードの出現順序や出現回数に関わらず、意図通りに抽出できています。
これだけパッと見ても、処理の内容(具体的なロジック)がわかりにくいので、各処理を分解して順番に解説していきます。
各処理の解説
入力された検索キーワードに含まれる全角スペースを半角スペースに変換する処理
これはもうそのままですね。replace関数で全角スペースを半角スペースに変換しています。
これにより、区切り文字のスペースを半角・全角両方に対応させています。
with search_word AS (
SELECT replace('<ここにスペース区切りで複数検索キーワードを入れる想定>', ' ', ' ') AS search_word
),入力された検索キーワードを分割する処理
split関数で半角スペースを区切り文字として、検索キーワードを分割した後、unnestすることで複数検索キーワードを縦持ちしています。
わざわざ縦持ちにしている理由は後述します。
split_search_word as (
select
word
from
search_word
cross join
unnest(split(search_word, ' ')) as t(word)
),前述の実行例のように検索キーワードとして「ありがとう 説明 部署 説明」を入力とした場合は以下のような持ち方になります。
検索対象文字列に各検索キーワードが含まれていた件数をカウントすることで全てのキーワードが含まれているか否かを判定する処理
ここが一番の肝であり、複雑な処理になっています。
search_result as (
select
q10,
case
when count(distinct(word)) filter(where q10 LIKE '%' || word || '%') = (select count(distinct(word)) from split_search_word)
then 0 -- 検索ワードが全てヒットした場合
else 1 -- 検索ワードが一つでもヒットしなかった場合
end as match_result
from
enquete
cross join
split_search_word
group by
q10
)具体的なロジックとしては以下のとおりです。
一つ前の処理で検索キーワードを縦持ちにしたテーブルと検索対象列が含まれるテーブルをCROSS JOINすることで、検索対象文字列×検索キーワードのレコードを持つデータセットになります。
これに対して、以下のよう集計、比較を行うことで、検索対象文字列に検索キーワードが全て含まれるか否かを判定して0 or 1のラベル付けをすることができます。
- filter句を使用することで、検索対象文字列に対して、検索キーワードが含まれているレコードだけをカウント
count(distinct(word)) filter(where q10 LIKE '%' || word || '%')
- 縦持ちにした検索キーワードの件数をカウントする
(select count(distinct(word)) from split_search_word)
- 上記2つのカウントを突き合わせることで、一致すれば全ての検索キーワードが、検索対象文字列のどこかに含まれていることになるので0(検索キーワードが全てヒットした場合のラベル)とし、一致しなければ1(検索キーワードが一つでもヒットしなかった場合のラベル)とする
filter関数を使用することで、条件に一致するレコードだけを対象として集計することができるので、これを活用しているわけです(もちろんcountだけでなく、sumなど他の集計関数でもfilterを使用することができます)
count(distinct(word)) の部分で、 dicstinct(word) しているのは、検索キーワードが重複している場合の対策です。
また、注意事項として、 group by q10 でグループ化のキーとして、検索対象文字列の列のみ指定していますが、検索対象文字列に重複があり一意にならない場合は、カウントの集計が意図しないものになる(重複カウントしてしまう)ので、一意になるような複合キーで指定してください。
検索キーワードが全て含まれる検索対象文字列を抽出する
最後にmatch_resultが0のレコードを抽出して完了です。
select
q10
from
search_result
where
match_result = 0
;最後に
Amazon Athena上で、SQLのFilter句を使用してスペース区切りで入力された複数検索キーワードによる検索を実現する為のSQLクエリを解説しました。 用途が限定的な内容ですが、ニーズはあると思いますので、何処かで誰かの参考になれば幸いです。
13
