ES中如何实现类似having的先聚合再过滤查询

一、问题描述：

在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 来实现此功能。