一、问题描述：
在mysql数据库中，我们可以很方面的通过having关键字实现对聚合结果的过滤查询。那么，在ES中该如何实现类似having的先聚合再过滤查询呢？
二、业务场景：
需要找出下单次数大于等于2单，并且平均下单金额大于等于100的客户
在关系型数据库中对应的SQL语句：
SELECT
userId,
AVG(amount) avgAmount,
count(*) orderCount
FROM order
GROUP by userId
HAVING avgAmount >= 100 and orderCount >=2
三、数据准备
创建订单索引order_index，并添加测试数据。
## 删除索引
## DELETE order_index
## 新建索引
PUT order_index
{
"mappings": {
"properties": {
"name": {
"type": "keyword"
},
"amount": {
"type": "integer"
}
}
}
}
## 添加数据
POST order_index/_bulk?refresh
{ "create": { } }
{ "name": "老万", "amount": 100}
{ "create": { } }
{ "name": "老万", "amount": 80}
{ "create": { } }
{ "name": "老万", "amount": 300}
{ "create": { } }
{ "name": "老王", "amount": 45}
{ "create": { } }
{ "name": "小明", "amount": 15}
{ "create": { } }
{ "name": "小明", "amount": 50}
{ "create": { } }
{ "name": "小红", "amount": 300}
四、具体实现
1、SQL实现方式
说明：由于ES6.3以后已经支持sql查询，所有首先尝试大家最熟悉的sql查询方案能否实现。
POST /_sql?format=txt
{
"query": "SELECT name,AVG(amount) avgAmount,count(*) orderCount FROM order_index group by name having avgAmount >= 100 and orderCount >=2 "
}
查询结果：
用户名为老万，满足平均订单金额大于100，且下单数大于2。
查询结果正确。
2、DSL实现方式
GET order_index/_search
{
"size": 0,
"aggs": {
"groupName": {
"terms": {
"field": "name"
},
"aggs": {
"avgAmount": {
"avg": {
"field": "amount"
}
},
"having": {
"bucket_selector": {
"buckets_path": {
"orderCount": "_count",
"avgAmount": "avgAmount"
},
"script": {
"source": "params.avgAmount >= 100 && params.orderCount >=2 "
}
}
}
}
}
}
}
查询结果：
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 7,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"groupUserId" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "老万",
"doc_count" : 3,
"avgAmount" : {
"value" : 160.0
}
}
]
}
}
}sql语句底层实现分析：
POST /_sql/translate
{
"query": "SELECT name,AVG(amount) avgAmount,count(*) orderCount FROM order_index group by name having avgAmount >= 100 and orderCount >=2 "
}
执行结果：
分析sql转化的DSL语句，和上面DSL语句的实现，说明两者底层实现原理一致。
mysql中通过having实现根据聚合结果进行过滤，ES中使用 bucket_selector 来实现此功能。
{
"size" : 0,
"_source" : false,
"stored_fields" : "_none_",
"aggregations" : {
"groupby" : {
"composite" : {
"size" : 1000,
"sources" : [
{
"7e80e5b2" : {
"terms" : {
"field" : "name",
"missing_bucket" : true,
"order" : "asc"
}
}
}
]
},
"aggregations" : {
"d8415567" : {
"avg" : {
"field" : "amount"
}
},
"having.having.d8415567_&_having.b26c7698" : {
"bucket_selector" : {
"buckets_path" : {
"a0" : "d8415567",
"a1" : "_count"
},
"script" : {
"source" : "InternalQlScriptUtils.nullSafeFilter(InternalQlScriptUtils.and(InternalQlScriptUtils.nullSafeFilter(InternalQlScriptUtils.gte(params.a0,params.v0)),InternalQlScriptUtils.nullSafeFilter(InternalQlScriptUtils.gte(params.a1,params.v1))))",
"lang" : "painless",
"params" : {
"v0" : 100,
"v1" : 2
}
},
"gap_policy" : "skip"
}
}
}
}
}
}具体实现
本文主要介绍了ES中如何实现类似having的先聚合再过滤查询。
1、介绍了基于sql和dsl的两种实现方式，但是二者的底层原理其实都是一样的。
2、实际项目中，更推荐直接采用sql来实现，代码简单，sql语句相比dsl上手更容易，也更容易理解。
3、mysql中通过having实现根据聚合结果进行过滤，ES中使用 bucket_selector 来实现此功能。