Elasticsearch实战（十三）---聚合搜索Aggs聚合及Count，Avg操作

最新推荐文章于 2023-12-09 22:40:19 发布

jzjie

最新推荐文章于 2023-12-09 22:40:19 发布

阅读量2.9k

点赞数

分类专栏： ElasticSearch 文章标签： elasticsearch ES 下钻分析 es aggs聚合操作 es aggs count求和 es aggs avg操作

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

版权

ElasticSearch 专栏收录该内容

26 篇文章 55 订阅

订阅专栏

Elasticsearch实战- 聚合搜索Aggs聚合及Count，Avg操作

文章目录

- - Elasticsearch实战- 聚合搜索Aggs聚合及Count，Avg操作

1.聚合搜索 bucket 桶及metric分析计算

bucket 就是聚合搜索时候的分组，类似Mysql的GroupBy，比如统计销售部有张三，李四，技术部有王五，赵六，group by 部门部门就是分组桶 bucket
metric 就是对桶内的数据进行统计分析，比如销售部有2个员工，技术部有2个员工，对桶内数据进行统计分析如求和，最大值，最小值，平均值等分析就是 metric

1.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 统计计数

2.1统计每个部门有多少人

#统计每个部门多少人
get /testcopy/_search
{
  "size":0,
  "aggs":{
    "count_by_deptname":{
      "terms": {
        "field": "deptName",
        "order": {
          "_key": "desc"
        }, 
        "size": 10
      }
    }
  }
}

统计出错
“reason” : “Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [deptName] in order to load field data by uninverting the inverted index. Note that this can use significant memory.”

 "root_cause" : [
      {
        "type" : "illegal_argument_exception",
        "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [deptName] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
      }

然后排查发现是因为我要统计的部门 deptName字段是text类习惯， text类型没有设置 fielddata=true，且被用于 aggs聚合排序中，所以要给需要统计的字段进行设置 fielddata
解决办法

#执行 设置 fielddata
PUT testcopy/_mapping
{
  "properties": {
    "deptName": { 
      "type":     "text",
      "fielddata": true
    }
  }
}

查询结果，可以查处结果，但是不是自己想要的，因为销售部被拆分成了 “销”,“售”,"部"单独做了统计，而现在我是要整个销售部的统计
在这里插入图片描述

修改查询语句，把整个 deptName 当作keyword 不分词处理查询

get /testcopy/_search
{
  "size":0,
  "aggs":{
    "count_by_deptname":{
      "terms": {
        "field": "deptName.keyword",
        "order": {
          "_key": "desc"
        }, 
        "size": 10
      }
    }
  }
}

查看结果
是我们想要的，销售部 4人，测试部 3人，技术部 4人
在这里插入图片描述

3.Avg求平均，先分组count，然后在求平均数avg

3.1 统计每个部门的人数及平均年龄

求每个部门的人数及大家平均的年龄，就是我先要对部门进行分组，形成一个桶，然后对桶内的数据进行求平均数，然后按照年龄的升序排列返回结果

#group_dept组内 再次进行 aggs
get /testcopy/_search
{
  "size":0,
  "aggs":{
    "group_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10,
        "order": {
          "aggs_by_age": "asc"
        }
      },
      //group桶内 进行avg操作
      "aggs": {
        "aggs_by_age": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  }
}

查看结果，结果是按照平均年龄升序进行排列

技术部 4人，平均年龄 24.75
销售部 4人，平均年龄 30.25
测试部 3 人，平均年龄 38.33

我们查一下测试部，看看统计是否准确， avg平均年龄计算是否准确

#查看 销售部的人 
get /testcopy/_search
{
  "query":{
    "match": {
      "deptName.keyword": "测试部"
    }
  }
}

查看结果测试部 3人，年龄 20+60+35 = 115 ，平均年龄 115 / 3 = 38.33 计算正确
在这里插入图片描述

3.2 嵌套分组先分组，然后组内在分组如何实现

比如现在我想统计销售部下面有哪些省份，每个省份有多少人即第一次分组以销售部分组，然后再销售部分组内部，然后再以 provice 省份做分组, 我们先把 provice 要进行 aggs 的字段加上 fielddata设置

#给 provice 要聚合的字段 加上 fielddata 配置
PUT testcopy/_mapping
{
  "properties": {
    "provice": { 
      "type":     "text",
      "fielddata": true
    }
  }
}

双重分组，组内再次进行分组如何实现

#部门分组后， 再桶内 再对省份 分组

get /testcopy/_search
{
  "size":0,
  "aggs":{
    "group_by_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10
      },
      "aggs": {
        "provice_count": {
          "terms": {
            "field": "provice.keyword",
            "size": 10
          }
        }
      }
    }
  }
}

查看结果，满足我们的需求

技术部 4人下面分了湖北省和武汉市(省份)，然后湖北省 3个人，武汉市 1个人
销售部 4人下面分了湖北省和河南省，然后湖北省2人，河南省 2人
测试部 3人，全部都是湖北省的

3.3 嵌套分组内再进行 avg计算

上面我们进行了嵌套分组，先以部门分组，然后以省份分组，现在我想再加一个统计分许，统计每个部门，每个省份的人的平均年龄
等于是 3步操作，比上一个更加复杂了

先分组部门 deptName
在分组省份 provice
然后再aggs 统计avg年龄

#嵌套多层 进行 avg 求平均数
get /testcopy/_search
{
  "size":0,
  "aggs":{
    "group_by_dept":{
      "terms": {
        "field": "deptName.keyword",
        "size": 10
      },
      //deptname 分组内 进行 aggs
      "aggs": {
        "group_by_provice": {
          "terms": {
            "field": "provice.keyword",
            "size": 10
          },
          //provice 分组内 进行 aggs 求avg
          "aggs": {
            "avg_by_age": {
              "avg": {
                "field": "age"
              }
            }
          }
        }
      }
    }
  }
}

我们查看下结果
技术部下面湖北省 3人，平均年龄 23，武汉市 1人，平均年龄 30
在这里插入图片描述

我们现在搜一下技术部的人，看下年龄分布是不是上面聚合的结果

#查 技术部， 湖北省的人 的年龄
get /testcopy/_search
{
  "query":{
    "bool": {
      "must": [
        {
          "match": {
            "deptName.keyword": "技术部"
          }
        },
        {
          "match": {
            "provice": "湖北省"
          }
        }
      ] 
    }
  },
  "_source":["deptName","provice","age"]
}

查询结果（ 20+30+19 ）= 69 / 3 = 23 ，平均年龄就是23 ，上面的聚合结果是准确的
在这里插入图片描述