#集約クエリ(Aggregations)
##目標
ElasticSearchの集計機能を使えるようにする
使用するデータはここで定義した、商品売り上げログ。
##集約(Aggregation)とは？
集計する機能。SQLでいうGROUP BYに相当。sumやmaxといった集約を行える。
BucketとMetrics
集約クエリ(Aggregations)はBucketとMetricsに分かれる。
| 種類 | 役割 | 例 |
|---|---|---|
| Bucket | 検索結果を指定した条件で分類 | 検索結果を日ごとにグループ化、商品名ごとにグループ化等 |
| Metrics | Bucketのデータを集計 | 合計値を求める、最大値を求める |
集約クエリの例
例：total_priceの合計値と平均値を取得するクエリ
- total_price合計
- total_price平均
- placeごとの
- total_price合計
- total_price平均
リクエスト(total_priceの合計値と平均値を取得するクエリ)
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"ave_total_price": {
"avg": {
"field": "total_price"
}
},
"group_by_term_place": {
"terms": {
"field": "place.keyword"
},
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"ave_total_price": {
"avg": {
"field": "total_price"
}
}
}
}
}
}
結果
結果(total_priceの合計値と平均値を取得するクエリ)
{
"took": 25,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_term_place": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "西コンビニ",
"doc_count": 2056,
"sum_total_price": {
"value": 1134685
},
"ave_total_price": {
"value": 884.3998441153547
}
},
{
"key": "北デパート",
"doc_count": 2044,
"sum_total_price": {
"value": 1135869
},
"ave_total_price": {
"value": 871.0651840490798
}
},
{
"key": "南商店",
"doc_count": 1993,
"sum_total_price": {
"value": 966642
},
"ave_total_price": {
"value": 795.5901234567901
}
},
{
"key": "東スーパー",
"doc_count": 1875,
"sum_total_price": {
"value": 1026745
},
"ave_total_price": {
"value": 878.3105218135158
}
}
]
},
"sum_total_price": {
"value": 4263941
},
"ave_total_price": {
"value": 857.7632267149467
}
}
}
全体のtotal_priceの合計値と平均値とplaceごとのtotal_priceの合計値と平均値を取得。
集約クエリの構造
集約クエリのjsonサンプル
{
"size":0,
"query":{
....
},
"aggs":{
{{集約の名称A}} :{
<Bucketクエリ>:{
.....
},
"aggs":{
{{集約の名称A-1}} :{
<Metricsクエリ>:{
.....
},
}
}
}
}
}
集約クエリの基本的な書き方は、aggsクエリの中に{{集約の名称}}を定義し、その中にまたは、を記述。
sizeを0にして、documentを表示しない。
Metricsクエリ
以下では、よく使うMetricsクエリの基本的な記述方法とサンプルを紹介。
value_count
指定した、フィールドの個数を取得する。
value_count
"value_count": {
"field": "{{フィールド名}}"
}
例：total_priceの個数を取得するクエリ
リクエスト（total_priceの個数を取得するクエリ
GET user_price_index_*/_search
{
"size":0,
"aggs": {
"value_count_total_price": {
"value_count": {
"field": "total_price"
}
}
}
}
結果
結果（total_priceの個数を取得するクエリ）
{
"took": 13,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"value_count_total_price": {
"value": 4971
}
}
}
cardinality
指定したフィールドのデータのcardinality(異なり数、種類の数)を取得する。
具体的な使い方としては、ユーザーのユニーク数の取得。
高速化のためにHyperLogLog というアルゴリズムを使用。
精度は99%のことなので、実用上は問題になることはないと思うが、完全に正確な値が欲しい場合は別の方法を考える必要がある。
cardinality
"cardinality": {
"field": "{{フィールド名}}"
}
例：placeのユニーク数を取得するクエリ
リクエスト（placeのユニーク数を取得するクエリ）
GET user_price_index_*/_search
{
"size":0,
"aggs": {
"unique_count_place": {
"cardinality": {
"field": "place.keyword"
}
}
}
}
結果
結果（placeのユニーク数を取得）
{
"took": 369,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"unique_count_place": {
"value": 4
}
}
}
max、min、avg、sum
指定したフィールドのデータの最大値、最小値、平均値、合計値をそれぞれ求める。
max
"max": {
"field": "{{フィールド名}}"
}
min
"min": {
"field": "{{フィールド名}}"
}
avg
"avg": {
"field": "{{フィールド名}}"
}
sum
"sum": {
"field": "{{フィールド名}}"
}
例：total_priceの最大値、最小値、平均値、合計値を求めるクエリ
リクエスト（total_priceの最大値、最小値、平均値、合計値を求めるクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"max_total_price": {
"max": {
"field": "total_price"
}
},
"min_total_price": {
"min": {
"field": "total_price"
}
},
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"avg_total_price": {
"avg": {
"field": "total_price"
}
}
}
}
結果
結果（total_priceの最大値、最小値、平均値、合計値を求める）
{
"took": 126,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"sum_total_price": {
"value": 4263941
},
"max_total_price": {
"value": 5000
},
"min_total_price": {
"value": 92
},
"avg_total_price": {
"value": 857.7632267149467
}
}
}
Bucketクエリ
Bucketクエリは検索した結果を、様々な条件でグループ化することができるクエリ。
以下によく使われるグループ化するためのクエリを紹介する。
###date_histogram
date_histogram
"date_histogram": {
"field": "{{フィールド名}}",
"interval": "{{インターバルの間隔}}",
"time_zone": "{{タイムゾーン}}"
}
検索結果を一定時間間隔ごとにグループ化。
intervalパラメータで、グループ化する期間を変更可能。
intervalパラメータの一部を紹介。
| intervalパラメータ | 説明 |
|---|---|
| day | 1日単位でグループ化 |
| hour | 1時間単位でグループ化 |
| week | 1週間単位でグループ化 |
| month | 1か月単位でグループ化 |
例：1日ごとにtotal_priceの合計を取得するクエリ
リクエスト(1日ごとにtotal_priceの合計を取得するクエリ)
GET user_price_index_*/_search
{
"size":0,
"aggs":{
"group_by_date_historgram":{
"date_histogram": {
"field": "@timestamp",
"interval": "day",
"time_zone": "Asia/Tokyo"
},"aggs": {
"sum_total_price": {
"sum": {"field": "total_price"}
}
}
}
}
}
結果
結果(1日ごとにtotal_priceの合計を取得するクエリ)
{
"took": 104,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_date_historgram": {
"buckets": [
{
"key_as_string": "2019-06-01T00:00:00.000+09:00",
"key": 1559314800000,
"doc_count": 277,
"sum_total_price": {
"value": 137955
}
},
{
"key_as_string": "2019-06-02T00:00:00.000+09:00",
"key": 1559401200000,
"doc_count": 288,
"sum_total_price": {
"value": 178792
}
},
{
"key_as_string": "2019-06-03T00:00:00.000+09:00",
"key": 1559487600000,
"doc_count": 211,
"sum_total_price": {
"value": 95146
}
},
{
"key_as_string": "2019-06-04T00:00:00.000+09:00",
"key": 1559574000000,
"doc_count": 253,
"sum_total_price": {
"value": 131683
}
},
{
"key_as_string": "2019-06-05T00:00:00.000+09:00",
"key": 1559660400000,
"doc_count": 340,
"sum_total_price": {
"value": 186304
}
},
{
"key_as_string": "2019-06-06T00:00:00.000+09:00",
"key": 1559746800000,
"doc_count": 306,
"sum_total_price": {
"value": 179831
}
},
{
"key_as_string": "2019-06-07T00:00:00.000+09:00",
"key": 1559833200000,
"doc_count": 315,
"sum_total_price": {
"value": 179044
}
},
{
"key_as_string": "2019-06-08T00:00:00.000+09:00",
"key": 1559919600000,
"doc_count": 232,
"sum_total_price": {
"value": 121043
}
},
{
"key_as_string": "2019-06-09T00:00:00.000+09:00",
"key": 1560006000000,
"doc_count": 309,
"sum_total_price": {
"value": 169241
}
},
{
"key_as_string": "2019-06-10T00:00:00.000+09:00",
"key": 1560092400000,
"doc_count": 271,
"sum_total_price": {
"value": 148374
}
},
{
"key_as_string": "2019-06-11T00:00:00.000+09:00",
"key": 1560178800000,
"doc_count": 273,
"sum_total_price": {
"value": 121180
}
},
{
"key_as_string": "2019-06-12T00:00:00.000+09:00",
"key": 1560265200000,
"doc_count": 279,
"sum_total_price": {
"value": 149739
}
},
{
"key_as_string": "2019-06-13T00:00:00.000+09:00",
"key": 1560351600000,
"doc_count": 269,
"sum_total_price": {
"value": 135475
}
},
{
"key_as_string": "2019-06-14T00:00:00.000+09:00",
"key": 1560438000000,
"doc_count": 283,
"sum_total_price": {
"value": 163750
}
},
{
"key_as_string": "2019-06-15T00:00:00.000+09:00",
"key": 1560524400000,
"doc_count": 263,
"sum_total_price": {
"value": 143828
}
},
{
"key_as_string": "2019-06-16T00:00:00.000+09:00",
"key": 1560610800000,
"doc_count": 279,
"sum_total_price": {
"value": 118527
}
},
{
"key_as_string": "2019-06-17T00:00:00.000+09:00",
"key": 1560697200000,
"doc_count": 325,
"sum_total_price": {
"value": 189196
}
},
{
"key_as_string": "2019-06-18T00:00:00.000+09:00",
"key": 1560783600000,
"doc_count": 268,
"sum_total_price": {
"value": 171347
}
},
{
"key_as_string": "2019-06-19T00:00:00.000+09:00",
"key": 1560870000000,
"doc_count": 285,
"sum_total_price": {
"value": 167730
}
},
{
"key_as_string": "2019-06-20T00:00:00.000+09:00",
"key": 1560956400000,
"doc_count": 247,
"sum_total_price": {
"value": 112029
}
},
{
"key_as_string": "2019-06-21T00:00:00.000+09:00",
"key": 1561042800000,
"doc_count": 296,
"sum_total_price": {
"value": 160207
}
},
{
"key_as_string": "2019-06-22T00:00:00.000+09:00",
"key": 1561129200000,
"doc_count": 252,
"sum_total_price": {
"value": 99611
}
},
{
"key_as_string": "2019-06-23T00:00:00.000+09:00",
"key": 1561215600000,
"doc_count": 249,
"sum_total_price": {
"value": 141931
}
},
{
"key_as_string": "2019-06-24T00:00:00.000+09:00",
"key": 1561302000000,
"doc_count": 260,
"sum_total_price": {
"value": 133424
}
},
{
"key_as_string": "2019-06-25T00:00:00.000+09:00",
"key": 1561388400000,
"doc_count": 255,
"sum_total_price": {
"value": 147472
}
},
{
"key_as_string": "2019-06-26T00:00:00.000+09:00",
"key": 1561474800000,
"doc_count": 303,
"sum_total_price": {
"value": 158299
}
},
{
"key_as_string": "2019-06-27T00:00:00.000+09:00",
"key": 1561561200000,
"doc_count": 254,
"sum_total_price": {
"value": 145455
}
},
{
"key_as_string": "2019-06-28T00:00:00.000+09:00",
"key": 1561647600000,
"doc_count": 217,
"sum_total_price": {
"value": 106418
}
},
{
"key_as_string": "2019-06-29T00:00:00.000+09:00",
"key": 1561734000000,
"doc_count": 309,
"sum_total_price": {
"value": 170910
}
}
]
}
}
}
1日ごとのtotal_priceの合計が取得できた。
###histogram
histogram
"histogram": {
"field": "{{フィールド名}}",
"interval": {{インターバル間隔}}
}
指定したフィールドの数値データをintervalで設定した間隔ごとにグループ化する。
例：total_priceの値を2000ごとにグループ化し、最大値と最小値と合計値を求めるクエリ
リクエスト（total_priceの値を2000ごとにグループ化し、最大値と最小値と合計値を求めるクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"group_by_date_historgram": {
"histogram": {
"field": "total_price",
"interval": 2000
},
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"max_total_price": {
"max": {
"field": "total_price"
}
},
"min_total_price": {
"min": {
"field": "total_price"
}
}
}
}
}
}
結果
結果（total_priceの値を2000ごとにグループ化し、最大値と最小値と合計値を求めるクエリ）
{
"took": 18,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_date_historgram": {
"buckets": [
{
"key": 0,
"doc_count": 4209,
"max_total_price": {
"value": 1000
},
"sum_total_price": {
"value": 1605941
},
"min_total_price": {
"value": 92
}
},
{
"key": 2000,
"doc_count": 392,
"max_total_price": {
"value": 3000
},
"sum_total_price": {
"value": 969000
},
"min_total_price": {
"value": 2000
}
},
{
"key": 4000,
"doc_count": 370,
"max_total_price": {
"value": 5000
},
"sum_total_price": {
"value": 1689000
},
"min_total_price": {
"value": 4000
}
}
]
}
}
}
###date_range
date_range
"date_range": {
"field": "{{フィールド名}}",
"time_zone": "{{タイムゾーン}}",
"ranges": [
{
"key": "{{任意のキー名}}",
"from": "{{範囲開始（境界は含む）}}",
"to": "{{範囲終了（境界は含まない）}}"
}, ...
]
}
検索した時系列データを設定した範囲ごとに区切りグループ化する。
rangesフィールドで、グループ化する範囲を指定する。
例：検索結果を@timestampで4分割し、total_priceの最大値と平均値を取得するクエリ
| key | 範囲 |
|---|---|
| june-first | 2019-06-01～2019-06-10 |
| june-second | 2019-06-11～2019-06-20 |
| june-third | 2019-06-21～2019-06-30 |
| june-all | 2019-06-01～2019-06-30 |
| [分割する@timestampの範囲とkey] |
リクエスト（検索結果を@timestampで4分割し、total_priceの最大値と平均値を取得するクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"group_by_date_range": {
"date_range": {
"field": "@timestamp",
"time_zone": "Asia/Tokyo",
"ranges": [
{
"key": "june-first",
"from": "2019-06-01T00:00:00",
"to": "2019-06-11T00:00:00"
},
{
"key": "june-second",
"from": "2019-06-11T00:00:00",
"to": "2019-06-21T00:00:00"
},
{
"key": "june-third",
"from": "2019-06-21T00:00:00",
"to": "2019-07-01T00:00:00"
},
{
"key": "june-all",
"from": "2019-06-01T00:00:00",
"to": "2019-07-01T00:00:00"
}
]
},
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"avg_total_price": {
"avg": {
"field": "total_price"
}
}
}
}
}
}
結果
結果（検索結果を@timestampで4分割し、total_priceの最大値と平均値を取得するクエリ）
{
"took": 10,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_date_range": {
"buckets": [
{
"key": "june-first",
"from": 1559314800000,
"from_as_string": "2019-06-01T00:00:00.000+09:00",
"to": 1560178799000,
"to_as_string": "2019-06-10T23:59:59.000+09:00",
"doc_count": 2802,
"sum_total_price": {
"value": 1527413
},
"avg_total_price": {
"value": 870.8169897377423
}
},
{
"key": "june-all",
"from": 1559314800000,
"from_as_string": "2019-06-01T00:00:00.000+09:00",
"to": 1561906799000,
"to_as_string": "2019-06-30T23:59:59.000+09:00",
"doc_count": 7968,
"sum_total_price": {
"value": 4263941
},
"avg_total_price": {
"value": 857.7632267149467
}
},
{
"key": "june-second",
"from": 1560178800000,
"from_as_string": "2019-06-11T00:00:00.000+09:00",
"to": 1561042799000,
"to_as_string": "2019-06-20T23:59:59.000+09:00",
"doc_count": 2771,
"sum_total_price": {
"value": 1472801
},
"avg_total_price": {
"value": 844.9804934021802
}
},
{
"key": "june-third",
"from": 1561042800000,
"from_as_string": "2019-06-21T00:00:00.000+09:00",
"to": 1561906799000,
"to_as_string": "2019-06-30T23:59:59.000+09:00",
"doc_count": 2395,
"sum_total_price": {
"value": 1263727
},
"avg_total_price": {
"value": 857.3453188602442
}
}
]
}
}
}
###range
検索した数値データを設定した範囲ごとに区切りグループ化する。
rangesフィールドで、グループ化する範囲を指定する。
range
"range": {
"field": "{{フィールド名}}",
"ranges": [
{
"key": "{{キー名}}",
"from": {{範囲開始（境界は含む）}}
"to": {{範囲終了（境界は含まない）}}
},
....
]
}
例：total_priceをグループ化し、グループごとのtotal_priceの最大値と最小値を取得するクエリ
| key | 範囲 |
|---|---|
| low | ～699 |
| middle | 700～2999 |
| high | 3000～ |
| [グループとtotal_priceの値の範囲] |
リクエスト（total_priceをグループ化し、グループごとのtotal_priceの最大値と最小値を取得するクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"group_by_range_price": {
"range": {
"field": "total_price",
"ranges": [
{
"key": "low",
"to": 700
},
{
"key": "middle",
"from": 700,
"to": 3000
},
{
"key": "high",
"from": 3000
}
]
},
"aggs": {
"max_total_price": {
"max": {
"field": "total_price"
}
},
"min_total_price": {
"min": {
"field": "total_price"
}
}
}
}
}
}
結果
結果（total_priceをグループ化し、グループごとのtotal_priceの最大値と最小値を取得するクエリ）
{
"took": 6,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_range_price": {
"buckets": [
{
"key": "low",
"to": 700,
"doc_count": 3795,
"max_total_price": {
"value": 600
},
"min_total_price": {
"value": 92
}
},
{
"key": "middle",
"from": 700,
"to": 3000,
"doc_count": 621,
"max_total_price": {
"value": 2000
},
"min_total_price": {
"value": 700
}
},
{
"key": "high",
"from": 3000,
"doc_count": 555,
"max_total_price": {
"value": 5000
},
"min_total_price": {
"value": 3000
}
}
]
}
}
}
###filter
検索した結果に対して、フィルタリングを行う。
filter
"filter": {
{{フィルタリング条件}}
}
例：検索した結果に対して、placeでフィルタリングを行い、total_priceの合計値と平均値を取得するクエリ
リクエスト（検索した結果に対して、placeでフィルタリングを行い、total_priceの合計値と平均値を取得するクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"group_by_filters_place": {
"filter": {
"terms": {
"place.keyword": [
"南商店",
"北デパート"
]
}
},
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"avg_total_price": {
"avg": {
"field": "total_price"
}
}
}
}
}
}
結果
結果（検索した結果に対して、placeでフィルタリングを行い、total_priceの合計値と平均値を取得するクエリ）
{
"took": 10,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_filters_place": {
"doc_count": 4037,
"sum_total_price": {
"value": 2102511
},
"avg_total_price": {
"value": 834.6609765780072
}
}
}
}
###terms
検索結果を、指定したフィールドの値ごとにグループ化する。
terms
"terms": {
"field": "{{フィールド名}}",
"size":{{グループ数(デフォルトは5)}}
}
sizeで、グループ化する数をコントロール可能。
指定したフィールドの値の種別がsizeより多い場合、グループ化はされずに切り捨てられる。
例：productの値ごとにグループ化し、total_priceの合計値と平均値を取得するクエリ
リクエスト（productの値ごとにグループ化し、total_priceの合計値と平均値を取得するクエリ）
GET user_price_index_*/_search
{
"size": 0,
"aggs": {
"group_by_terms_product": {
"terms": {
"field": "product.keyword",
"size":5
},
"aggs": {
"sum_total_price": {
"sum": {
"field": "total_price"
}
},
"avg_total_price": {
"avg": {
"field": "total_price"
}
}
}
}
}
}
結果
結果（productの値ごとにグループ化し、total_priceの合計値と平均値を取得するクエリ）
{
"took": 13,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7968,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_terms_product": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "コアラのマーチ",
"doc_count": 1049,
"sum_total_price": {
"value": 292928
},
"avg_total_price": {
"value": 279.24499523355576
}
},
{
"key": "たけのこの里",
"doc_count": 1036,
"sum_total_price": {
"value": 439740
},
"avg_total_price": {
"value": 424.4594594594595
}
},
{
"key": "キノコの山",
"doc_count": 997,
"sum_total_price": {
"value": 366480
},
"avg_total_price": {
"value": 367.5827482447342
}
},
{
"key": "ハーゲンダッツ",
"doc_count": 951,
"sum_total_price": {
"value": 2847000
},
"avg_total_price": {
"value": 2993.6908517350157
}
},
{
"key": "杉のこ村",
"doc_count": 938,
"sum_total_price": {
"value": 317793
},
"avg_total_price": {
"value": 338.7985074626866
}
}
]
}
}
}
#ElasticSearchシリーズ
- [ElasticSearchの検索クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/97d41c275434be897d58)
- [ElaticSearchの検索クエリに関して（応用編）] (https://qiita.com/horankey_jet_city/private/67bb73b42014cfb99f27)
- ElasticSearchの集約クエリに関して（基礎編）
- ElasticSearchの集約クエリに関して（応用？編）