Elasticsearch实战（十四）---聚合搜索Aggs多层嵌套聚合处理

jzjie

已于 2022-07-10 19:34:26 修改

阅读量5.5k

点赞数

分类专栏： ElasticSearch 文章标签： elasticsearch aggs avg操作 aggs count操作 aggs嵌套分组查询 es多层分组嵌套aggs统计

于 2022-07-10 17:39:20 首次发布

本文链接：https://blog.csdn.net/u010134642/article/details/125706962

版权

ElasticSearch 专栏收录该内容

26 篇文章 52 订阅

订阅专栏

Elasticsearch实战- -聚合搜索Aggs多层分组嵌套统计处理

文章目录

- - Elasticsearch实战- -聚合搜索Aggs多层分组嵌套统计处理

1.准备数据

POST /testcopy/_bulk
{"index":{"_id": 1}}
{"empId" : "111","name" : "员工1","age" : 20,"sex" : "男","mobile" : "19000001111","salary":1333,"deptName" : "技术部","provice" : "湖北省","city":"武汉","area":"光谷大道","address":"湖北省武汉市洪山区光谷大厦","content" : "i like to write best elasticsearch article"}
{"index":{"_id": 2}}
{"empId" : "222","name" : "员工2","age" : 25,"sex" : "男","mobile" : "19000002222","salary":15963,"deptName" : "销售部","provice" : "湖北省","city":"武汉","area":"江汉区","address" : "湖北省武汉市江汉路","content" : "i think java is the best programming language"}
{"index":{"_id": 3}}
{ "empId" : "333","name" : "员工3","age" : 30,"sex" : "男","mobile" : "19000003333","salary":20000,"deptName" : "技术部","provice" : "湖北省","city":"武汉","area":"经济技术开发区","address" : "湖北省武汉市经济开发区","content" : "i am only an elasticsearch beginner"}
{"index":{"_id": 4}}
{"empId" : "444","name" : "员工4","age" : 20,"sex" : "女","mobile" : "19000004444","salary":5600,"deptName" : "销售部","provice" : "湖北省","city":"武汉","area":"沌口开发区","address" : "湖北省武汉市沌口开发区","content" : "elasticsearch and hadoop are all very good solution, i am a beginner"}
{"index":{"_id": 5}}
{ "empId" : "555","name" : "员工5","age" : 20,"sex" : "男","mobile" : "19000005555","salary":9665,"deptName" : "测试部","provice" : "湖北省","city":"高新开发区","area":"武汉","address" : "湖北省武汉市东湖隧道","content" : "spark is best big data solution based on scala ,an programming language similar to java"}
{"index":{"_id": 6}}
{"empId" : "666","name" : "员工6","age" : 30,"sex" : "女","mobile" : "19000006666","salary":30000,"deptName" : "技术部","provice" : "武汉市","city":"湖北省","area":"江汉区","address" : "湖北省武汉市江汉路","content" : "i like java developer"}
{"index":{"_id": 7}}
{"empId" : "777","name" : "员工7","age" : 60,"sex" : "女","mobile" : "19000007777","salary":52130,"deptName" : "测试部","provice" : "湖北省","city":"黄冈市","area":"边城区","address" : "湖北省黄冈市边城区","content" : "i like elasticsearch developer"}
{"index":{"_id": 8}}
{"empId" : "888","name" : "员工8","age" : 19,"sex" : "女","mobile" : "19000008888","salary":60000,"deptName" : "技术部","provice" : "湖北省","city":"武汉","area":"汉阳区","address" : "湖北省武汉市江汉大学","content" : "i like spark language"}
{"index":{"_id": 9}}
{"empId" : "999","name" : "员工9","age" : 40,"sex" : "男","mobile" : "19000009999","salary":23000,"deptName" : "销售部","provice" : "河南省","city":"郑州市","area":"二七区","address" : "河南省郑州市郑州大学","content" : "i like java developer"}
{"index":{"_id": 10}}
{"empId" : "101010","name" : "张湖北","age" : 35,"sex" : "男","mobile" : "19000001010","salary":18000,"deptName" : "测试部","provice" : "湖北省","city":"武汉","area":"高新开发区","address" : "湖北省武汉市东湖高新","content" : "i like java developer i also like  elasticsearch"}
{"index":{"_id": 11}}
{"empId" : "111111","name" : "王河南","age" : 61,"sex" : "男","mobile" : "19000001011","salary":10000,"deptName" : "销售部",,"provice" : "河南省","city":"开封市","area":"金明区","address" : "河南省开封市河南大学","content" : "i am not like  java "}
{"index":{"_id": 12}}
{"empId" : "121212","name" : "张大学","age" : 26,"sex" : "女","mobile" : "19000001012","salary":1321,"deptName" : "测试部",,"provice" : "河南省","city":"开封市","area":"金明区","address" : "河南省开封市河南大学","content" : "i am java developer  thing java is good"}
{"index":{"_id": 13}}
{"empId" : "131313","name" : "李江汉","age" : 36,"sex" : "男","mobile" : "19000001013","salary":1125,"deptName" : "销售部","provice" : "河南省","city":"郑州市","area":"二七区","address" : "河南省郑州市二七区","content" : "i like java and java is very best i like it do you like java "}
{"index":{"_id": 14}}
{"empId" : "141414","name" : "王技术","age" : 45,"sex" : "女","mobile" : "19000001014","salary":6222,"deptName" : "测试部",,"provice" : "河南省","city":"郑州市","area":"金水区","address" : "河南省郑州市金水区","content" : "i like c++"}
{"index":{"_id": 15}}
{"empId" : "151515","name" : "张测试","age" : 18,"sex" : "男","mobile" : "19000001015","salary":20000,"deptName" : "技术部",,"provice" : "河南省","city":"郑州市","area":"高新开发区","address" : "河南省郑州高新开发区","content" : "i think spark is good"}

2.分组嵌套查询及count，avg操作

2.1 以部门分组，求部门avg年龄，且部门内以省分组，省平均年龄，且 order by 每个省 avg年龄

这次的嵌套分组和上一篇文章的 Elasticsearch实战（十三）—聚合搜索Aggs聚合及Count，Avg操作中的 3.3 嵌套分组内avg有什么区别？
之前文章是先以部门分组，然后以省份分组，统计每个部门内，每个省份的人的平均年龄，只求了一次平局年龄
aggs 是在terms 平级开始操作，只有一个平均年龄就是部门内省份内的1个平均年龄

之前文章是  先以部门 分组， 然后以 省份 分组，  统计 每个部门内，每个省份的人的 平均年龄，只求了一次平局年龄， 没有求 部门的平均年龄
1.先分组 部门 deptName
2.在分组 省份 provice
3.然后在省份内 aggs 统计avg年龄

{
  "size":0,
  "aggs":{
    "group_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10
      },
      //dept分组内 term结束 就开始组内嵌套分组
      "aggs": {
        "group_provice": {
          "terms": {
            "field": "provice.keyword",
            "size": 10
          },
          //provice分组内 term结束 就开始统计avg
          "aggs": {
            "provice_avg_age": {
              "avg": {
                "field": "age"
              }
            }
          }
        }
      }
    }
  }
}

之前的查询结果
在这里插入图片描述

这次呢？不仅要统计内层的avg 年龄，我还要统计外层的部门内的 avg年龄，做了两次avg
部门分组，求avg，组内再分组省份，再求avg

#本次查询不是 基于组内 再进行 aggs ，而是 直接再 dept_avg_age 部门求平均年龄 平级 直接进行group_provice 省份分组
#然后在省份分组的 term平级 进行 aggs 进行 provice_avg_age 省内平均年龄

get /testcopy/_search
{
  "size":0,
  "aggs":{
    "group_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10,
        "order": {
          "dept_avg_age": "desc"
        }
      },
      //dept分组 terms结束 直接aggs先求dept avg年龄
      "aggs": {
        "dept_avg_age": {
          "avg": {
            "field": "age"
          }
        },
        //求完 dept avg age，dept_avg_age 同级别 直接并列分组名字,以provice分组
        "group_provice":{
          "terms": {
            "field": "provice.keyword",
            "size": 10,
            "order": {
              "provice_avg_age": "desc"
            }
          },
          //group provice内部 ，terms结束 直接aggs 求provice avg年龄
          "aggs": {
            "provice_avg_age": {
              "avg": {
                "field": "age"
              }
            }
          }
        }
      }
    }
  }
}

本次查询结果
在这里插入图片描述

2.2 aggs并列实现多次查询，求不同部门的 max， min，sum，avg 四个统计

求每个部门的人数的最大年龄，最小年龄，年龄综合，平均年龄， aggs平级可以多个聚合种类比如 max，min，sum，avg等

#aggs平级 四个名字，一次性做四次分组
get /testcopy/_search
{
  "size":0,//不展示原始数据
  "aggs":{
    "group_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10
      },
      //group分组内 term平级 进行 统计 max，min，sum，avg
      "aggs": {
        "max_age": {
          "max": {
            "field": "age"
          }
        },
        //aggs内 max分组名称 平级 开始 min分组名称
        "min_age":{
          "min": {
            "field": "age"
          }
        },
        //aggs内 max,min 分组名称 平级 开始 sum分组名称
        "sum_age":{
          "sum": {
            "field": "age"
          }
        },
        //aggs内 max,min,sum 分组名称 平级 开始 avg分组名称
        "avg_age":{
          "avg": {
            "field": "age"
          }
        }
      }
    }
  }
}

查看结果
技术部 4人 max：30， avg：24.75， sum：99，min：19
销售部 4人 max：40， avg：30.25， sum：121，min：20
在这里插入图片描述