Elasticsearch学习---实现类似SQL的分组统计功能

测试数据

使用kibana自带的数据。

GET /kibana_sample_data_flights/_search

实现功能

过滤DestCountry为IT的文档数据，然后按照OriginCountry进行分组，统计每个OriginCountry的AvgTicketPrice总和，和DistanceMiles总和

类似于实现如下SQL：

select OriginCountry,sum(AvgTicketPrice),sum(DistanceMiles) from table where DestCountry = 'IT' group by OriginCountry order by OriginCountry 

实现语法

GET /kibana_sample_data_flights/_search
{
  "query": {
    "match": {
      "DestCountry": "ZA"
    }
  },
  "aggs": {
    "result": {
      "terms": {
        "field": "OriginCountry",
        "order": {
          "_key": "asc"
        }
      },
      "aggs": {
        "sum_price": {
          "sum": {
            "field": "AvgTicketPrice"
          }
        },
        "sum_miles":{
          "sum": {
            "field": "DistanceMiles"
          }
        }
      }
    }
  }
}

查询结果

当然也可以按照聚合中的其他字段排序，比如DistanceMiles

GET /kibana_sample_data_flights/_search
{
  "query": {
    "match": {
      "DestCountry": "ZA"
    }
  },
  "aggs": {
    "result": {
      "terms": {
        "field": "OriginCountry",
        "order": {
          "sum_miles": "desc"
        }
      },
      "aggs": {
        "sum_price": {
          "sum": {
            "field": "AvgTicketPrice"
          }
        },
        "sum_miles":{
          "sum": {
            "field": "DistanceMiles"
          }
        }
      }
    }
  }
}