将SQL查询转换为ElasticSearch查询

Question

我想将以下SQL查询转换为Elasticsearch之一。谁能帮上忙

select csgg, sum(amount) from table1
where type in ('a','b','c') and year=2016 and fc="33" group by csgg having sum(amount)=0

我尝试了以下方法：enter code here

{
  "size": 500,
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 {"term" : {"fc" : "33"}},
                 {"term" : {"year" : 2016}} 
              ],
              "should" : [
                {"terms" : {"type" : ["a","b","c"] }}   
              ]
           }
         }
      }
   },
  "aggs": {
    "group_by_csgg": {
      "terms": {
        "field": "csgg"
      },
      "aggs": {
        "sum_amount": {
          "sum": {
            "field": "amount"
          }
        }
      }
    }
  }
}

但不确定我是否做对了，因为它无法验证结果。似乎要在聚合内添加查询。

Answer 1

假设您使用Elasticsearch 2.x，则有可能在Elasticsearch中 具有 -semantics。我不知道2.0之前的可能性。

您可以使用新的Pipeline Aggregation Bucket Selector
Aggregation
，它仅选择满足特定条件的存储桶：

POST test/test/_search
{
  "size": 0,
  "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 {"term" : {"fc" : "33"}},
                 {"term" : {"year" : 2016}},
                 {"terms" : {"type" : ["a","b","c"] }}
              ]
           }
         }
      }
   },
   "aggs": {
    "group_by_csgg": {
      "terms": {
        "field": "csgg",
        "size": 100
      },
      "aggs": {
        "sum_amount": {
          "sum": {
            "field": "amount"
          }
        },
        "no_amount_filter": {
          "bucket_selector": {
            "buckets_path": {"sumAmount": "sum_amount"},
            "script": "sumAmount == 0"
          }
        }
      }
    }
  }
}

但是，有两个警告。根据您的配置，可能需要启用如下脚本：

script.aggs: true
script.groovy: true

此外，由于它适用于父存储桶，因此不能保证您获得所有金额= 0的存储桶。如果字词聚合仅选择总金额！= 0的字词，则不会有结果。