ElasticSearch聚合操作
ElasticSearch聚合操作
·
一、聚合简介
1、聚合简介
Elasticsearch除搜索以外，提供了针对 ES 数据进行统计分析的功能。
聚合(aggregations)：可以让我们极其方便的实现对数据的统计、分析、运算。
基本语法：
"aggs" : { #和query同级的关键词
"<aggregation_name>" : { #自定义的聚合名字
"<aggregation_type>" : { #聚合的定义：不同的type+body
<aggregation_body>
}
[,"meta" : { [<meta_data_body>] } ]?
[,"aggregations" : { [<sub_aggregation>]+ } ]? #子聚合查询
}
[,"<aggregation_name_2>" : { ... } ]* #可以包含多个同级的聚合查询
}
2、聚合的分类
- Metric Aggregation：一些数学运算，可以对文档字段进行统计分析，类比 Mysql中的 min(), max(), sum() 操作。
- Bucket Aggregation：一些满足特定条件的文档的集合放置到一个桶里，每一个桶关联一个 key，类比Mysql中的group by操作。
- Pipeline Aggregation：对其他的聚合结果进行二次聚合。
示例数据：
DELETE /employees
#创建索引库
PUT /employees
{
"mappings": {
"properties": {
"age": {
"type": "integer"
},
"gender": {
"type": "keyword"
},
"job": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 50
}
}
},
"name": {
"type": "keyword"
},
"salary": {
"type": "integer"
}
}
}
}
PUT /employees/_bulk
{ "index" : { "_id" : "1" } }
{ "name" : "Emma","age":32,"job":"Product Manager","gender":"female","salary":35000 }
{ "index" : { "_id" : "2" } }
{ "name" : "Underwood","age":41,"job":"Dev Manager","gender":"male","salary": 50000}
{ "index" : { "_id" : "3" } }
{ "name" : "Tran","age":25,"job":"Web Designer","gender":"male","salary":18000 }
{ "index" : { "_id" : "4" } }
{ "name" : "Rivera","age":26,"job":"Web Designer","gender":"female","salary": 22000}
{ "index" : { "_id" : "5" } }
{ "name" : "Rose","age":25,"job":"QA","gender":"female","salary":18000 }
{ "index" : { "_id" : "6" } }
{ "name" : "Lucy","age":31,"job":"QA","gender":"female","salary": 25000}
{ "index" : { "_id" : "7" } }
{ "name" : "Byrd","age":27,"job":"QA","gender":"male","salary":20000 }
{ "index" : { "_id" : "8" } }
{ "name" : "Foster","age":27,"job":"Java Programmer","gender":"male","salary": 20000}
{ "index" : { "_id" : "9" } }
{ "name" : "Gregory","age":32,"job":"Java Programmer","gender":"male","salary":22000 }
{ "index" : { "_id" : "10" } }
{ "name" : "Bryant","age":20,"job":"Java Programmer","gender":"male","salary": 9000}
{ "index" : { "_id" : "11" } }
{ "name" : "Jenny","age":36,"job":"Java Programmer","gender":"female","salary":38000 }
{ "index" : { "_id" : "12" } }
{ "name" : "Mcdonald","age":31,"job":"Java Programmer","gender":"male","salary": 32000}
{ "index" : { "_id" : "13" } }
{ "name" : "Jonthna","age":30,"job":"Java Programmer","gender":"female","salary":30000 }
{ "index" : { "_id" : "14" } }
{ "name" : "Marshall","age":32,"job":"Javascript Programmer","gender":"male","salary": 25000}
{ "index" : { "_id" : "15" } }
{ "name" : "King","age":33,"job":"Java Programmer","gender":"male","salary":28000 }
{ "index" : { "_id" : "16" } }
{ "name" : "Mccarthy","age":21,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : { "_id" : "17" } }
{ "name" : "Goodwin","age":25,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : { "_id" : "18" } }
{ "name" : "Catherine","age":29,"job":"Javascript Programmer","gender":"female","salary": 20000}
{ "index" : { "_id" : "19" } }
{ "name" : "Boone","age":30,"job":"DBA","gender":"male","salary": 30000}
{ "index" : { "_id" : "20" } }
{ "name" : "Kathy","age":29,"job":"DBA","gender":"female","salary": 20000}
二、聚合操作
1、Metric Aggregation
Metric Aggregation 一些数学运算，可以对文档字段进行统计分析。
比如：
- 单值分析：只输出一个分析结果
- min, max, avg, sum
- Cardinality（类似distinct Count)
- 多值分析：输出多个分析结果
- stats（统计）, extended stats
- percentile （百分位）, percentile rank
- top hits(排在前面的示例)
1.1 查询员工的最低最高和平均工资
#多个 Metric 聚合，找到最低最高和平均工资
POST /employees/_search
{
"size": 0,
"aggs": {
"max_salary": {
"max": {
"field": "salary"
}
},
"min_salary": {
"min": {
"field": "salary"
}
},
"avg_salary": {
"avg": {
"field": "salary"
}
}
}
}
1.2 对salary进行统计
# 一个聚合，输出多个统计值
POST /employees/_search
{
"size": 0,
"aggs": {
"stats_salary": {
"stats": {
"field": "salary"
}
}
}
}
1.3 cardinate对搜索结果去重统计
#对job结果去重统计
POST /employees/_search
{
"size": 0,
"aggs": {
"cardinate": {
"cardinality": {
"field": "job.keyword"
}
}
}
}
2、Bucket Aggregation
Bucket Aggregation：按照一定的规则，将文档分配到不同的桶中，每一个桶关联一个 key，从而达到分类的目的。类比Mysql中的group by操作。
ES提供的一些常见的Bucket Aggregation。
- Terms，需要字段支持filedata
- keyword 默认支持fielddata
- text需要在Mapping 中开启fielddata，会按照分词后的结果进行分桶
- 数字类型
- Range / Data Range
- Histogram（直方图） / Date Histogram - 支持嵌套：也就在桶里再做分桶
2.1 获取 job的分类信息
POST /employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
}
}
}
}
聚合可配置属性有：
- field：指定聚合字段
- size：指定聚合结果数量
- order：指定聚合结果排序方式
默认情况下，Bucket聚合会统计 Bucket内的文档数量，记为 _count，并且按照 _count降序排序。我们可以指定 order属性。
自定义聚合的排序方式：
POST /employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"size": 10,
"order": {
"_count": "desc"
}
}
}
}
}
2.2 限定聚合范围
#只对salary在10000元以上的文档聚合
POST /employees/_search
{
"query": {
"range": {
"salary": {
"gte": 10000
}
}
},
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"size": 10,
"order":{
"_count": "asc"
}
}
}
}
}
2.3 对 Text 字段进行 terms 聚合查询，会失败抛出异常
注意：对 Text 字段进行 terms 聚合查询，会失败抛出异常。
POST /employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job"
}
}
}
}
解决办法：对 Text 字段打开 fielddata，支持 terms aggregation
#text字段打开fielddata.
PUT /employees/_mapping
{
"properties": {
"job": {
"type": "text",
"fielddata": true
}
}
}
#对 text字段进行分词，分词后的terms
POST /employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job",
"size": 10
}
}
}
}
对 job.keyword 和 job 进行 terms 聚合，分桶的总数并不一样
POST /employees/_search
{
"size": 0,
"aggs": {
"cardinate": {
"cardinality": {
"field": "job"
#"field": "job.keyword"
}
}
}
}
2.4 Range & Histogram聚合
Range & Histogram聚合：
- 按照数字的范围，进行分桶
- 在 Range Aggregation中，可以自定义 Key
2.4.1 Range 示例：按照工资的 Range 分桶
#Salary Range分桶，可以自己定义 key
POST employees/_search
{
"size": 0,
"aggs": {
"salary_range": {
"range": {
"field": "salary",
"ranges": [
{
"to": 10000
},
{
"from": 10000,
"to": 20000
},
{
"key": ">20000",
"from": 20000
}
]
}
}
}
}
2.4.2 Histogram示例：按照工资的间隔分桶
#工资0到10万，以 5000一个区间进行分桶
POST employees/_search
{
"size": 0,
"aggs": {
"salary_histrogram": {
"histogram": {
"field": "salary",
"interval": 5000,
"extended_bounds": {
"min": 0,
"max": 100000
}
}
}
}
}
2.5 top_hits
top_hits应用场景：当获取分桶后，桶内最匹配的顶部文档列表
# 指定size，不同工种中，年纪最大的3个员工的具体信息
POST /employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
},
"aggs": {
"old_employee": {
"top_hits": {
"size": 3,
"sort": [
{
"age": {
"order": "desc"
}
}
]
}
}
}
}
}
}
2.6 嵌套聚合示例
# 嵌套聚合1，按照工作类型分桶，并统计工资信息
POST employees/_search
{
"size": 0,
"aggs": {
"Job_salary_stats": {
"terms": {
"field": "job.keyword"
},
"aggs": {
"salary": {
"stats": {
"field": "salary"
}
}
}
}
}
}
# 多次嵌套。根据工作类型分桶，然后按照性别分桶，计算工资的统计信息
POST employees/_search
{
"size": 0,
"aggs": {
"Job_gender_stats": {
"terms": {
"field": "job.keyword"
},
"aggs": {
"gender_stats": {
"terms": {
"field": "gender"
},
"aggs": {
"salary_stats": {
"stats": {
"field": "salary"
}
}
}
}
}
}
}
}
3、Pipeline Aggregation
Pipeline Aggregation：支持对聚合分析的结果，再次进行聚合分析。
Pipeline 的分析结果会输出到原结果中，根据位置的不同，分为两类：
- Sibling：结果和现有分析结果同级
- Max，min，Avg & Sum Bucket
- Stats，Extended Status Bucket
- Percentiles Bucket
- Parent：结果内嵌到现有的聚合分析结果之中
- Derivative(求导)
- Cumultive Sum(累计求和)
- Moving Function(移动平均值 )
3.1 min_bucket示例：最小值
在员工数最多的工种里，找出平均工资最低的工种
# 平均工资最低的工种
POST employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"size": 10
},
"aggs": {
"avg_salary": {
"avg": {
"field": "salary"
}
}
}
},
"min_salary_by_job": {
"min_bucket": {
"buckets_path": "jobs>avg_salary"
}
}
}
}
- min_salary_by_jo b结果和 jobs的聚合同级
- min_bucket求之前结果的最小值
- 通过 bucket_path关键字指定路径
3.2 Stats示例：统计分析
#平均工资的统计分析
POST employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"size": 10
},
"aggs": {
"avg_salary": {
"avg": {
"field": "salary"
}
}
}
},
"stats_salary_by_job": {
"stats_bucket": {
"buckets_path": "jobs>avg_salary"
}
}
}
}
3.3 percentiles示例：百分位数
# 平均工资的百分位数
POST employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"size": 10
},
"aggs": {
"avg_salary": {
"avg": {
"field": "salary"
}
}
}
},
"percentiles_salary_by_job": {
"percentiles_bucket": {
"buckets_path": "jobs>avg_salary"
}
}
}
}
3.4 Cumulative_sum示例：累计求和
#按年龄累计求和
POST employees/_search
{
"size": 0,
"aggs": {
"age": {
"histogram": {
"field": "age",
"min_doc_count": 0,
"interval": 1
},
"aggs": {
"avg_salary": {
"avg": {
"field": "salary"
}
},
"cumulative_salary": {
"cumulative_sum": {
"buckets_path": "avg_salary"
}
}
}
}
}
}
3.5 聚合的作用范围
ES聚合分析的默认作用范围是 query的查询结果集，同时 ES还支持以下方式改变聚合的作用范围：
- Filter
- Post Filter
- Global
#Query
POST employees/_search
{
"size": 0,
"query": {
"range": {
"age": {
"gte": 20
}
}
},
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
}
}
}
}
#Filter
POST employees/_search
{
"size": 0,
"aggs": {
"older_person": {
"filter": {
"range": {
"age": {
"from": 35
}
}
},
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
}
}
}
},
"all_jobs": {
"terms": {
"field": "job.keyword"
}
}
}
}
#Post field. 一条语句，找出所有的job类型。还能找到聚合后符合条件的结果
POST employees/_search
{
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
}
}
},
"post_filter": {
"match": {
"job.keyword": "Dev Manager"
}
}
}
#global
POST employees/_search
{
"size": 0,
"query": {
"range": {
"age": {
"gte": 40
}
}
},
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword"
}
},
"all": {
"global": {},
"aggs": {
"salary_avg": {
"avg": {
"field": "salary"
}
}
}
}
}
}
3.6 排序
指定 order，按照 count和 key进行排序：
- 默认情况，按照 count降序排序
- 指定 size，就能返回相应的桶
#排序 order
#count and key
POST employees/_search
{
"size": 0,
"query": {
"range": {
"age": {
"gte": 20
}
}
},
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"order": [
{
"_count": "asc"
},
{
"_key": "desc"
}
]
}
}
}
}
#排序 order
#count and key
POST employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"order": [
{
"avg_salary": "desc"
}
]
},
"aggs": {
"avg_salary": {
"avg": {
"field": "salary"
}
}
}
}
}
}
#排序 order
#count and key
POST employees/_search
{
"size": 0,
"aggs": {
"jobs": {
"terms": {
"field": "job.keyword",
"order": [
{
"stats_salary.min": "desc"
}
]
},
"aggs": {
"stats_salary": {
"stats": {
"field": "salary"
}
}
}
}
}
}
– 求知若饥，虚心若愚。
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
2
0- 0
已为社区贡献13条内容
所有评论(0)