Elasticsearch聚合分析作用范围（聚合前筛选条件、聚合后筛选条件）及对聚合结果指定排序方式

向右看齐 2022-10-26 14:04 167阅读 0赞

初始化数据
    DELETE /employees
    PUT /employees/
    {
      "mappings" : {
          "properties" : {
            "age" : {
              "type" : "integer"
            },
            "gender" : {
              "type" : "keyword"
            },
            "job" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 50
                }
              }
            },
            "name" : {
              "type" : "keyword"
            },
            "salary" : {
              "type" : "integer"
            }
          }
        }
    }
    
    PUT /employees/_bulk
    { "index" : {  "_id" : "1" } }
    { "name" : "Emma","age":32,"job":"Product Manager","gender":"female","salary":35000 }
    { "index" : {  "_id" : "2" } }
    { "name" : "Underwood","age":41,"job":"Dev Manager","gender":"male","salary": 50000}
    { "index" : {  "_id" : "3" } }
    { "name" : "Tran","age":25,"job":"Web Designer","gender":"male","salary":18000 }
    { "index" : {  "_id" : "4" } }
    { "name" : "Rivera","age":26,"job":"Web Designer","gender":"female","salary": 22000}
    { "index" : {  "_id" : "5" } }
    { "name" : "Rose","age":25,"job":"QA","gender":"female","salary":18000 }
    { "index" : {  "_id" : "6" } }
    { "name" : "Lucy","age":31,"job":"QA","gender":"female","salary": 25000}
    { "index" : {  "_id" : "7" } }
    { "name" : "Byrd","age":27,"job":"QA","gender":"male","salary":20000 }
    { "index" : {  "_id" : "8" } }
    { "name" : "Foster","age":27,"job":"Java Programmer","gender":"male","salary": 20000}
    { "index" : {  "_id" : "9" } }
    { "name" : "Gregory","age":32,"job":"Java Programmer","gender":"male","salary":22000 }
    { "index" : {  "_id" : "10" } }
    { "name" : "Bryant","age":20,"job":"Java Programmer","gender":"male","salary": 9000}
    { "index" : {  "_id" : "11" } }
    { "name" : "Jenny","age":36,"job":"Java Programmer","gender":"female","salary":38000 }
    { "index" : {  "_id" : "12" } }
    { "name" : "Mcdonald","age":31,"job":"Java Programmer","gender":"male","salary": 32000}
    { "index" : {  "_id" : "13" } }
    { "name" : "Jonthna","age":30,"job":"Java Programmer","gender":"female","salary":30000 }
    { "index" : {  "_id" : "14" } }
    { "name" : "Marshall","age":32,"job":"Javascript Programmer","gender":"male","salary": 25000}
    { "index" : {  "_id" : "15" } }
    { "name" : "King","age":33,"job":"Java Programmer","gender":"male","salary":28000 }
    { "index" : {  "_id" : "16" } }
    { "name" : "Mccarthy","age":21,"job":"Javascript Programmer","gender":"male","salary": 16000}
    { "index" : {  "_id" : "17" } }
    { "name" : "Goodwin","age":25,"job":"Javascript Programmer","gender":"male","salary": 16000}
    { "index" : {  "_id" : "18" } }
    { "name" : "Catherine","age":29,"job":"Javascript Programmer","gender":"female","salary": 20000}
    { "index" : {  "_id" : "19" } }
    { "name" : "Boone","age":30,"job":"DBA","gender":"male","salary": 30000}
    { "index" : {  "_id" : "20" } }
    { "name" : "Kathy","age":29,"job":"DBA","gender":"female","salary": 20000}
    
    聚合的默认作用范围是query,在query返回的结果中进行聚合
    post employees/_search
    {
      "size":0,
      "query":{
        "range": {
          "age": {
            "gte": 20
          }
        }
      },
      "aggs":{
        "jobs":{
          "terms": {
            "field": "job.keyword"
          }
        }
      }
    }
    
    filter只是作为当前聚合子句的条件，不会作为其他聚合语句的条件
    post employees/_search
    {
      "size":0,
      "aggs":{
        "old_person":{
          "filter": {
            "range": {
              "age": {
                "from": 35
              }
            }
          },
          "aggs":{
            "jobs":{
              "terms": {
                "field": "job.keyword"
              }
            }
          }
        },
        "all_jobs":{
          "terms": {
            "field": "job.keyword",
            "size": 10
          }
        }
      }
    }
    
    post_filter对聚合后的结果再次筛选，相当于having
    post employees/_search
    {
      "size":0,
      "aggs":{
        "jobs":{
          "terms": {
            "field": "job.keyword"
          }
        }
      },
      "post_filter":{
        "match":{
          "job.keyword":"Dev Manager"
        }
      }
    }
    
    gloable使得当前聚合语句不受其他条件影响
    post employees/_search
    {
      "size":0,
      "query":{
        "range": {
          "age": {
            "gte": 40 
          }
        }
      },
      "aggs":{
        "jobs":{
          "terms": {
            "field": "job.keyword"
          }
        },
        "all":{
          "global": {},
          "aggs": {
            "salary_avg": {
              "avg": {
                "field": "salary"
              }
            }
          }
        }
      }
    }
    
    按每个分组的人数升序排列
    post employees/_search
    {
      "size":0,
      "query":{
        "range": {
          "age": {
            "gte": 20
          }
        }
      },
      "aggs": {
        "jobs": {
          "terms": {
            "field":"job.keyword",
            "order":[
              {"_count":"asc"},
              {"_key":"desc"}
              ]
            
          }
        }
      }
    }
    
    按每个工种的平均工资降序排列
    post employees/_search
    {
      "size":0,
      "aggs":{
        "jobs":{
          "terms": {
            "field": "job.keyword",
            "order": [
              {"avg_salary":"desc"}
              ]
          },
          "aggs":{
            "avg_salary":{
              "avg": {
                "field": "salary"
              }
            }
          }
        }
      }
    }