弹性搜索(COUNT *)，具有分组依据和条件 [英] Elastic Search (COUNT*) with group by and where condition

问题描述

尊敬的Elastic Serach用户，

Dear Elastic Serach users,

我是ElasticSearch的新手.

I am newbie in ElasticSearch.

我对于如何将以下sql命令转换为elasticSearch DSL查询感到困惑?谁能帮助我.

I am confused for how to convert the following sql command into elasticSearch DSL query ? Can anyone help to assist me.

SELECT ip, count(*) as c  FROM elastic WHERE  date 
BETWEEN '2016-08-20  00:00:00' and '2016-08-22 13:41:09' 
AND service='http' AND destination='10.17.102.1' GROUP BY ip ORDER BY c DESC;

谢谢你

推荐答案

以下查询将完全实现您想要的内容，即，它将在所需的 date 范围内并使用所需的 service 和 destination ，然后在其 ip 字段上运行 terms 聚合(= group by)，并按以下顺序对后者进行排序递减计数顺序.

The following query will achieve exactly what you want, i.e. it will select the documents within the desired date range and with the required service and destination and then run a terms aggregation (=group by) on their ip field and order the latter in decreasing count order.

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "date": {
              "gt": "2016-08-22T00:00:00.000Z",
              "lt": "2016-08-22T13:41:09.000Z"
            }
          }
        },
        {
          "term": {
            "service": "http"
          }
        },
        {
          "term": {
            "destination": "10.17.102.1"
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_ip": {
      "terms": {
        "field": "ip"
      }
    }
  }
}

这篇关于弹性搜索(COUNT *)，具有分组依据和条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！