elasticSearch核心概念的介绍（十）：指标聚合查询

聚合查询之指标查询

上一章介绍基本的范围、布尔、排序查询，有兴趣的可以参考一下
elasticSearch核心概念的介绍（九）：范围、布尔、排序查询
这一章我们介绍了聚合查询的指标聚合

ES 聚合查询是什么

聚合查询时数据库中重要的功能特性，完成对一个查询得到的数据集的聚合计算，如：找出某字段（或计算表达式的结果）的最大值、最小值、计算和、平均值等。ES作为搜索引擎，同样提供了强大的聚合分析能力。对一个数据集求最大、最小、和、平均值等指标的聚合，在ES 中成为指标聚合而关系型数据中除了有集合函数外，还可以对查询出的数据进行分组group by，再在祖上进行指标聚合，在ES 中称为桶聚合

–	–
max	最大数
min	最小数
sum	求和
avg	平均数
value_count	统计非空字段的文档数
Cardinality	值去重
stats	统计 count max min avg sum5个值
Extended stats	比stats多4个统计：平方和、方差、标准差、平均值加/减两个标准差的区间
Percentiles	占比百分位对应的值统计，默认返回[1,5,25,50,75,95,99]分位上的值

查询出平均球衣号

请求

curl -X POST "http://172.25.45.150:9200/nba/_search" -H 'Content-Type:application/json' -d '
{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "avgJer":{
           "avg":{
                "field":"jerse_no"
           }
        }
    }
}
'

响应

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 0.26706278,
        "hits": [
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "l4cuG38B4RxnBaUYPdnZ",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 30
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mIcuG38B4RxnBaUYWNko",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 23
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mYcuG38B4RxnBaUYadlM",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 35
                }
            }
        ]
    },
    "aggregations": {
        "avgJer": {
            "value": 29.333333333333332
        }
    }
}

Cardinality 去重

请求

curl -X POST "http://172.25.45.150:9200/nba/_search" -H 'Content-Type:application/json' -d '
{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "avgJer":{
           "cardinality":{
                "field":"position"
           }
        }
    }
}
'

响应

{
    "took": 21,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 0.26706278,
        "hits": [
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "l4cuG38B4RxnBaUYPdnZ",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 30
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mIcuG38B4RxnBaUYWNko",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 23
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mYcuG38B4RxnBaUYadlM",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 35
                }
            }
        ]
    },
    "aggregations": {
        "avgJer": {
            "value": 1
        }
    }
}

stats 查出聚合集合

请求

curl -X POST "http://172.25.45.150:9200/nba/_search" -H 'Content-Type:application/json' -d '

{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "statsAge":{
           "stats":{
                "field":"jerse_no"
           }
        }
    }
}
'

响应

{
    "took": 10,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 0.26706278,
        "hits": [
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "l4cuG38B4RxnBaUYPdnZ",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 30
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mIcuG38B4RxnBaUYWNko",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 23
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mYcuG38B4RxnBaUYadlM",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 35
                }
            }
        ]
    },
    "aggregations": {
        "statsAge": {
            "count": 3,
            "min": 23.0,
            "max": 35.0,
            "avg": 29.333333333333332,
            "sum": 88.0
        }
    }
}

Extended stats

请求

curl -X POST "http://172.25.45.150:9200/nba/_search" -H 'Content-Type:application/json' -d '
{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "extendStats":{
           "extended_stats":{
                "field":"jerse_no"
           }
        }
    }
}

'

响应

{
    "took": 20,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 0.26706278,
        "hits": [
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "l4cuG38B4RxnBaUYPdnZ",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 30
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mIcuG38B4RxnBaUYWNko",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 23
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mYcuG38B4RxnBaUYadlM",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 35
                }
            }
        ]
    },
    "aggregations": {
        "extendStats": {
            "count": 3,
            "min": 23.0,
            "max": 35.0,
            "avg": 29.333333333333332,
            "sum": 88.0,
            "sum_of_squares": 2654.0,
            "variance": 24.22222222222217,
            "std_deviation": 4.921607686744462,
            "std_deviation_bounds": {
                "upper": 39.17654870682226,
                "lower": 19.490117959844408
            }
        }
    }
}

Percentiles 统计球衣号的占比

请求

curl -X POST "http://172.25.45.150:9200/nba/_search" -H 'Content-Type:application/json' -d '

{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "percentJerse":{
           "percentiles":{
                "field":"jerse_no"
           }
        }
    }
}
'

当前我们也可以自定义百分位数，不使用默认的参数如下

{
    "query":{
        "match":{
            "name":"库里"
        }
    },
    "aggs":{
        "percentJerse":{
           "percentiles":{
                "field":"jerse_no",
                "percents":[
                    20,
                    50,
                    75
                ]
           }
        }
    }
}

响应

{
    "took": 14,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 0.26706278,
        "hits": [
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "l4cuG38B4RxnBaUYPdnZ",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 30
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mIcuG38B4RxnBaUYWNko",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 23
                }
            },
            {
                "_index": "nba",
                "_type": "_doc",
                "_id": "mYcuG38B4RxnBaUYadlM",
                "_score": 0.26706278,
                "_source": {
                    "name": "库里",
                    "team_name": "勇士",
                    "position": "组织后卫",
                    "play_year": "10",
                    "jerse_no": 35
                }
            }
        ]
    },
    "aggregations": {
        "percentJerse": {
            "values": {
                "1.0": 22.999999999999996,
                "5.0": 23.0,
                "25.0": 24.75,
                "50.0": 30.0,
                "75.0": 33.75,
                "95.0": 35.0,
                "99.0": 35.0
            }
        }
    }
}

elasticSearch核心概念的介绍（十）：指标聚合查询

大数据系统相关栏目本月热门文章