# 关于es聚合查询指标过滤并限制返回结果数量的问题

**URL:** <https://discuss.elastic.co/t/es/237379>\
**Category:** 中文提问与讨论\
**Tags:** docker, ilm-index-lifecycle-management\
**Created:** [June 17, 2020, 1:34am UTC](https://discuss.elastic.co/t/es/237379 "2020-06-17T01:34:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![NiFeng](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@NiFeng](https://discuss.elastic.co/u/NiFeng)\
**Post date:** [June 17, 2020, 1:34am UTC](https://discuss.elastic.co/t/es/237379/1 "2020-06-17T01:34:02Z")

</div>

{  
"size": 0,  
"query": {  
"bool": {  
"filter":   
}  
},  
"track\_total\_hits": false,  
"aggregations": {  
"my\_buckets": {  
"composite": {  
"size": 5,  
"sources": [  
{  
"eventTime.keyword": {  
"terms": {  
"field": "eventTime.keyword",  
"missing": ""  
}  
}  
},  
{  
"model.keyword": {  
"terms": {  
"field": "model.keyword",  
"missing": ""  
}  
}  
},  
{  
"aaid.keyword": {  
"terms": {  
"field": "aaid.keyword",  
"missing": ""  
}  
}  
},  
{  
"1f82b8a0": {  
"sum": {  
"field": "dataType"  
}  
}  
}  
]  
},  
"aggs": {  
"1f82b8a0": {  
"sum": {  
"field": "dataType"  
}  
},  
"111df5af": {  
"sum": {  
"field": "appId"  
}  
},  
"u1Mdpy-4niN85-hUx47A\_bucket": {  
"bucket\_selector": {  
"buckets\_path": {  
"a04aa8832": "1f82b8a0"  
},  
"script": {  
"source": "params.a04aa8832\>=5"  
}  
}  
},  
"sort\_bucket": {  
"bucket\_sort": {  
"sort": [  
{  
"1f82b8a0": {  
"order": "desc"  
}  
}  
]  
}  
}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![NiFeng](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@NiFeng](https://discuss.elastic.co/u/NiFeng)\
**Post date:** [June 17, 2020, 1:34am UTC](https://discuss.elastic.co/t/es/237379/2 "2020-06-17T01:34:31Z")

</div>

我想实现类似sql中聚合查询having过滤，并限制返回结果的数量。可是并没有找到适合的查询方式。要么是返回数据量不好控制，要么是返回结果不合人意。恳请诸位大佬指点迷津！感谢！以上是我的采用过的不符合的查询方式之一。

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [June 24, 2020, 2:49am UTC](https://discuss.elastic.co/t/es/237379/3 "2020-06-24T02:49:45Z")

</div>

不清楚你的具体需求，可以看一下这个有没有帮助：

> <https://stackoverflow.com/questions/46908360/sql-like-group-by-and-having/46953947#46953947>

---

<div class="post-metadata">

**Author:** ![NiFeng](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@NiFeng](https://discuss.elastic.co/u/NiFeng)\
**Post date:** [June 28, 2020, 2:14pm UTC](https://discuss.elastic.co/t/es/237379/5 "2020-06-28T14:14:45Z")

</div>

首先，谢谢您百忙之中的回复！

```
 但是这个好像不能解决我的问题。我想实现这样的SQL:

```

select colum1,column2,column3,column4,sum(column5),avg(column6)  
from table  
group by column1,column2,column3,column4 having sum(column5) \> 500 and avg(column6) \>1000 limit 1000

```
  如果按照你推荐文档，确实可以实现聚合。但是在表数据量很大的情况下，难以控制聚合结果的数据量。

  即使对每个aggs的纬度字段都设置size，也不能准确的控制聚合结果的数量。

  后来，我采用了复合聚合composite,但是它对bucket_selector的兼容性好像不是很理想。

  复合聚合使用bucket_selector只是对查询结果在内存中过滤，在限制数据量的情况下，发现有空桶占位的现象。

  所以，我不知道是es暂时不支持我这样的需求，还是我对es不够熟悉。希望大佬能够提供下帮助。

 谢谢！期待您的回复！
```

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [June 29, 2020, 4:44am UTC](https://discuss.elastic.co/t/es/237379/6 "2020-06-29T04:44:20Z")

</div>

> [@NiFeng](#):
>
> `bucket_selector`

是的，pipeline aggregation 和 composite aggregation不兼容。

> 即使对每个aggs的纬度字段都设置size，也不能准确的控制聚合结果的数量。

能详细解释一下为什么不能准确控制聚合结果的数量？

---

<div class="post-metadata">

**Author:** ![NiFeng](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@NiFeng](https://discuss.elastic.co/u/NiFeng)\
**Post date:** [June 29, 2020, 6:46am UTC](https://discuss.elastic.co/t/es/237379/9 "2020-06-29T06:46:17Z")

</div>

场景是这样的，还是以上面的SQL为例子：  
select colum1,column2,column3,column4,sum(column5),avg(column6)  
from table  
group by column1,column2,column3,column4 having sum(column5) \> 500 and avg(column6) \>1000 limit 1000  
在es中使用 pipeline aggregation实现该sql的需求，我必须aggs每个维度字段，并将他们内嵌下去。如：  
{  
"size": 0,  
"query": {  
"bool": {  
"filter":  
}  
},  
"track\_total\_hits": true,  
"aggregations": {  
"coulumn1": {  
"terms": {  
"field": "coulumn1",  
"missing": "",  
"size": 10  
},  
"aggs": {  
"coulumn2": {  
"terms": {  
"field": "coulumn2",  
"missing": "",  
"size": 10  
},  
"aggs": {  
"coulumn3": {  
"terms": {  
"field": "coulumn3",  
"missing": "",  
"size": 10  
},  
"aggs": {  
"coulumn4": {  
"terms": {  
"field": "coulumn4",  
"missing": "",  
"size": 10  
},  
"aggs": {  
"1f82b8a0": {  
"sum": {  
"field": "column5"  
}  
},  
"111df5af": {  
"avg": {  
"field": "column5"  
}  
},  
"u1Mdpy-4niN85-hUx47A\_bucket": {  
"bucket\_selector": {  
"buckets\_path": {  
"a04aa8832": "1f82b8a0"  
},  
"script": {  
"source": "params.a04aa8832\>500"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
这种聚合方式好像只能通过设置每个aggs聚合维度字段的size来控制大小，在我数据量满足的情况下。查询的总体数量是随着我维度字段的数据递增的,增长方式是：10 \* 10 \* 10 \* 10，是每个size的乘积；  
这样我在大量维度下就无法控制聚合返回的数据量了。  
当然，也可能是我对ES不是很熟，没有找到正确的查询配置方式，还请指正！  
谢谢！

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [June 30, 2020, 5:00am UTC](https://discuss.elastic.co/t/es/237379/10 "2020-06-30T05:00:06Z")

</div>

对的，无法实现你的例子中的 limit 1000。正如你说的，Es是的多个字段分组其实是嵌套的聚合，只能控制每一层嵌套的聚合的文档数量。

---

<div class="post-metadata">

**Author:** ![NiFeng](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@NiFeng](https://discuss.elastic.co/u/NiFeng)\
**Post date:** [June 30, 2020, 5:47am UTC](https://discuss.elastic.co/t/es/237379/11 "2020-06-30T05:47:29Z")

</div>

多谢您的解答！衷心希望后续es能支持！

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 28, 2020, 5:47am UTC](https://discuss.elastic.co/t/es/237379/12 "2020-07-28T05:47:31Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.