一、聚合分析简介
聚合分析是数据库中重要的功能特性，完成对一个查询的数据集中数据的聚合计算，如：找出某字段（或计算表达式的结果）的最大值、最小值，计算和、平均值等。ES作为搜索引擎兼数据库，同样提供了强大的聚合分析能力。
对一个数据集求最大、最小、和、平均值等指标的聚合，在ES中称为指标聚合 metric
而关系型数据库中除了有聚合函数外，还可以对查询出的数据进行分组group by，再在组上进行指标聚合。在 ES 中group by 称为分桶，桶聚合 bucketing。
ES中还提供了矩阵聚合（matrix）、管道聚合（pipleline），但还在完善中。
聚合分析的值来源：
聚合计算的值可以取字段的值，也可是脚本计算的结果。
二、指标聚合
- 查找价格最高的商品
GET /goods_index/goods_type/_search
{
"size": 0,
"aggs": {
"masssbalance": {
"max": {
"field": "sell_price"
}
}
}
}
- 查找价格最低的商品
GET /goods_index/goods_type/_search
{
"size": 0,
"aggs": {
"masssbalance": {
"min": {
"field": "sell_price"
}
}
}
}
- 查找所有商品和
GET /goods_index/goods_type/_search
{
"size": 0,
"aggs": {
"masssbalance": {
"sum": {
"field": "sell_price"
}
}
}
}
- 查询商品平均价
GET /goods_index/goods_type/_search
{
"size": 0,
"aggs": {
"masssbalance": {
"avg": {
"field": "sell_price"
}
}
}
}
- 文档计数 count
统计商品价格大于500的文档数量
GET /goods_index/goods_type/_count
{
"query": {
"bool": {
"filter": {
"range": {
"sell_price": {
"gte": 10
}
}
}
}
}
}
- Value count 统计某字段有值的文档数
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"sell_count": {
"value_count": {
"field": "sell_price"
}
}
}
}
结果:
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"sell_count": {
"value": 7
}
}
}
- cardinality 值去重计数
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"sell_count": {
"cardinality": {
"field": "sell_price"
}
}
}
}
结果:
{
"took": 6,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"sell_count": {
"value": 6
}
}
}
8.stats 统计 count max min avg sum 5个值
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"sell_stats": {
"stats": {
"field": "sell_price"
}
}
}
}
结果:
{
"took": 17,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"sell_stats": {
"count": 7,
"min": 398,
"max": 980,
"avg": 692.1428571428571,
"sum": 4845
}
}
}
- Extended stats
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"sell_stats": {
"extended_stats": {
"field": "sell_price"
}
}
}
}
结果：
{
"took": 1,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"sell_stats": {
"count": 7,
"min": 398,
"max": 980,
"avg": 692.1428571428571,
"sum": 4845,
"sum_of_squares": 3565461,
"variance": 30289.836734693898,
"std_deviation": 174.03975619005533,
"std_deviation_bounds": {
"upper": 1040.2223695229677,
"lower": 344.06334476274645
}
}
}
}
- Percentiles 占比百分位对应的值统计
对指定字段（脚本）的值按从小到大累计每个值对应的文档数的占比（占所有命中文档数的百分比），返回指定占比比例对应的值。默认返回[ 1, 5, 25, 50, 75, 95, 99 ]分位上的值。如下中间的结果，可以理解为：占比为50%的文档的sell_price值 <= 696，或反过来：sell_price<=696的文档数占总命中文档数的50%。
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"age_percents": {
"percentiles": {
"field": "sell_price"
}
}
}
}
结果：
{
"took": 34,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"age_percents": {
"values": {
"1.0": 398,
"5.0": 398,
"25.0": 603.75,
"50.0": 696,
"75.0": 819,
"95.0": 980,
"99.0": 980
}
}
}
}
指定分位值
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"age_percents": {
"percentiles": {
"field": "sell_price",
"percents" : [95, 99, 99.9]
}
}
}
}
结果：
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"age_percents": {
"values": {
"95.0": 980,
"99.0": 980,
"99.9": 980
}
}
}
}
- Percentiles rank 统计值小于等于指定值的文档占比
统计年龄小于800和500的文档的占比
GET /goods_index/goods_type/_search?size=0
{
"aggs": {
"gge_perc_rank": {
"percentile_ranks": {
"field": "sell_price",
"values": [
500,
800
]
}
}
}
}
结果：
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"gge_perc_rank": {
"values": {
"500.0": 14.417379855167873,
"800.0": 73.64185110663985
}
}
}
}
一、计算每个tag下的商品数量
image.png
将文本field的fielddata属性设置为true
image.png
image.png
二、对名称中包含yagao的商品，计算每个tag下的商品数量
image.png
三、先分组，再算每组的平均值，计算每个tag下的商品的平均价格
image.png
四、计算每个tag下的商品的平均价格，并且按照平均价格降序排序
image.png
五、按照指定的价格范围区间进行分组，然后在每组内再按照tag进行分组，最后再计算每组的平均价格
新增一个商品便于分析
PUT ecommerce/product/4
{
"name": "shiwang yagao",
"desc": "gaoxiao meibai fangzhu",
"price": 30,
"producer": "shiwang producer",
"tags": [
"meibai",
"fangzhu"
]
}
image.png
