Elasticsearch教程(3) ES聚合查询DSL

推荐一个好的翻译官方文档网站 (http://cwiki.apachecn.org/)
聚合提供了从数据中分组和提取数据的能力。最简单的聚合方法大致等于 SQL GROUP BY 和 SQL 聚合函数。

1. 准备数据

PUT /pigg/_doc/1
{
  "name": "三爷",
  "age": 29,
  "hometown": "盐城",
  "gender": "male"
}

PUT /pigg/_doc/2
{
  "name": "珣爷",
  "age": 28,
  "hometown": "徐州",
  "gender": "female"
}

PUT /pigg/_doc/3
{
  "name": "米可",
  "age": 1,
  "hometown": "苏州",
  "gender": "female"
}

2. group by(举例: 按照性别分组)

2.1 SQL描述

SELECT gender, COUNT(1) 
FROM pigg 
GROUP BY gender 
ORDER BY COUNT(1) DESC  

2.2 DSL描述

GET /pigg/_search
{
  "size": 0,
  "aggs": {
    "group_by_gender": {
      "terms": {
        "field": "gender.keyword"
      }
    }
  }
}

2.3 返回结果

{
  ...
  
  "aggregations": {
    "group_by_gender": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "female",
          "doc_count": 2
        },
        {
          "key": "male",
          "doc_count": 1
        }
      ]
    }
  }
}

3. avg(举例: 求平均年龄)

3.1 SQL描述

SELECT AVG(age) 
FROM pigg

3.2 DSL描述

GET /pigg/_search
{
  "aggs": {
    "avg_age": {
      "avg": {
        "field": "age"
      }
    }
  }
}

3.3 返回结果

{
  ...
  
  "aggregations": {
    "avg_age": {
      "value": 19.333333333333332
    }
  }
}

4. avg和group by组合(举例: 求不同性别的平均年龄)

4.1 SQL描述

SELECT gender, AVG(age) as 'avg_age'
FROM pigg
GROUP BY gender 
ORDER BY 'avg_age' DESC

4.2 DSL描述

GET /pigg/_search
{
  "size": 0, 
  "aggs": {
    "group_by_gender":{
      "terms": {
        "field": "gender.keyword",
        "order": {
          "avg_age": "desc"
        }
      },
      "aggs": {
        "avg_age": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  }
}

4.3 返回结果

{
  ...

  "aggregations": {
    "group_by_gender": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "male",
          "doc_count": 1,
          "avg_age": {
            "value": 29
          }
        },
        {
          "key": "female",
          "doc_count": 2,
          "avg_age": {
            "value": 14.5
          }
        }
      ]
    }
  }
}

暂时写到这边,ES的聚合功能非常强大,远不止上面的例子,还需好好学习整理,加油!