Elasticsearchでサンプルのデータセット(ドキュメント1000件)を用いてQuery DSLの簡単な集計機能をいくつか試す

Elasticsearchでサンプルのデータセット(ドキュメント1000件)を用いてQuery DSLの簡単な集計機能をいくつか試す方法を記す。

公式ドキュメント Executing Aggregations を参考にした。 www.elastic.co

検証環境: Elasticsearch 6.0.0-rc1

前提として、Elasticsearchにサンプルのデータセット(ドキュメント1000件)をbulk APIを使って登録しておきQuery DSLを用いて検索方法を試す。 sakura818uuu.hatenadiary.com

sakura818uuu.hatenadiary.com

sakura818uuu.hatenadiary.com

sakura818uuu.hatenadiary.com

Elasticsearchには集計機能がある。SQLでいうところのGROUP BYだ。
以下の検索クエリでは、ドキュメントの中からstate(ユーザーの住んでいる州)をごとに集計し、それをグループ化したものの上位10件(sizeのデフォルトが10件)を降順に表示することを意味する。sizeを0に設定したのは集計結果だけをみたいからだ。 SQLで書くと SELECT state, COUNT() FROM bank GROUP BY state ORDER BY COUNT() DESC こんなかんじになるだろう。

bash-3.2$ curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
> {
>   "size": 0,
>   "aggs": {
>     "group_by_state": {
>       "terms": {
>         "field": "state.keyword"
>       }
>     }
>   }
> }
> '
{
  "took" : 80,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1000,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_state" : {
      "doc_count_error_upper_bound" : 20,
      "sum_other_doc_count" : 770,
      "buckets" : [
        {
          "key" : "ID",
          "doc_count" : 27
        },
        {
          "key" : "TX",
          "doc_count" : 27
        },
        {
          "key" : "AL",
          "doc_count" : 25
        },
        {
          "key" : "MD",
          "doc_count" : 25
        },
        {
          "key" : "TN",
          "doc_count" : 23
        },
        {
          "key" : "MA",
          "doc_count" : 21
        },
        {
          "key" : "NC",
          "doc_count" : 21
        },
        {
          "key" : "ND",
          "doc_count" : 21
        },
        {
          "key" : "ME",
          "doc_count" : 20
        },
        {
          "key" : "MO",
          "doc_count" : 20
        }
      ]
    }
  }
}

実行結果をみると、ID(アイダホ州を意味する)は27件のヒットがあり、次にTX(テキサス州)も27件、次にAL(アラバマ州)が25件という風に集計されている。

次のクエリでは州ごとの平均balance(アカウント口座残高)を表示する。並んでいる順序はbalanceの降順ではなく、state(ユーザーが住んでいる州)の降順である。

bash-3.2$ curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
> {
>   "size": 0,
>   "aggs": {
>     "group_by_state": {
>       "terms": {
>         "field": "state.keyword"
>       },
>       "aggs": {
>         "average_balance": {
>           "avg": {
>             "field": "balance"
>           }
>         }
>       }
>     }
>   }
> }
> '
{
  "took" : 27,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1000,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_state" : {
      "doc_count_error_upper_bound" : 20,
      "sum_other_doc_count" : 770,
      "buckets" : [
        {
          "key" : "ID",
          "doc_count" : 27,
          "average_balance" : {
            "value" : 24368.777777777777
          }
        },
        {
          "key" : "TX",
          "doc_count" : 27,
          "average_balance" : {
            "value" : 27462.925925925927
          }
        },
        {
          "key" : "AL",
          "doc_count" : 25,
          "average_balance" : {
            "value" : 25739.56
          }
        },
        {
          "key" : "MD",
          "doc_count" : 25,
          "average_balance" : {
            "value" : 24963.52
          }
        },
        {
          "key" : "TN",
          "doc_count" : 23,
          "average_balance" : {
            "value" : 29796.782608695652
          }
        },
        {
          "key" : "MA",
          "doc_count" : 21,
          "average_balance" : {
            "value" : 29726.47619047619
          }
        },
        {
          "key" : "NC",
          "doc_count" : 21,
          "average_balance" : {
            "value" : 26785.428571428572
          }
        },
        {
          "key" : "ND",
          "doc_count" : 21,
          "average_balance" : {
            "value" : 26303.333333333332
          }
        },
        {
          "key" : "ME",
          "doc_count" : 20,
          "average_balance" : {
            "value" : 19575.05
          }
        },
        {
          "key" : "MO",
          "doc_count" : 20,
          "average_balance" : {
            "value" : 24151.8
          }
        }
      ]
    }
  }
}

次のクエリでは州ごとの平均balance(アカウント口座残高)を表示する。こちらではorderに"average_balance": "desc"としており、並んでいる順序はstate(ユーザーが住んでいる州)の降順ではなく平均balance(アカウント残高)の降順である。

bash-3.2$ curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
> {
>   "size": 0,
>   "aggs": {
>     "group_by_state": {
>       "terms": {
>         "field": "state.keyword",
>         "order": {
>           "average_balance": "desc"
>         }
>       },
>       "aggs": {
>         "average_balance": {
>           "avg": {
>             "field": "balance"
>           }
>         }
>       }
>     }
>   }
> }
> '
{
  "took" : 11,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1000,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_state" : {
      "doc_count_error_upper_bound" : -1,
      "sum_other_doc_count" : 918,
      "buckets" : [
        {
          "key" : "AL",
          "doc_count" : 6,
          "average_balance" : {
            "value" : 41418.166666666664
          }
        },
        {
          "key" : "SC",
          "doc_count" : 1,
          "average_balance" : {
            "value" : 40019.0
          }
        },
        {
          "key" : "AZ",
          "doc_count" : 10,
          "average_balance" : {
            "value" : 36847.4
          }
        },
        {
          "key" : "VA",
          "doc_count" : 13,
          "average_balance" : {
            "value" : 35418.846153846156
          }
        },
        {
          "key" : "DE",
          "doc_count" : 8,
          "average_balance" : {
            "value" : 35135.375
          }
        },
        {
          "key" : "WA",
          "doc_count" : 7,
          "average_balance" : {
            "value" : 34787.142857142855
          }
        },
        {
          "key" : "ME",
          "doc_count" : 3,
          "average_balance" : {
            "value" : 34539.666666666664
          }
        },
        {
          "key" : "OK",
          "doc_count" : 9,
          "average_balance" : {
            "value" : 34529.77777777778
          }
        },
        {
          "key" : "CO",
          "doc_count" : 13,
          "average_balance" : {
            "value" : 33379.769230769234
          }
        },
        {
          "key" : "MI",
          "doc_count" : 12,
          "average_balance" : {
            "value" : 32905.916666666664
          }
        }
      ]
    }
  }
}

以下のクエリでは、年齢層ごとの性別ごとの平均balance(アカウント口座残高)を取得する。age(年齢)で20〜29歳・30〜39歳・40〜49歳・の3つにグループ分けし、かつ、gender(性別)でグループ分けしている。

bash-3.2$ curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
> {
>   "size": 0,
>   "aggs": {
>     "group_by_age": {
>       "range": {
>         "field": "age",
>         "ranges": [
>           {
>             "from": 20,
>             "to": 30
>           },
>           {
>             "from": 30,
>             "to": 40
>           },
>           {
>             "from": 40,
>             "to": 50
>           }
>         ]
>       },
>       "aggs": {
>         "group_by_gender": {
>           "terms": {
>             "field": "gender.keyword"
>           },
>           "aggs": {
>             "average_balance": {
>               "avg": {
>                 "field": "balance"
>               }
>             }
>           }
>         }
>       }
>     }
>   }
> }
> '
{
  "took" : 22,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1000,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_age" : {
      "buckets" : [
        {
          "key" : "20.0-30.0",
          "from" : 20.0,
          "to" : 30.0,
          "doc_count" : 451,
          "group_by_gender" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "M",
                "doc_count" : 232,
                "average_balance" : {
                  "value" : 27374.05172413793
                }
              },
              {
                "key" : "F",
                "doc_count" : 219,
                "average_balance" : {
                  "value" : 25341.260273972603
                }
              }
            ]
          }
        },
        {
          "key" : "30.0-40.0",
          "from" : 30.0,
          "to" : 40.0,
          "doc_count" : 504,
          "group_by_gender" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "F",
                "doc_count" : 253,
                "average_balance" : {
                  "value" : 25670.869565217392
                }
              },
              {
                "key" : "M",
                "doc_count" : 251,
                "average_balance" : {
                  "value" : 24288.239043824702
                }
              }
            ]
          }
        },
        {
          "key" : "40.0-50.0",
          "from" : 40.0,
          "to" : 50.0,
          "doc_count" : 45,
          "group_by_gender" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "M",
                "doc_count" : 24,
                "average_balance" : {
                  "value" : 26474.958333333332
                }
              },
              {
                "key" : "F",
                "doc_count" : 21,
                "average_balance" : {
                  "value" : 27992.571428571428
                }
              }
            ]
          }
        }
      ]
    }
  }
}