LoginSignup
5
7

More than 3 years have passed since last update.

@yurucamp(深津大好きおじさん)

ElasticSearchの集約クエリに関して（応用？編）

Last updated at Posted at 2019-05-31

集約クエリ(Aggregations)

はじめに

ElasticSearchの集約クエリに関して（基礎編） で、集約の方法を記述したが、今回はもう少し難易度の高い集約クエリの説明をする。1

使用するデータはここで定義した、商品売り上げログ。

Pipeline Aggregations

ざっくりいうと、集約結果(Aggregations )をさらに集約(Aggregations )可能になるクエリのこと。
以下の4つがある。

クエリ 説明
max_bucket 集約結果の最大値をとる
min_bucket 集約結果の最小値をとる
avg_bucket 集約結果の平均値をとる
sum_bucket 集約結果の合計値をとる

今回は、商品ごとに売り上げの合計を取得するクエリを実行し、その結果に対して、
最大値、最小値、平均値、合計値を取得する方法を紹介。

リクエスト(商品ごとに売り上げの合計を取得するクエリ)
{
  "size": 0,
  "aggs": {
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得するクエリ)
{
  "took": 570,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          }
        }
      ]
    }
  }
}

max_bucket

集約結果の中で最大の値を求める。

max_bucket
"max_bucket": {
   "buckets_path": "{{対象バケット名}}>{{対象metrics名}}",
}

例：max_bucket(商品ごとに売り上げの合計を取得し、最大値を求めるクエリ)

リクエスト(商品ごとに売り上げの合計を取得し、最大値を求めるクエリ)
{
  "size": 0,
  "aggs": {
    "max_sum_total_price": {
      "max_bucket": {
        "buckets_path": "by_prodcuts>sum_total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得し、最大値を求めるクエリ)
{
  "took": 61,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          }
        }
      ]
    },
    "max_sum_total_price": {
      "value": 2847000,
      "keys": [
        "ハーゲンダッツ"
      ]
    }
  }
}

min_bucket

集約結果の中で最小の値を求める。

min_bucket
"min_bucket": {
   "buckets_path": "{{対象バケット名}}>{{対象metrics名}}",
}

例：min_bucket(商品ごとに売り上げの合計を取得し、最小値を求めるクエリ)

リクエスト(商品ごとに売り上げの合計を取得し、最小値を求めるクエリ)
{
  "size": 0,
  "aggs": {
    "min_sum_total_price": {
      "min_bucket": {
        "buckets_path": "by_prodcuts>sum_total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得し、最小値を求めるクエリ)
{
  "took": 57,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          }
        }
      ]
    },
    "min_sum_total_price": {
      "value": 292928,
      "keys": [
        "コアラのマーチ"
      ]
    }
  }
}

avg_bucket

集約結果の中で平均の値を求める。

avg_bucket
"avg_bucket": {
   "buckets_path": "{{対象バケット名}}>{{対象metrics名}}",
}

例：avg_bucket(商品ごとに売り上げの合計を取得し、平均値を求めるクエリ)

リクエスト(商品ごとに売り上げの合計を取得し、平均値を求めるクエリ)
{
  "size": 0,
  "aggs": {
    "avg_sum_total_price": {
      "avg_bucket": {
        "buckets_path": "by_prodcuts>sum_total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得し、平均値を求めるクエリ)
{
  "took": 24,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          }
        }
      ]
    },
    "avg_sum_total_price": {
      "value": 852788.2
    }
  }
}

sum_bucket

集約結果の中で合計の値を求める。

sum_bucket
"sum_bucket": {
   "buckets_path": "{{対象バケット名}}>{{対象metrics名}}",
}

例：(商品ごとに売り上げの合計を取得し、合計値を求めるクエリ)

リクエスト(商品ごとに売り上げの合計を取得し、合計値を求めるクエリ)
GET user_price_index_*/_search
{
  "size": 0,
  "aggs": {
    "sum_sum_total_price": {
      "sum_bucket": {
        "buckets_path": "by_prodcuts>sum_total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得し、合計値を求めるクエリ)
{
  "took": 180,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          }
        }
      ]
    },
    "sum_sum_total_price": {
      "value": 4263941
    }
  }
}

Script

ElasticSearchのクエリ内で、Script言語を使用することで表現の幅を広げることが可能。
複数のScript言語が使用できるがデフォルトではpainlessというScript言語が使用可能2
検索QueryやMetricsクエリなど様々場面で使用できるが、ここでは、bucket_script、bucket_selecter
での使い方を紹介する。

bucket_script

集約結果に対して、グループごとにScript処理を行う。

bucket_script
"bucket_script": {
   "buckets_path": { 
     "{{パラメータ名A}}":"{{対象metrics名}}" ,
     ....
   },
  "script":"{{スクリプトの処理}}"
}

scriptをより詳細に記述可能

bucket_script
"bucket_script": {
   "buckets_path": { 
     "{{パラメータ名A}}":"{{対象metrics名}}" ,　//集計された値を使用したい場合はここで、定義する。
     ....
   },
  "script":{
    "params":{
      "{{パラメータ名A}}":{{値}}  //ここで定数を定義できる？
　   },
     "lang":"{{スクリプト言語名}}",
     "inline":"{{スクリプトの処理}}"
  }
}

例：(商品ごとに売り上げの合計を取得し、一人頭の売上、消費税込みの総売上を求めるクエリ)

リクエスト(商品ごとに売り上げの合計を取得し、一人頭の売上、消費税込みの総売上を求めるクエリ)

{
  "size": 0,
  "aggs": {
    "sum_total_price2": {
      "sum": {
        "field": "total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {

        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        },
        "unique_user": {
          "cardinality": {
            "field": "user_id"
          }
        },
        "一人頭の売上": {
          "bucket_script": {
            "buckets_path": {
              "total_price": "sum_total_price",
              "unique_user_count": "unique_user"
            },
            "script": "(int)(params.total_price/params.unique_user_count)"
          }
        },
        "消費税込みの総売上": {
          "bucket_script": {
            "buckets_path": {
              "sum_total_price": "sum_total_price"

            },
            "script": {
              "params": {
                "tax_rate": 108
              },
              "inline": "(int)((params.sum_total_price*params.tax_rate)/100.0)"
            }
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに売り上げの合計を取得し、一人頭の売上、消費税込みの総売上を求めるクエリ)
{
  "took": 1246,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "sum_total_price2": {
      "value": 4263941
    },
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "コアラのマーチ",
          "doc_count": 1049,
          "sum_total_price": {
            "value": 292928
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 2929
          },
          "消費税込みの総売上": {
            "value": 316362
          }
        },
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 4397
          },
          "消費税込みの総売上": {
            "value": 474919
          }
        },
        {
          "key": "キノコの山",
          "doc_count": 997,
          "sum_total_price": {
            "value": 366480
          },
          "unique_user": {
            "value": 99
          },
          "一人頭の売上": {
            "value": 3701
          },
          "消費税込みの総売上": {
            "value": 395798
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 28470
          },
          "消費税込みの総売上": {
            "value": 3074760
          }
        },
        {
          "key": "杉のこ村",
          "doc_count": 938,
          "sum_total_price": {
            "value": 317793
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 3177
          },
          "消費税込みの総売上": {
            "value": 343216
          }
        }
      ]
    }
  }
}

bucket_selecter

集約結果に対して、フィルタリング処理を行う。

bucket_selecter
"bucket_selecter": {
   "buckets_path": { 
     "{{パラメータ名A}}":"{{対象metrics名}}" ,
     ....
   },
  "script":"{{真偽値を返すスクリプトの処理}}"
}

scriptをより詳細に記述可能

bucket_selecter
"bucket_script": {
   "buckets_path": { 
     "{{パラメータ名A}}":"{{対象metrics名}}" ,　//集計された値を使用したい場合はここで、定義する。
     ....
   },
  "script":{
    "params":{
      "{{パラメータ名A}}":{{値}}  //ここで定数を定義できる？
　   },
     "lang":"{{スクリプト言語名}}",
     "inline":"{{真偽値を返すスクリプトの処理}}"
  }
}

例：(商品ごとに総売上を取得し、一人頭の売上が4000以上の商品を取得するクエリ)

リクエスト(商品ごとに総売上を取得し、一人頭の売上が4000以上の商品を取得するクエリ)
{
  "size": 0,
  "aggs": {
    "sum_total_price2": {
      "sum": {
        "field": "total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {

        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        },
        "unique_user": {
          "cardinality": {
            "field": "user_id"
          }
        },
        "一人頭の売上": {
          "bucket_script": {
            "buckets_path": {
              "total_price": "sum_total_price",
              "unique_user_count": "unique_user"
            },
            "script": "(int)(params.total_price/params.unique_user_count)"
          }
        },"一人頭の売上が4000以上商品のみ表示": {
          "bucket_selector": {
            "buckets_path": {
              "total_price": "sum_total_price",
              "unique_user_count": "unique_user"
            },
            "script": "(int)(params.total_price/params.unique_user_count)>=4000"
          }
        }
      }
    }
  }
}

検索結果
検索結果(商品ごとに総売上を取得し、一人頭の売上が4000以上の商品を取得するクエリ)
{
  "took": 718,
  "timed_out": false,
  "_shards": {
    "total": 145,
    "successful": 145,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 7968,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "sum_total_price2": {
      "value": 4263941
    },
    "by_prodcuts": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "たけのこの里",
          "doc_count": 1036,
          "sum_total_price": {
            "value": 439740
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 4397
          }
        },
        {
          "key": "ハーゲンダッツ",
          "doc_count": 951,
          "sum_total_price": {
            "value": 2847000
          },
          "unique_user": {
            "value": 100
          },
          "一人頭の売上": {
            "value": 28470
          }
        }
      ]
    }
  }
}

課題点

気持ち以下のような、各種商品の売上が全体の何割を占めているか、求めたい場合のクエリの書き方がわからない。
以下のクエリはエラーになる。

エラーになるクエリ
{
  "size": 0,
  "aggs": {
    "全売上": {
      "sum": {
        "field": "total_price"
      }
    },
    "by_prodcuts": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "sum_total_price": {
          "sum": {
            "field": "total_price"
          }
        },
        "unique_user": {
          "cardinality": {
            "field": "user_id"
          }
        },
        "一人頭の売上": {
          "bucket_script": {
            "buckets_path": {
              "total_price": "sum_total_price",
              "unique_user_count": "unique_user"
            },
            "script": "(int)(params.total_price/params.unique_user_count)"
          }
        },
        "一人頭の売上/全売上": {
          "bucket_script": {
            "buckets_path": {
              "p1": "全売上",
              "p2": "一人頭の売上"
            },
            "script": "params.p2/params.p1"
          }
        }
      }
    }
  }
}

検索結果
検索結果(エラーになるクエリ)
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "No aggregation found for path [全売上]"
      },
      ......
  }
}

ElasticSearchシリーズ

  1. とはいっても、自分が理解している範囲での話に絞る。 ↩

  2. 使い方はほとんどわかっておりませんすみません。 ↩

5
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
7