elasticsearch系列七：聚合查询

概述

今天咱们来看下es中的聚合查询，在es中聚合查询分为三大类bucket、metrics、pipeline，每一大类下又有十几种小类，咱们各举例集中，有兴许的同学可以参考官网：https://www.elastic.co/guide/en/elasticsearch/reference/7.10/search-aggregations.html 本次基于es7.10.2版本编写。

metics聚合

常用指标类的聚合无外乎这几种：Avg、Min、Max、Sum、Cardinality、Percentile ranks。咱们来看下具体语法：

Avg、Min、Max、Sum这几个雷同只需要换函数名即可，假如我们有一个日志索引，其索引mapping如下：

{    "mappings": {        "properties": {            "routePath": {                "type":"keyword"            },            "serverCode": {                "type":"keyword"            },            "taskTime": {                "type":"long"            },            "reuqestMsg": {                "type":"text"            },            "responseMsg": {                "type":"text"            }        }    }}

我们想看下近一月的接口某接口平均耗时、最小耗时、最大耗时等指标，此时dsl可以如下编写：

GET /log-2023-02/_serach{    "size": 0,    "query": {        "bool": {            "filter": [                {                    "term": {                        "routePath": "/user/getUserInfo"                    }                }            ]        }    },    "aggs": {        "avg": {            "avg": {                "field": "taskTime"            }        }    }}

返回结果：

        咱们看下如何去重，根据接口地址去重查询：

{    "size": 0,    "aggs": {        "cardinality": {            "cardinality": {                "field": "routePath"            }        }    }}

只是这个cardinality有误差，它底层采用的是HyperLogLog的算法，通过计算数据的hash值来去重所以有误差，百万数据误差在5%以内，我们可以通过precision_threshold参数去调整最大支持4万，该值越大耗费内存也就越大如果数据总量在4万以内那么调整到最大值可以保证100%正确。

接下来咱们看Percentile ranks这个也是比较常用的聚合分析函数他的结果也是有误差的但是不影响我们分析整体情况，比如我们需要计算整体系统的性能可以这样搞：查询接口再响应这些耗时上的百分比就可以通过如下语句​​​​​​​

{    "size": 0,    "aggs": {        "rate": {            "percentile_ranks": {                "field": "taskTime",                "values": [                    20,                    40,                    50,                    60                ]            }        }    }}

结果：

bucket聚合

桶聚合中我们常用的有分组、直方图、范围、根据日期分桶聚合这几类，咱们先看下分组查询（terms）举例我们想统计下各个接口调用量情况：​​​​​​​

{    "size": 0,    "aggs": {        "term": {            "terms": {                "field": "routePath"            }        }    }

返回结果：​​​​​​​

"aggregations": {        "term": {            "doc_count_error_upper_bound": 0,            "sum_other_doc_count": 0,            "buckets": [                {                    "key": "/user/getUserInfo",                    "doc_count": 5                },                {                    "key": "/user/addUser",                    "doc_count": 1                },                {                    "key": "/user/updateMobile",                    "doc_count": 1                },                {                    "key": "/user/updateUser",                    "doc_count": 1                }            ]        }    }

咱们再看直方图的查询统计接口耗时、间隔为1：​​​​​​​

{    "size": 0,    "aggs": {        "histogram": {            "histogram": {                "field": "taskTime",                "interval": 1            }        }    }}

结果

"aggregations": {        "histogram": {            "buckets": [                {                    "key": 20.0,                    "doc_count": 2                },                {                    "key": 21.0,                    "doc_count": 0                },                {                    "key": 22.0,                    "doc_count": 0                }           ]        }    }

根据日期统计各接口调用情况，用直方图实行展现：​​​​​​​

{    "size": 0,    "aggs": {        "date_histogram": {            "date_histogram": {                "field": "requestTime",                "interval": "day"            }        }    }}

查询结果：

"aggregations": {        "histogram": {            "buckets": [                {                    "key_as_string": "2023-02-01T00:00:00.000Z",                    "key": 1675209600000,                    "doc_count": 1                },                {                    "key_as_string": "2023-02-02T00:00:00.000Z",                    "key": 1675296000000,                    "doc_count": 1                },                {                    "key_as_string": "2023-02-03T00:00:00.000Z",                    "key": 1675382400000,                    "doc_count": 1                }            ]        }    }

pipeline聚合

它其实是对bucket聚合的结果再次进行聚合分期，数据准备：

{ "create" : {  "_index" : "employees" } }
{ "name" : "Emma","age":32,"job":"Product Manager","gender":"female","salary":35000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Underwood","age":41,"job":"Dev Manager","gender":"male","salary": 50000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Tran","age":25,"job":"Web Designer","gender":"male","salary":18000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Rivera","age":26,"job":"Web Designer","gender":"female","salary": 22000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Rose","age":25,"job":"QA","gender":"female","salary":18000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Lucy","age":31,"job":"QA","gender":"female","salary": 25000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Byrd","age":27,"job":"QA","gender":"male","salary":20000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Foster","age":27,"job":"Java Programmer","gender":"male","salary": 20000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Gregory","age":32,"job":"Java Programmer","gender":"male","salary":22000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Bryant","age":20,"job":"Java Programmer","gender":"male","salary": 9000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Jenny","age":36,"job":"Java Programmer","gender":"female","salary":38000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Mcdonald","age":31,"job":"Java Programmer","gender":"male","salary": 32000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Jonthna","age":30,"job":"Java Programmer","gender":"female","salary":30000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Marshall","age":32,"job":"Javascript Programmer","gender":"male","salary": 25000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "King","age":33,"job":"Java Programmer","gender":"male","salary":28000 }
{ "create" : {  "_index" : "employees" } }
{ "name" : "Mccarthy","age":21,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Goodwin","age":25,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Catherine","age":29,"job":"Javascript Programmer","gender":"female","salary": 20000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Boone","age":30,"job":"DBA","gender":"male","salary": 30000}
{ "create" : {  "_index" : "employees" } }
{ "name" : "Kathy","age":29,"job":"DBA","gender":"female","salary": 20000}

我们根据以上数据想要查询平均薪资最低的行业：​​​​​​​

{  "size": 0,  "aggs": {    "jobs": {      "terms": {        "field": "job.keyword",        "size": 10      },      "aggs": {        "avg_salary": {          "avg": {            "field": "salary"          }        }      }    },    "min_salary_by_job":{      "min_bucket": {  #再次进行聚合查询 将jobs桶下的avg_salary求出最小值        "buckets_path": "jobs>avg_salary"      }    }  }}

结果如下：​​​​​​​

"aggregations": {        "jobs": {            "doc_count_error_upper_bound": 0,            "sum_other_doc_count": 0,            "buckets": [                {                    "key": "Java Programmer",                    "doc_count": 7,                    "avg_salary": {                        "value": 25571.428571428572                    }                },                {                    "key": "Javascript Programmer",                    "doc_count": 4,                    "avg_salary": {                        "value": 19250.0                    }                },                {                    "key": "DBA",                    "doc_count": 2,                    "avg_salary": {                        "value": 25000.0                    }                },                {                    "key": "Product Manager",                    "doc_count": 1,                    "avg_salary": {                        "value": 35000.0                    }                }            ]        },        "min_salary_by_job": {            "value": 19250.0,            "keys": [                "Javascript Programmer"            ]        }    }

还有将bucket结果再次进行平均 avg_bucket，bucket结果再次求最大的max_bucket，bucket结果再次求百分比的 percentiles_bucket等等。

总结

基本上咱们把常用的一些聚合查询都给大家演示了一遍，当然es本身支持的聚合查询远远不止这些，有兴趣的同学可以参考es官网的学习手册：https://www.elastic.co/guide/en/elasticsearch/reference/7.10/index.html 来探索更多的语法糖。

---

Elasticsearch系列经典文章

• elasticsearch列一：索引模板的使用

• elasticsearch系列二：引入索引模板后发现数据达到一定量还是慢怎么办？

• elasticsearch系列三：常用查询语法

• elasticsearch系列四：集群常规运维

• elasticsearch系列五：集群的备份与恢复

• elasticsearch系列六：索引重建