#集約クエリ(Aggregations)
##はじめに
[ElasticSearchの集約クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/94eda5046a8fe1febe45) で、集約の方法を記述したが、今回はもう少し難易度の高い集約クエリの説明をする。¹

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

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] (https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-walkthrough.html)というScript言語が使用可能[^iiwake2]

検索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シリーズ - [ElasticSearchの検索クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/97d41c275434be897d58) - [ElaticSearchの検索クエリに関して（応用編）] (https://qiita.com/horankey_jet_city/private/67bb73b42014cfb99f27) - [ElasticSearchの集約クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/94eda5046a8fe1febe45) - ElasticSearchの集約クエリに関して（応用？編）

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