文章目录
聚合分组
select * from table group by title.keyword
{
"size": 0,
"aggs": {
"group_name": {
"terms": {
"field": "title.keyword"
}
}
}
}
解释:
size:0 表示只展示聚合结果,不展示原始数据
aggs:表示聚合的操作符
group_name:给聚合操作取名
terms:根据字段的值进行分组
field:根据指定的字段值进行分组
求和
按照title分组求和
select sum(price) from table group by title.
{
"size": 0,
"aggs": {
"group_name": {
"terms": {
"field": "title.keyword"
},
"aggs":{
"sum_price":{
"sum":{
"field":"price"
}
}
}
}
}
}
平均值
select avg(price) from table group by title.
{
"size": 0,
"aggs": {
"group_name": {
"terms": {
"field": "title.keyword"
},
"aggs":{
"avg_price":{
"avg":{
"field":"price"
}
}
}
}
}
}
分析每种颜色下每种品牌的平均价格
{
"size": 0,
"aggs": {
"group_name": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"group_by_brand": {
"terms": {
"field": "brand.keyword"
},
"aggs": {
"avg_price_by_color": {
"avg": {
"field": "price"
}
}
}
}
}
}
}
}
更多的metric学习
{
"size": 0,
"aggs": {
"group_name": {
"terms": {
"field": "color.keyword"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"sum_price": {
"sum": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
},
"min_max": {
"min": {
"field": "price"
}
}
}
}
}
}
输出:
"took": 66,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 3,
"relation": "eq"
},
"max_score": null,
"hits": [
]
},
"aggregations": {
"group_name": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "white",
"doc_count": 2,
"max_price": {
"value": 5500
},
"min_max": {
"value": 4500
},
"avg_price": {
"value": 5000
},
"sum_price": {
"value": 10000
}
},
{
"key": "blue",
"doc_count": 1,
"max_price": {
"value": 4000
},
"min_max": {
"value": 4000
},
"avg_price": {
"value": 4000
},
"sum_price": {
"value": 4000
}
}
]
}
}
}
一般来说,90%的常见的数据分析的操作,metric,无非就是count,avg,max,min,sum
Cardinality(唯一值)
cardinality 即去重计算,类似sql中 count(distinct),先去重再求和,计算指定field值的种类数。
{
"size": 0,
"aggs": {
"cartinality_gender": {
"cardinality": {
"field": "city.keyword"
}
}
}
}
输出
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 15,
"max_score": 0,
"hits": []
},
"aggregations": {
"cartinality_gender": {
"value": 15
}
}
}```
## stats 一个聚合,输出多值
```java
{
"size": 0,
"aggs": {
"stats_price": {
"stats": {
"field": "price"
}
}
}
}
{
"took": 3,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 3,
"relation": "eq"
},
"max_score": null,
"hits": [
]
},
"aggregations": {
"stats_price": {
"count": 3,
"min": 4000,
"max": 5500,
"avg": 4666.666666666667,
"sum": 14000
}
}
}
查询+聚合分析
{
"query":{
"match":{
"title":"神州"
}
},
"aggs":{
"sum_price":{
"sum":{
"field":"price"
}
}
}
}
输出
"took": 54,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 2,
"relation": "eq"
},
"max_score": 0.9983525,
"hits": [
{
"_index": "aggs_index",
"_type": "_doc",
"_id": "5",
"_score": 0.9983525,
"_source": {
"brand": "huashuo",
"color": "white",
"price": 5500,
"title": "神州"
}
},
{
"_index": "aggs_index",
"_type": "_doc",
"_id": "6",
"_score": 0.8416345,
"_source": {
"brand": "dell",
"color": "white",
"price": 4500,
"title": "神州dell"
}
}
]
},
"aggregations": {
"sum_price": {
"value": 10000
}
}
}
查询聚合+全局聚合 深入聚合数据分析_global bucket:单个品牌与所有品牌销量对比
global:就是global bucket,就是将所有数据纳入聚合的scope,而不管之前的query
{
"query": {
"match": {
"title": "神州"
}
},
"aggs": {
"sum_price": {
"sum": {
"field": "price"
}
},
"all": {
"global": {},
"aggs": {
"all_sum_price": {
"sum": {
"field": "price"
}
}
}
}
}
}
输出
{
"query": {
"match": {
"title": "神州"
}
},
"aggs": {
"sum_price": {
"sum": {
"field": "price"
}
},
"all": {
"global": {},
"aggs": {
"all_sum_price": {
"sum": {
"field": "price"
}
}
}
}
}
}
过滤+聚合:统计价格大于1200的电视平均价格
{
"size": 0,
"query": {
"constant_score": {
"filter": {
"range": {
"price": {
"gte": 1200
}
}
}
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
统计最近一个月的平均价格
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "长虹"
}
}
},
"aggs": {
"recent_150d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-150d"
}
}
},
"aggs": {
"recent_150d_avg_price": {
"avg": {
"field": "price"
}
}
}
},
"recent_140d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-140d"
}
}
},
"aggs": {
"recent_140d_avg_price": {
"avg": {
"field": "price"
}
}
}
},
"recent_130d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-130d"
}
}
},
"aggs": {
"recent_130d_avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
按照每种品牌的平均价格排序
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "brand.keyword",
"order":{
"avg_price":"desc"
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}