SELECT COUNT(color) FROM table GROUP BY color
桶 简单来说就是满足特定条件的文档的集合:
桶能让我们划分文档到有意义的集合, 但有意义的是对这些桶内的文档进行一些指标的计算。
分桶是一种达到目的的手段:它提供了一种给文档分组的方法来让我们可以计算感兴趣的指标。
大多数 指标 是简单的数学运算(例如最小值、平均值、最大值,还有汇总)
标签
作为桶
, 比如:{
“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
例子:展示&点击之间关系
{ "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" } } } } } }