es 聚合查询
1，es聚合查询
0，数据
批量插入数据
POST /car_statics_index/_doc/_bulk
{ "index": {}}
{ "price" : 10000, "color" : "red", "make" : "honda", "material" : "lvban" , "level" : 2, "create_date" : "2021-03-05"}
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "material" : "tiekuai", "level" : 3 , "create_date" : "2021-03-15"}
{ "index": {}}
{ "price" : 30000, "color" : "green", "make" : "ford", "material" : "tiekuai", "level" : 3, "create_date" : "2021-04-05" }
{ "index": {}}
{ "price" : 15000, "color" : "blue", "make" : "toyota", "material" : "lvban" , "level" : 2, "create_date" : "2021-05-05"}
{ "index": {}}
{ "price" : 12000, "color" : "green", "make" : "toyota", "material" : "jinshu", "level" : 1, "create_date" : "2021-06-05" }
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "material" : "jinshu", "level" : 1, "create_date" : "2021-07-05" }
{ "index": {}}
{ "price" : 80000, "color" : "red", "make" : "bmw", "material" : "jinshu", "level" : 1, "create_date" : "2021-08-05" }
{ "index": {}}
{ "price" : 25000, "color" : "blue", "make" : "ford", "material" : "tiekuai", "level" : 3, "create_date" : "2021-09-05" }
1，常见es聚合类型：
1，metric: 常见的指标：
sum： 求和
avg： 平均值
max: 最大值
min: 最小值
value_count: 个数 相当于sql的： select count(FIELD)
stats: 相当于上面五个的
cardinality ： 去重 个数 相当于sql的： select count(distinct FIELD)
2, bucket 桶
terms: 聚合； 相当于sql的 group by 分组
bucket_script: 计算 需要在terms下面。 具体的使用看例子： terms+bucket_script，参数名称也不同
range: 范围查询。
date-range: 日期的范围，要带format字段， 具体的看range查询的例子
histogram： 直方图
date_histogram: 日期直方图，要带format字段， 具体的看range查询的例子
2，简单指标：
参数：
GET car_statics_index/_search
{
"size": 0,
"aggs": {
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
},
"avg_price": {
"avg": {
"field": "price"
}
},
"sum_price": {
"sum": {
"field": "price"
}
},
"cistinct_price": {
"cardinality": {
"field": "price"
}
}
}
}
返回值：
{
"took" : 35,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 8,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"max_price" : {
"value" : 80000.0
},
"min_price" : {
"value" : 10000.0
},
"avg_price" : {
"value" : 26500.0
},
"cistinct_price" : {
"value" : 7
},
"sum_price" : {
"value" : 212000.0
}
}
}
3，terms 按照一定规则将文档分配到不同的桶里，分类分析
1，简单**terms **
每个唯一值一个桶，返回字段的值和值的个数doc_count。 如果是text类型，则按照分词后的结果分桶
如果是字符型的，要加keyword，这个处理跟term一样
GET car_statics_index/_search
{
"size": 0,
"aggs" : {
"colors" : {
"terms" : {
"field" : "color.keyword"
}
}
}
}
返回数据格式：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "red",
"doc_count" : 3
},
{
"key" : "blue",
"doc_count" : 2
},
{
"key" : "green",
"doc_count" : 2
}
]
}
}
}
2，terms+指标：
参数
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
返回值：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "red",
"doc_count" : 3,
"avg_price" : {
"value" : 36666.666666666664
}
},
{
"key" : "blue",
"doc_count" : 2,
"avg_price" : {
"value" : 20000.0
}
},
{
"key" : "green",
"doc_count" : 2,
"avg_price" : {
"value" : 21000.0
}
}
]
}
}
}
3，terms嵌套
参数：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"make": {
"terms": {
"field": "make.keyword"
}
}
}
}
}
}
返回值：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "red",
"doc_count" : 3,
"avg_price" : {
"value" : 36666.666666666664
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "honda",
"doc_count" : 2
},
{
"key" : "bmw",
"doc_count" : 1
}
]
}
},
{
"key" : "blue",
"doc_count" : 2,
"avg_price" : {
"value" : 20000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1
},
{
"key" : "toyota",
"doc_count" : 1
}
]
}
},
{
"key" : "green",
"doc_count" : 2,
"avg_price" : {
"value" : 21000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1
},
{
"key" : "toyota",
"doc_count" : 1
}
]
}
}
]
}
}
}
4，terms嵌套再加指标
参数
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"avg_price": { "avg": { "field": "price" }
},
"make" : {
"terms" : {
"field" : "make.keyword"
},
"aggs" : {
"min_price" : { "min": { "field": "price"} },
"max_price" : { "max": { "field": "price"} }
}
}
}
}
}
}
返回值：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "red",
"doc_count" : 3,
"avg_price" : {
"value" : 36666.666666666664
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "honda",
"doc_count" : 2,
"max_price" : {
"value" : 20000.0
},
"min_price" : {
"value" : 10000.0
}
},
{
"key" : "bmw",
"doc_count" : 1,
"max_price" : {
"value" : 80000.0
},
"min_price" : {
"value" : 80000.0
}
}
]
}
},
{
"key" : "blue",
"doc_count" : 2,
"avg_price" : {
"value" : 20000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1,
"max_price" : {
"value" : 25000.0
},
"min_price" : {
"value" : 25000.0
}
},
{
"key" : "toyota",
"doc_count" : 1,
"max_price" : {
"value" : 15000.0
},
"min_price" : {
"value" : 15000.0
}
}
]
}
},
{
"key" : "green",
"doc_count" : 2,
"avg_price" : {
"value" : 21000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1,
"max_price" : {
"value" : 30000.0
},
"min_price" : {
"value" : 30000.0
}
},
{
"key" : "toyota",
"doc_count" : 1,
"max_price" : {
"value" : 12000.0
},
"min_price" : {
"value" : 12000.0
}
}
]
}
}
]
}
}
}
5，terms复合嵌套
{
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"make": {
"terms": {
"field": "make.keyword"
},
"aggs": {
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
},
"materials": {
"terms": {
"field": "material.keyword"
},
"aggs": {
"avg_level": {
"avg": {
"field": "level"
}
}
}
}
}
}
}
}
}
}
返回值：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "red",
"doc_count" : 4,
"avg_price" : {
"value" : 32500.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "honda",
"doc_count" : 3,
"max_price" : {
"value" : 20000.0
},
"min_price" : {
"value" : 10000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "jinshu",
"doc_count" : 1,
"avg_level" : {
"value" : 1.0
}
},
{
"key" : "lvban",
"doc_count" : 1,
"avg_level" : {
"value" : 2.0
}
},
{
"key" : "tiekuai",
"doc_count" : 1,
"avg_level" : {
"value" : 3.0
}
}
]
}
},
{
"key" : "bmw",
"doc_count" : 1,
"max_price" : {
"value" : 80000.0
},
"min_price" : {
"value" : 80000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "jinshu",
"doc_count" : 1,
"avg_level" : {
"value" : 1.0
}
}
]
}
}
]
}
},
{
"key" : "blue",
"doc_count" : 2,
"avg_price" : {
"value" : 20000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1,
"max_price" : {
"value" : 25000.0
},
"min_price" : {
"value" : 25000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "tiekuai",
"doc_count" : 1,
"avg_level" : {
"value" : 3.0
}
}
]
}
},
{
"key" : "toyota",
"doc_count" : 1,
"max_price" : {
"value" : 15000.0
},
"min_price" : {
"value" : 15000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "lvban",
"doc_count" : 1,
"avg_level" : {
"value" : 2.0
}
}
]
}
}
]
}
},
{
"key" : "green",
"doc_count" : 2,
"avg_price" : {
"value" : 21000.0
},
"make" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "ford",
"doc_count" : 1,
"max_price" : {
"value" : 30000.0
},
"min_price" : {
"value" : 30000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "tiekuai",
"doc_count" : 1,
"avg_level" : {
"value" : 3.0
}
}
]
}
},
{
"key" : "toyota",
"doc_count" : 1,
"max_price" : {
"value" : 12000.0
},
"min_price" : {
"value" : 12000.0
},
"materials" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "jinshu",
"doc_count" : 1,
"avg_level" : {
"value" : 1.0
}
}
]
}
}
]
}
}
]
}
}
}
6，terms+bucket_script：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"min_price": {
"value_count": {
"field": "material.keyword"
}
},
"max_price": {
"sum": {
"field": "price"
}
},
"gap_price": {
"bucket_script": {
"buckets_path": {
"deal0": "max_price",
"deal1": "min_price"
},
"script": "params.deal0 - params.deal1"
}
}
}
}
}
}
返回值：
{
"aggregations" : {
"colors" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 20000,
"doc_count" : 2,
"max_price" : {
"value" : 40000.0
},
"min_price" : {
"value" : 2
},
"deal_aggs" : {
"value" : 3333.3333333333335
}
},
{
"key" : 10000,
"doc_count" : 1,
"max_price" : {
"value" : 10000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 909.0909090909091
}
},
{
"key" : 12000,
"doc_count" : 1,
"max_price" : {
"value" : 12000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 1090.909090909091
}
},
{
"key" : 15000,
"doc_count" : 1,
"max_price" : {
"value" : 15000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 1363.6363636363637
}
},
{
"key" : 25000,
"doc_count" : 1,
"max_price" : {
"value" : 25000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 2272.7272727272725
}
},
{
"key" : 30000,
"doc_count" : 1,
"max_price" : {
"value" : 30000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 2727.2727272727275
}
},
{
"key" : 80000,
"doc_count" : 1,
"max_price" : {
"value" : 80000.0
},
"min_price" : {
"value" : 1
},
"deal_aggs" : {
"value" : 7272.727272727273
}
}
]
}
}
}
4，date-range 查询
参数：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"date_range": {
"range": {
"field": "create_date",
"format": "yyyy-MM-dd",
"ranges": [{
"from": "2021-03-01",
"to": "2021-04-10"
},{
"from": "2021-04-11",
"to": "2021-09-20"
}]
}
}
}
}
返回值：
{
"aggregations" : {
"date_range" : {
"buckets" : [
{
"key" : "2021-03-01-2021-04-10",
"from" : 1.6145568E12,
"from_as_string" : "2021-03-01",
"to" : 1.6180128E12,
"to_as_string" : "2021-04-10",
"doc_count" : 3
},
{
"key" : "2021-04-11-2021-09-20",
"from" : 1.6180992E12,
"from_as_string" : "2021-04-11",
"to" : 1.632096E12,
"to_as_string" : "2021-09-20",
"doc_count" : 5
}
]
}
}
}
5，histogram 直方图
参数：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"sales": {
"histogram": {
"field": "level",
"interval": 1,
"min_doc_count": 2
}
}
}
}
返回值：
{
"aggregations" : {
"sales" : {
"buckets" : [
{
"key" : 1.0,
"doc_count" : 3
},
{
"key" : 2.0,
"doc_count" : 2
},
{
"key" : 3.0,
"doc_count" : 3
}
]
}
}
}
6，date-histogram 日期直方图
参数：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"dates": {
"date_histogram": {
"field": "create_date",
"interval": "1M",
"format": "yyyy-MM",
"time_zone": "+08:00",
"min_doc_count": 1
}
}
}
}
返回值：
{
"aggregations" : {
"dates" : {
"buckets" : [
{
"key_as_string" : "2021-03",
"key" : 1614528000000,
"doc_count" : 2
},
{
"key_as_string" : "2021-04",
"key" : 1617206400000,
"doc_count" : 1
},
{
"key_as_string" : "2021-05",
"key" : 1619798400000,
"doc_count" : 1
},
{
"key_as_string" : "2021-06",
"key" : 1622476800000,
"doc_count" : 1
},
{
"key_as_string" : "2021-07",
"key" : 1625068800000,
"doc_count" : 1
},
{
"key_as_string" : "2021-08",
"key" : 1627747200000,
"doc_count" : 1
},
{
"key_as_string" : "2021-09",
"key" : 1630425600000,
"doc_count" : 1
}
]
}
}
}
interval 说明
| 表达式 | 含义 |
|---|---|
| 1y | 一年(数量只能是1，例如2y不合法) |
| 1q | 一个季度(数量只能是1，例如2q不合法) |
| 1M | 一个月(数量只能是1，例如2M不合法，注意区分大写，M表示月，m表示分钟) |
| 1w | 一周(数量只能是1，例如2w不合法) |
| 2d | 两天(数量可以是整数类型) |
| 3h | 三个小时(数量可以是整数类型) |
| 4m | 四分钟(数量可以是整数类型，注意区分大写，M表示月，m表示分钟) |
| 5s | 五秒钟(数量可以是整数类型) |
注意：年、季度、月、周都的数量只能是1，其他粒度的数量可以是整数；
7，百分比
对于百分比的应用，完全是懵的
参数：
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"level_percentiles": {
"percentiles": {
"field": "level"
}
}
}
}
返回值：
{
"aggregations" : {
"level_percentiles" : {
"values" : {
"1.0" : 1.0,
"5.0" : 1.0,
"25.0" : 1.0,
"50.0" : 2.0,
"75.0" : 3.0,
"95.0" : 3.0,
"99.0" : 3.0
}
}
}
}
看不懂这种分布，可能选的例子不好吧。
对于 percentile_ranks的使用，
GET /car_statics_index/_search
{
"size": 0,
"aggs": {
"level_percentiles": {
"percentile_ranks": {
"field": "level",
"values": [1,2,3]
}
}
}
}
返回值：
{
"aggregations" : {
"level_percentiles" : {
"values" : {
"1.0" : 25.0,
"2.0" : 50.0,
"3.0" : 100.0
}
}
}
}
算了，百分比的先放弃。后面有遇到再完善吧
总结：
整理下了常见的聚合查询，其它的，后面有用到，再进行补充。 对于bucket_script 固定内容，好处理。如果要动态拼接参数的话，比较麻烦，而且不灵活。数据之间的计算，使用配置，在程序里面进行处理，不直接查es。
![ElasticSearch：部署ElasticSearch & Kibana[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)