Elasticsearch aggs 聚合

桶和指标

SELECT COUNT(color)
FROM table
GROUP BY color

COUNT(color) 相当于指标。GROUP BY color 相当于桶.

桶简单来说就是满足特定条件的文档的集合：

一个雇员属于男性桶或者女性桶奥尔巴尼属于纽约桶日期2014-10-28属于十月桶

桶能让我们划分文档到有意义的集合，但有意义的是对这些桶内的文档进行一些指标的计算。

分桶是一种达到目的的手段：它提供了一种给文档分组的方法来让我们可以计算感兴趣的指标。

大多数指标是简单的数学运算（例如最小值、平均值、最大值，还有汇总）

尝试聚合

以每个标签作为桶, 比如:

{
“size”: 0,
“aggs”: {
“tag”: {
“terms”: {
“field”: “tag_id”
}
}
}
}

添加指标每个标签的总消耗

{
"size": 0,
"aggs": {
"tag": {
"terms": {
"field": "tag_id"
},
"aggs": {
"total_cost": {
"sum": {
"field": "cost"
}
}
}
}
}}

标签消耗中，嵌套桶每个设计师拥有素材

{
"size": 0,
"aggs": {
"tag": {
"terms": {
"field": "tag_id"
},
"aggs": {
"total_cost": {
"sum": {
"field": "cost"
}
},
"designer": {
"terms": {
"field": "designer_id"
}
}
}
}
}}

标签消耗中，嵌套桶每个设计师最大、二手买卖最小消耗

{
"size": 0,
"aggs": {
"tag": {
"terms": {
"field": "tag_id"
},
"aggs": {
"total_cost": {
"sum": {
"field": "cost"
}
},
"designer": {
"terms": {
"field": "designer_id"
},
"aggs": {
"avg_cost": {
"avg": { "field": "cost" }
},
"max_cost": {
"max": { "field": "cost" }
}
}
}
}
}
}}

条形图

date_histogram（和histogram一样）默认只会返回文档数目非零的 buckets

基础条形图

histogram 桶要求两个参数：一个数值字段以及一个定义桶大小间隔。sum 度量嵌套在每个售价区间内，用来显示每个区间内的总收入。

例子：展示&点击之间关系

{
"size" : 0,
"aggs":{
"show":{
"histogram":{
"field": "show",
"interval": 1000
},
"aggs":{
"total_click": {
"sum": {
"field" : "click"
}
}
}
}
}}

按时间统计

每月素材多少展示？

{
"size" : 0,
"aggs": {
"show": {
"date_histogram": {
"field": "show",
"interval": "month",
"format": "yyyy-MM-dd"
}
}
}}

buckets 中没有文档也返回，需要设置两个参数来实现效果：

{
"size" : 0,
"aggs": {
"show": {
"date_histogram": {
"field": "show",
"interval": "month",
"format": "yyyy-MM-dd",
"min_doc_count" : 0,
"extended_bounds" : {
"min" : "2019-01-01",
"max" : "2019-12-31"
}
}
}
}}
min_doc_count这个参数强制返回空 buckets。extended_bounds这个参数强制返回整年。 // 存疑

// 作为例子，我们构建聚合以便按季度展示所有汽车品牌总销售额。同时按季度、按每个汽车品牌计算销售总额，以便可以找出哪种品牌最赚钱：

{
"size" : 0,
"aggs": {
"sales": {
"date_histogram": {
"field": "sold",
"interval": "quarter",
"format": "yyyy-MM-dd",
"min_doc_count" : 0
},
"aggs": {
"per_make_sum": {
"terms": { "field": "make" },
"aggs": {
"sum_price": { "sum": { "field": "price" } }
}
},
"total_sum": {
"sum": { "field": "price" }
}
}
}
}}

范围限定的聚合编辑

id = 259的设计师素材展示&消耗

{
"query" : {
"match" : { "designer_id" : "259" }
},
"size":0,
"aggs" : {
"avg_show": { "avg": { "field": "show" } },
"avg_cost": { "avg": { "field": "cost" } }
}}

id = 259的设计师素材展示&消耗与全部素材做对比

{
"query" : {
"match" : {
"designer_id" : "259"
}
},
"size":0,
"aggs" : {
"avg_show": {
"avg": { "field": "show" }
},
"avg_cost": {
"avg": { "field": "cost" }
},
"all": {
"global" : {},
"aggs" : {
"all_avg_show": {
"avg": { "field": "show" }
},
"all_avg_cost": {
"avg": { "field": "cost" }
}
}
}
}}

聚合过滤

消耗大于 1000 的素材平均消耗

{
"size" : 0,
"query" : {
"constant_score": {
"filter": {
"range": {
"cost": { "gte": 1000 }
}
}
}
},
"aggs" : {
"single_avg_cost": { "avg" : { "field" : "cost" } },
"per_designer_sum": {
"terms": { "field": "designer_id" },
"aggs":{
"single_avg_cost": { "avg" : { "field" : "cost" } },
"single_avg_show": { "avg" : { "field" : "show" } }
}
}
}}

聚合计算

{
"size": 0,
"aggs": {
"tag_aggs": {
"terms": {
"field": "tag_id"
},
"aggs": {
"sum_cost": {
"sum": {
"field": "cost"
}
},
"sum_show": {
"sum": {
"field": "show"
}
},
"cpm": {
"bucket_script": {
"buckets_path": {
"total_cost": "sum_cost",
"total_show": "sum_show"
},
"script": "( params.total_cost / params.total_show ) * 1000"
}
}
}
}
}}

更多相关文章

随机推荐