目录
聚合的分类
-
度量聚合文章源自编程技术分享-https://mervyn.life/72bfc282.html
在一组文档中队某个数字型字段进行计算得出指标值文章源自编程技术分享-https://mervyn.life/72bfc282.html
-
分组聚合文章源自编程技术分享-https://mervyn.life/72bfc282.html
创建多个分组,每个分组关联一个关键字和相关文档标准文章源自编程技术分享-https://mervyn.life/72bfc282.html
-
管道聚合文章源自编程技术分享-https://mervyn.life/72bfc282.html
这一类的聚合的数据源是其他聚合的输出,然后进行相关指标的计算文章源自编程技术分享-https://mervyn.life/72bfc282.html
度量聚合
平均值聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/student/_search?pretty' -d '
{
"size":0,
"aggs":{
"avg_score":{ //聚合的名称
"avg":{
"field":"socre"
}
}
}
}
'
最大值聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/student/_search?pretty' -d '
{
"size":0,
"aggs":{
"max_age":{
"max":{
"field":"age"
}
}
}
}
'
最小值聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/student/_search?pretty' -d '
{
"size":0,
"aggs":{
"min_socre":{
"min":{
"field":"socre"
}
}
}
}
'
百分比聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/student/_search?pretty' -d '
{
"size":0,
"aggs":{
"score":{
"percentiles":{
"field":"socre",
"percents":[50] //默认的百分比指标为[1, 5, 25, 50, 75, 95, 99]
}
}
}
}
'
返回结果:文章源自编程技术分享-https://mervyn.life/72bfc282.html
{
"took" : 12,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 10,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"score" : {
"values" : {
"1.0" : 66.63,
"5.0" : 69.15,
"25.0" : 81.0,
"50.0" : 84.5,
"75.0" : 99.0,
"95.0" : 111.0,
"99.0" : 111.0
}
}
}
}
75%的分数在66.63~99.0文章源自编程技术分享-https://mervyn.life/72bfc282.html
分组聚合
直方图聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/student/_search?pretty' -d '
{
"size":0,
"aggs":{
"score":{
"histogram":{
"field":"socre",
"interval":10,
"min_doc_count":0,
"extended_bounds":{
"min":0,
"max":150
}
}
}
}
}
'
日期直方图聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"aggs":{
"by_month":{
"date_histogram":{
"field":"ctime",
"interval":"month",
"format":"yyyy-MM-dd HH:mm:ss",
"min_doc_count":0,
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
}
}
}
}
'
时间范围聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":10,
"aggs":{
"by_month":{
"date_range":{
"field":"ctime",
"format":"yyyy-MM-dd HH:mm:ss",
"ranges":[
{"to":"now-10M/M"}, //10月内的数据
{"from":"now-10M/M"} //近10个月前的数据
]
}
}
}
}
'
范围聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/order/_search?pretty' -d '
{
"size":10,
"aggs":{
"by_month":{
"range":{
"field":"money",
"ranges":[
{"to":3036},
{"from":3036, "to": 3500},
{"from":3000}
]
}
}
}
}
'
嵌套聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/order/_search?pretty' -d '
{
"size":0,
"aggs":{
"order_lines":{
"nested":{"path":"lines"},
"aggs":{
"min_price":{"min":{"field":"lines.price"}},
"max_price":{"max":{"field":"lines.price"}}
}
}
}
}
'
curl -XGET 'http://127.0.0.1:9200/aggreation_test/order/_search?pretty' -d '
{
"size":10,
"aggs":{
"order_by_month":{
"date_histogram":{
"min_doc_count":1,
"field":"ctime",
"interval":"month",
"extended_bounds":{
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"lines":{
"nested":{
"path":"lines"
},
"aggs":{
"num_cout":{
"sum":{
"field":"lines.num"
}
},
"sum_price":{
"sum":{
"field":"lines.price"
}
}
}
}
}
}
}
}
'
管道聚合
通过buckete_path 参数指定请求指标的路径,管道聚合可以引用需要的聚合来执行计算文章源自编程技术分享-https://mervyn.life/72bfc282.html
最大分组聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/order/_search?pretty' -d '
{
"size":0,
"aggs":{
"order_by_month":{
"date_histogram":{
"min_doc_count":0,
"field":"ctime",
"interval":"month",
"extended_bounds":{
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"sum_money":{"sum":{"field":"money"} }
}
},
"max_month_sales":{
"max_bucket":{ //最大分组聚合,找出 所有月份销售总额最大值 (最小分组聚合 min_bucket)
"buckets_path":"order_by_month>sum_money"
}
}
}
}
'
总和累计聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":10,
"aggs":{
"per_month":{
"date_histogram":{
"min_doc_count":0,
"field":"ctime",
"interval":"month",
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"device_id":{
"cardinality":{ //语法
"field":"device_id" //每月独立设备数
}
},
"cumulative_device":{
"cumulative_sum":{ //语法
"buckets_path":"device_id" //按月累计设备数
}
}
}
}
}
}
'
差值聚合
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":10,
"aggs":{
"by_month":{
"date_histogram":{ //这个为固定语法
"min_doc_count":0, //参数会强制返回空桶
"field":"ctime",
"interval":"month",
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"home":{
"cardinality":{ //去重近似值
"field":"home_id"
}
},
"home_deriv":{
"derivative":{ //去重近似值
"buckets_path":"home"
}
}
}
}
}
}'
不同场景下的聚合应用
下面主要以不同场景下的应用来介绍ES聚合的情况。假设现在有一张设备事件表如下结构:文章源自编程技术分享-https://mervyn.life/72bfc282.html
CREATE TABLE `device_events ` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '# 事件ID',
`identifier` tinyint(4) NOT NULL COMMENT '# 事件标识',
`type` tinyint(4) NOT NULL COMMENT '# 事件类型',
`device_id` int(11) NOT NULL COMMENT '# 设备ID',
`home_id` int(11) NOT NULL COMMENT '# 家庭ID',
`product_id` int(11) NOT NULL COMMENT '# 设备对应的产品ID',
`user_id` int(11) NOT NULL COMMENT '# 用户ID',
`params` int(11) NOT NULL COMMENT '# 设备上报的参数,如:{"success":1}',
`ctime` datetime NOT NULL COMMENT '# 事件上报的时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8 COMMENT='设备事件表';
对应ES结构如下:文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -u elastic:changeme -XPOST 'http://127.0.0.1:9200/aggreation_test/device_events' -d'
{
"mappings": {
"demo": {
"properties": {
"identifier": {
"type": "integer"
},
"event_id": {
"type": "integer"
},
"type": {
"type": "integer"
},
"device_id": {
"type": "integer"
},
"ctime": {
"type": "date",
"format":"yyyy-MM-dd HH:mm:ss"
},
"home_id": {
"type": "integer"
},
"product_id": {
"type": "integer"
},
"user_id": {
"type": "integer"
},
"params": {
"type": "object",
"properties":{
"success":{
"type":"integer"
}
}
}
}
}
}
}'
这里注意params的类型,ES里两个比较特殊的类型 object
和 nested
文章源自编程技术分享-https://mervyn.life/72bfc282.html
object
为对象数据类型,单独的JSON对象文章源自编程技术分享-https://mervyn.life/72bfc282.html
nested
为嵌套数据类型,关于JSON对象的数组文章源自编程技术分享-https://mervyn.life/72bfc282.html
通过调用SDK批量插入一批数据,即可开始进行聚合测试。文章源自编程技术分享-https://mervyn.life/72bfc282.html
统计每个事件类型的数量文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2016-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
}
]
}
},
"aggs":{
"type_percent":{
"histogram":{
"min_doc_count":0, //最少要有0个文档
"field":"type",
"interval":"1"
}
}
}
}
'
以月为单位统计每月的事件消息数,独立设备数,独立家庭数。文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2017-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
}
]
}
},
"aggs":{
"by_month":{
"date_histogram":{ //这个为固定语法
"min_doc_count":0, //参数会强制返回空桶
"field":"ctime",
"interval":"month",
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"device_id":{
"cardinality":{ //去重近似值
"field":"device_id"
}
},
"home_id":{
"cardinality":{
"field":"home_id"
}
}
}
}
}
}'
以月为单位统计每月独立家庭数的差值文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2017-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
}
]
}
},
"aggs":{
"by_month":{
"date_histogram":{ //这个为固定语法
"min_doc_count":0, //参数会强制返回空桶
"field":"ctime",
"interval":"month",
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"home":{
"cardinality":{ //去重近似值
"field":"home_id"
}
},
"home_deriv":{
"derivative":{ //去重近似值
"buckets_path":"home"
}
}
}
}
}
}'
param参数success为1的统计文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":10,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2016-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
},
{
"term":{
"params.success":2
}
}
]
}
},
"aggs":{
"device_id":{
"cardinality":{
"field":"device_id"
}
},
"home_id":{
"cardinality":{
"field":"home_id"
}
}
}
}'
以月为单位计算每月累计独立设备数文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2016-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
}
]
}
},
"aggs":{
"per_month":{
"date_histogram":{
"min_doc_count":0,
"field":"ctime",
"interval":"month",
"extended_bounds":{ //会强制返回一整年的数据
"min":"2017-01-01 00:00:00",
"max":"2017-12-31 00:00:00"
}
},
"aggs":{
"device_id":{
"cardinality":{ //语法
"field":"device_id"
}
},
"cumulative_device":{
"cumulative_sum":{ //语法
"buckets_path":"device_id"
}
}
}
}
}
}
'
统计参数success不同值的数量文章源自编程技术分享-https://mervyn.life/72bfc282.html
curl -XGET 'http://127.0.0.1:9200/aggreation_test/device_events/_search?pretty' -d '
{
"size":0,
"query":{
"bool":{
"must":[
{
"range":{
"ctime":{
"gt":"2016-01-01 00:00:00",
"lt":"2018-08-01 00:00:00"
}
}
}
]
}
},
"aggs":{
"type_percent":{
"histogram":{
"min_doc_count":0,
"field":"params.success",
"interval":"1"
}
}
}
}
'
文章源自编程技术分享-https://mervyn.life/72bfc282.html
评论