Amazon OpenSearch Service （ ElasticSearch ）のクエリで、排他的に抽出対象を絞り込む方法 - 株式会社CINC 開発本部 エンジニアブログ

株式会社CINC 開発本部 エンジニアブログ

ビッグデータ取得・自然言語処理・人工知能（AI）開発を軸に、マーケティングソリューションの開発や、DX推進を行っている、株式会社CINCの開発本部です。

Amazon OpenSearch Service （ ElasticSearch ）のクエリで、排他的に抽出対象を絞り込む方法

Amazon OpenSearch Service

どうしてこの記事を書こうと思ったのか

こんにちは、株式会社CINC開発本部です。

最近よく業務で Amazon OpenSearch Service （ ElasticSearch ） を利用しています。

今回は、SQLのサブクエリのような絞り込みが bucket_selector を使って実現できました。

Amazon OpenSearch Service （ ElasticSearch ） について基本的なクエリに関する情報は、公式ドキュメントはもちろん、いくつかのサイトでも見つけられましたが、今回のような高度な使い方に関しては、具体的な情報があまり見当たりませんでした。

そこで、自分が試行錯誤しながら辿り着いた解決策を備忘録として残したいと思いました。 この情報が同じように悩んでいる方々の参考になればと思います。

概要

特定の条件で絞り込んだ対象について、その中でも「グループAとそれ以外」という様な排他的な集計を行うクエリを書く際に、RDBであればサブクエリを利用します。
Amazon OpenSearch Service （ ElasticSearch ） では、現時点でサブクエリを実現する機能が提供されていないのですが、今回 bucket_selector を使うことで相当のデータ取得を行いましたので、その実装方法を記載します。

事例

次のような構造のインデックスにデータが入っている時

  • キーワードID（kw_id）
  • ドメイン（host）
  • 検索Vol（sv）

対象インデックスに対して

という条件で抽出を行いたい場合に

RDBであればサブクエリを利用する事で、それぞれの条件での抽出を行う事が可能です。
それに対して、 Amazon OpenSearch Service （ ElasticSearch ） でどの様に抽出したかをまとめていきます。

RDBでの実現方法

下記の様なクエリで抽出を行っています。

SELECT
    t02.host ,
    COUNT(t02.cnt_own) AS cnt_own ,
    sum(t02.sv_own) AS sv_own
FROM
    (
    SELECT
      host,
      kw_id ,
      max(cnt_own) AS cnt_own ,
      max(sv_own) AS sv_own
    FROM
      (
        SELECT
          host,
          kw_id,
          1 AS cnt_own,
          sv AS sv_own
        FROM
          data_table t01
        WHERE
          host = :host_own
          AND kw_id NOT IN (
            SELECT DISTINCT
              kw_id
            FROM
              data_table
            WHERE 
              host = :host_competitor_1
          )
      )
    GROUP BY
      host,
      kw_id
  ) t02
GROUP BY
  t02.host

サブクエリが入れ子になっていますが、自社が持つキーワードの中から、競合のホストの情報を持つkw_idをNOT IN で除外する事で、自社のみが持つキーワードを対象としています。

その後 host, kw_idでGROUP BYして自社のみが持つキーワードについての集計を行っています。
(競合についての集計を行う際には1, svとしている部分については、CASE文を利用して集計用のデータを作ることになります。)

OpenSearchでの実現方法

下記の様なクエリで対応しました。
(JSONにコメントは付けられないので、実行時には // の行は削除が必要です。)

{
  "size": 0,
  "from": 0,
  "_source": false,
  "query": {
    "bool": {
      "filter": [
        {
           // ① 全体の絞り込み
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "match": {
                  "host": "test1.com"
                }
              },
              {
                "match": {
                  "host": "test2.com"
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "kw_id_terms": {
      "aggs": {
         // ② キーワード単位でのグルーピング
        "terms":{
          "field": "kw_id",
          "size": 100
        },
        // ③ 自社の絞り込み
        "own_filter": {
          "filter": {
            "match": {
              "host": "test1.com"
            }
          }
        },
        // ③ 競合の絞り込み
        "competitors_filter": {
          "filter": {
            "bool": {
              "minimum_should_match": 1,
              "should": [
                {
                  "match": {
                    "host": "test2.com"
                  }
                }
              ]
            }
          }
        },
        // ④ キーワードの絞り込み
        "keyword_bucket_selector": {
          "bucket_selector": {
            "buckets_path": {
              "competitors": "competitors_filter>_count",
              "own": "own_filter>_count"
            },
            "script": "params.own > 0 && params.competitors == 0"
          }
        },
        "sv_max": {
          "max": {
            "field": "sv"
          }
        }

      }
    },
    "sv_sum_bucket": {
      "sum_bucket": {
        "buckets_path": "kw_id_terms>sv_max"
      }
    }
  }
}

以下で①〜④のポイントについて詳細に解説します。

①全体の絞り込み

"bool": {
      "filter": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "match": {
                  "host": "test1.com"
                }
              },
              {
                "match": {
                  "host": "test2.com"
                }
              }
            ]
          }
        }
      ]
    }

filterの中でshouldを利用しtest1.com と test2.comのいずれかに一致している結果を対象としています。
minimum_should_matchに1を設定する事でいずれか一つ以上にヒットしているもののみが対象となります。
(minimum_should_matchはデフォルトでは0なので、指定しない場合に他のfilter条件があると、それにマッチしているだけで検索に引っかかってしまうためです。)

②キーワード単位でのグルーピング

        "terms":{
          "field": "kw_id",
          "size": 100
        },

termsでkw_id毎にバケットを作成しています。
(なお、例ではsize:100としているが、結果キーワード数が十分入るsizeにしておく必要があります。場合によってはパーティションを設定して複数回に分けての取得が必要です。)

③自社・競合の絞り込み

        "own_filter": {
          "filter": {
            "match": {
              "host": "test1.com"
            }
          }
        },
        "competitors_filter": {
          "filter": {
            "bool": {
              "minimum_should_match": 1,
              "should": [
                {
                  "match": {
                    "host": "test2.com"
                  }
                }
              ]
            }
          }
        },

termsのバケット内に更に

  • 自社ドメインのみを抽出するフィルタ（own_filter）
  • 競合ドメインのみを抽出するフィルタ（competitors_filter）

を用意しています。

競合用の filter では 複数の競合を指定する事 もあるため should を利用しています。

④キーワードの絞り込み

        "keyword_bucket_selector": {
          "bucket_selector": {
            "buckets_path": {
              "competitors": "competitors_filter>_count",
              "own": "own_filter>_count"
            },
            "script": "params.own > 0 && params.competitors == 0"
          }
        },

bucket_selectorを利用することで 指定した条件に合致するバケットのみ を対象とする事が可能です。

例では「 自社ドメインを含まない（競合ドメインのみ） のフィルタのドキュメント数が0で、且つ 自社ドメインのみのフィルタのドキュメント数 が0より大きいバケット」を対象としています。
これにより termsでは、自社のみドキュメントがあるキーワードだけがバケットに含まれる形になり、 sv_sum_bucket では、条件を満たしたバケットの sv_max の合計を集計しているため 自社のみが含まれるキーワード のSV合計が算出できます。

参考

Bucket aggregations - OpenSearch Documentation