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

梦里梦外; 2022-09-02 08:08 684阅读 0赞

# 一、问题描述： #

在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。  
查询结果正确。  
![在这里插入图片描述][b354f741a8774ab9bfcc728e3de54e26.png]

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

[b354f741a8774ab9bfcc728e3de54e26.png]: /images/20220829/9c7d4f8aa7e74606895a738a000a0b68.png