有表t,DDL如下:
drop table if exists t; create table if not exists t( id int primary key auto_increment, name varchar(32) not null, salary int not null, city varchar(16) not null )engine=innodb default charset=utf8mb4;
在对应的t_index索引中实现类似如下操作:
select max(salary), min(salary), avg(salary), sum(salary) from t group by city;
ES中的聚合分为4类:Metrics Aggregation、Bucket Aggregation、Pipeline Aggregation、Matrix Aggregation,都可同时操作多个索引,下面以仅操作单个索引讲解。
一、Metrics Aggregation
Avg Aggregation、Max Aggregation、Min Aggregation、Sum Aggregation
get /t_index/_search
{
"size":0,
"aggs":{
"avg_id":{
"avg":{
"field":"id"
}
}
}
}
等同于select avg(id) from t;
aggs是关键字,是aggregations的缩写,avg_id是我们给聚合结果起的名字,avg是关键字,表示取平均值,field是关键字,表示根据某个字段聚合,id表示根据id字段。
如果要执行select avg(id) from t where id < 10呢,只需添加query就好了。
GET /t_index/_search
{
"query" : {
"range": {
"id": {
"gte": 0,
"lt": 10
}
}
},
"size" : 0,
"aggs" : {
"avg_id" : {
"avg": {
"field" : "id"
}
}
}
}
field还可以换成script,表示根据脚本聚合。
get /t_index/_search
{
"size":0,
"aggs":{
"avg_id":{
"avg":{
"script":{
"lang":"painless",
"source":"doc.name.value.length()"
}
}
}
}
}
等同于select avg(char_length(name)) from t;
get /t_index/_search
{
"size":1,
"aggs":{
"max_id":{
"max": {
"field": "id"
}
}
}
}
等同于select max(id) from t;
同样,也可以根据脚本聚合。
get /t_index/_search
{
"size":1,
"aggs":{
"min_name_length":{
"min": {
"script": {
"lang": "painless",
"source": "doc.name.value.length()"
}
}
}
}
}
等同于select min(char_length(name)) from t;
get /t_index/_search
{
"size":1,
"aggs":{
"sum_id":{
"sum": {
"field": "id"
}
}
}
}
等同于select sum(id) from t;
Value_count Aggregation
get /t_index/_search
{
"size":1,
"aggs":{
"types_count":{
"value_count":{
"field":"id"
}
}
}
}
等同于select count(id) from t;
Stats Aggregation
一次性返回count、min、max、avg、sum
get /t_index/_search
{
"size":0,
"aggs":{
"id_stats":{
"stats": {
"field": "id"
}
}
}
}
等同于select count(id), min(id), max(id), avg(id), sum(id) from t;
返回如下:
get /t_index/_search
{
"aggregations":{
"id_stats":{
"count":11524,
"min":1,
"max":3122,
"avg":1538.1006594932314,
"sum":17725072
}
}
}
Extended_stats Aggregation
增强型的Stats Aggregation,不仅会返回count、min、max、avg、sum,还会返回sum_of_squares、variance、std_deviation、std_deviation_bounds。
sum_of_squares表示平方和,variance表示方差,std_deviation表示标准差。
get /t_index/_search
{
"size":0,
"aggs":{
"id_extended_stats":{
"extended_stats":{
"field":"id"
}
}
}
}
返回如下:
{
"aggregations":{
"id_extended_stats":{
"count":11524,
"min":1,
"max":3122,
"avg":1538.1006594932314,
"sum":17725072,
"sum_of_squares":37053374752,
"variance":849568.7104507973,
"std_deviation":921.7205164532237,
"std_deviation_bounds":{
"upper":3381.541692399679,
"lower":-305.34037341321596
}
}
}
}
Cardinality Aggregation
get /t_index/_search
{
"size":0,
"aggs":{
"count_distinct_name":{
"cardinality":{
"field":"name"
}
}
}
}
等同于select count(distinct name) from t;
cardinality意思是基数,指的是一个集合去重后元素的个数。同selct count(distinct xxx)不同的是,cardinality aggregation的值只是一个近似值,不保证完全准确。cardinality aggregation提供一个precision_threshold参数,precision_threshold越大,聚合操作占用的内存越多,聚合结果也越准确。precision_threshold默认是3000,最大支持40000。
get /t_index/_search
{
"size":0,
"aggs":{
"count_distinct_name":{
"cardinality":{
"field":"name",
"precision_threshold": 5000
}
}
}
}
cardinality底层用的是HyperLogLog算法。这个算法很有意思,有空可以研究下。
在redis中也有对应实现,pfadd、pfcount、pfmerge这三个命令底层都用了HyperLogLog结构。pfadd往一个集合中放数据,pfadd返回这个集合的基数,pfmerge可以合并多个集合。
pfadd hll a b c d e f a b
pfcount hll,返回6
pfadd hll2 c d e g h
pfcount hll2,返回5
pfmerge hll3 hll hll2
pfcount hll3,返回8
二、Bucket Aggregation
Terms Aggregation
get /t_index/_search
{
"size":0,
"aggs":{
"id_group":{
"terms":{
"field":"id"
}
}
}
}
等同于select name, count(1) from t group by name order by count(1) desc limit 10;
默认取top10,然后把top10之后的文档的总数量赋值给sum_other_doc_count。我们可以通过size参数来控制取top多少。
返回:
{
"aggregations":{
"genres":{
"doc_count_error_upper_bound":0,
"sum_other_doc_count":9,
"buckets":[
{
"key":'A',
"doc_count":6
},
{
"key":'B',
"doc_count":6
},
{
"key":'G',
"doc_count":5
},
{
"key":'D',
"doc_count":4
},
{
"key":'F',
"doc_count":3
},
{
"key":'E',
"doc_count":3
},
{
"key":'C',
"doc_count":3
},
{
"key":'H',
"doc_count":3
},
{
"key":'M',
"doc_count":2
},
{
"key":'J',
"doc_count":2
}
]
}
}
}
也可以根据script来分组。如下
get /t_index/_search
{
"size":0,
"aggs":{
"name_length_group":{
"terms":{
"script":"doc.name.value.length()"
}
}
}
}
等同于select char_length(name), count(1) from t group by char_length(name) order by count(1) desc limit 10;
还可以一次对多个字段进行分组,但是这里的多字段分组并不是group by id, name这种意思,而是在根据id分组,取其topN的同时,又根据name分组,取其topM。如下:
get /t_index/_search
{
"size":0,
"aggs":{
"id_group":{
"terms":{
"field":"id",
"size":5
}
},
"name_group":{
"terms":{
"field":"name",
"size":15
}
}
}
}
等同于
select id, count(1) from t group by id order by count(1) desc limit 5;赋值给id_group
select name, count(1) from t group by name order by count(1) desc limit 15;赋值给name_group
默认是根据doc_count倒序排列的,我们还可以用order参数来自定义排列顺序,如根据doc_count正序,根据分组字段排序,甚至还可以根据其他聚合值排序,如下
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"name",
"size":10000,
"order":{
"_count":"asc"
}
}
}
}
}
等同于select name, count(1) from t group by name order by count(1) limit 10000;
_count表示根据doc_count排序,_key表示根据分组字段排序。
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"name",
"size":10000,
"order":{
"_key":"asc"
}
}
}
}
}
等同于select name, count(1) from t group by name order by name limit 10000;
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"name",
"order":{
"max_id":"desc"
}
},
"aggs":{
"max_id":{
"max":{
"field":"id"
}
}
}
}
}
}
用于排序的聚合也可以是stats aggregation或者extended_stats aggregation,如下:
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"name",
"order":{
"id_stats.min":"desc"
}
},
"aggs":{
"id_stats":{
"stats":{
"field":"id"
}
}
}
}
}
}
用min_doc_count参数实现having的效果:
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"name",
"shard_size":10000,
"min_doc_count":400,
"order":{
"_count":"asc"
}
}
}
}
}
等同于select name, count(1) from t group by name having count(1) >= 400 order by count(1) asc limit 10;
如果想在分组字段上加一些where条件,可以用include参数、exclude参数,如下
get /t_index/_search
{
"size":0,
"aggs":{
"names":{
"terms":{
"field":"name",
"include":"球蛋白",
"shard_size":1000
}
}
}
}
等同于select name, count(1) from t where name = '球蛋白' group by name order by count(1) desc limit 10;
get /t_index/_search
{
"size":0,
"aggs":{
"names":{
"terms":{
"field":"name",
"include":["球蛋白", "白蛋白"],
"shard_size":1000
}
}
}
}
等同于select name, count(1) from t where name in ('球蛋白', '白蛋白') group by name order by count(1) desc limit 10;
get /t_index/_search
{
"size":0,
"aggs":{
"names":{
"terms":{
"field":"name",
"include":".*蛋白.*",
"exclude":"组织.*",
"shard_size":1000
}
}
}
}
等同于select name, count(1) from t where name like '%蛋白%' and name not like '组织%' group by name order by count(1) desc limit 10;
底层原理:索引的各主分片或其副本分片会先在本地分组,然后协调节点会根据doc_count抽取前shard_size的文档,汇总后,返回top size。
如果只有一个主分片,那么shard_size值默认等于size,否则,shard_size值默认是1.5 * size + 10。当shard_size值比分组字段的基数小时,top size的key和doc_count都不一定准确。当返回的doc_count_error_upper_bound不等于0时,就表示有的doc_count不准了。
get /t_index/_search
{
"size":0,
"aggs":{
"genres":{
"terms":{
"field":"id",
"size":10,
"shard_size":100000
}
}
}
}
为什么doc_count会不准呢?
假设我们有一个有3个主分片的索引,第一个主分片上有6个A、5个B、4个C、3个D、2个E、1个F,第二个主分片上也是有6个A、5个B、4个C、3个D、2个E、1个F,第三个分片上数据稍微有点不一样,有6个A、5个B、4个C、3个D、2个F、1个E。现在取top5,那么协调节点会从第一个主分片上取前5名,即6个A、5个B、4个C、3个D、2个E,从第二个分片上取前5名,即6个A、5个B、4个C、3个D、2个E,从第三个分片上取前5名,即6个A、5个B、4个C、3个D、2个F,文档汇总后,得出有18个A、15个B、12个C、9个D、4个E、2个F,返回前5名,即A 18个,B 15个,C 12个,D 9个,E 4个。很明显,E的数量是不对的,因为在第三个分片上,E排不上前5,从而没有被抽取到协调节点上,但是在前两个分片上的数量就已经使其排到了前5。
为什么key会不准呢?
还是以一个有3个主分片的索引举例,第一个主分片上有20个A、19个B、18个C、17个D、16个E、15个P,第二个主分片上有15个F、14个G、13个H、12个I、11个J、10个P,第三个主分片上有10个K、9个L、8个M、7个N、6个O、5个P。现在取top5,那么协调节点会从第一个主分片上取前5名,即20个A、19个B、18个C、17个D、16个E,从第二个分片上取前5名,即15个F、14个G、13个H、12个I、11个J,从第三个分片上取前5名,即10个K、9个L、8个M、7个N、6个O,文档汇总后返回top5,即A 20个,B 19个,C 18个,D 7个,E 16个。很明显,top5不对,因为P在三个分片上的总数量是30,理应排在第一名的,但是现在却名落孙山。
如果要terms aggregation的结果准确,第一种方式是把所有文档都routing到一个主分片上,第二种方式是聚合时shard_size设置大一点,超过分组字段值基数就没毛病了,但这也意味着给es更大的压力。
Composite Aggregation
composite意思是复合。