Elasticsearch Bucket和Metric聚合分析与嵌套聚合

「爱情、让人受尽委屈。」 2023-01-08 08:21 127阅读 0赞

## ##

### 1. 初始化索引 ###

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"  
        \}  
      \}  
    \}  
\}

### 2. 初始化数据 ###

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\}

### 3. 查询最低工资 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "min\_salary":\{  
      "min": \{  
        "field": "salary"  
      \}  
    \}  
  \}  
\}

### 4. 查询最高工资 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "max\_salary":\{  
      "max": \{  
        "field": "salary"  
      \}  
    \}  
  \}  
\}

### 5. 查询最高、最低、平均工资 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "max\_salary":\{  
      "max": \{  
        "field": "salary"  
      \}  
    \},  
    "min\_salary":\{  
      "min": \{  
        "field": "salary"  
      \}  
    \},  
    "avg\_salary":\{  
      "avg": \{  
        "field": "salary"  
      \}  
    \}  
  \}  
\}

### 6. stats返回工资最高、最低、平均、汇总值及员工数量 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "stats\_salary":\{  
      "stats": \{  
        "field": "salary"  
      \}  
    \}  
  \}  
\}

### 7. 对工作类型（keyword类型）分组默认计算数量 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "jobs":\{  
      "terms": \{  
        "field": "job.keyword"  
      \}  
    \}  
  \}  
\}

### 8. 对工作类型（text类型）分组报错，默认需要在mapping中开启fielddata ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "jobs":\{  
      "terms": \{  
        "field": "job"  
      \}  
    \}  
  \}  
\}

### 9. 对text类型的job字段 开启fielddata 让其支持分组查询 ###

post employees/\_mapping  
\{  
  "properties":\{  
    "job":\{  
      "type":"text",  
      "fielddata":true  
    \}  
  \}  
\}

### 10. 由于开启fielddata text类型的job字段也支持分组，但是text类型进行term分组会被分词 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "jobs":\{  
      "terms": \{  
        "field": "job"  
      \}  
    \}  
  \}  
\}

### 11. cardinality相当于 distinct count,查询去重数量 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "cardinate":\{  
      "cardinality": \{  
        "field": "job.keyword"  
      \}  
    \}  
  \}  
\}

### 12. 对keyword类型的性别分组计算数量 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "gender\_":\{  
      "terms": \{  
        "field": "gender"  
      \}  
    \}  
  \}  
\}

### 13. 按年龄分组，取前3个 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "age\_5":\{  
      "terms": \{  
        "field": "age",  
        "size": 3  
      \}  
    \}  
  \}  
\}

### 14. 查找 每个工种 年龄最大的三名员工 按年龄倒序 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "jobs":\{  
      "terms": \{  
        "field": "job.keyword"  
      \},  
      "aggs":\{  
        "old\_emp":\{  
          "top\_hits": \{  
             "size":3,  
          "sort":\[  
              \{  
                "age":\{  
                  "order":"desc"  
                \}  
              \}  
            \]  
          \}  
        \}  
      \}  
    \}  
  \}  
\}

### 15. 按照工资范围分组，可以自定义key的名称 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "salary\_range":\{  
      "range": \{  
        "field": "salary",  
        "ranges": \[  
          \{  
            "to": 10000  
          \},  
          \{  
            "from":10000,  
            "to":20000  
          \},  
          \{  
            "key":"大于 20000",  
            "from":20000  
          \}  
        \]  
      \}  
    \}  
  \}  
\}

### 16. 根据工资查询柱状图，间隔是5000，查询范围0-100000 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "salary\_histrogram":\{  
      "histogram": \{  
        "field": "salary",  
        "interval": 5000,  
        "extended\_bounds": \{  
          "min": 0,  
          "max": 100000  
        \}  
      \}  
    \}  
  \}  
\}

### 17. 嵌套聚合 按照工种分组，在每组对薪资计算最小值 最大值 平均值 和及文档数量等薪资的统计信息 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "job\_salary\_stat":\{  
      "terms": \{  
        "field": "job.keyword"  
      \},  
      "aggs":\{  
        "salary":\{  
          "stats": \{  
            "field": "salary"  
          \}  
        \}  
      \}  
    \}  
  \}  
\}

### 18. 嵌套聚合 按工种分组 在每组按性别分组 在每组对薪资计算最小值 最大值 平均值 和及文档数量等薪资的统计信息 ###

post employees/\_search  
\{  
  "size":0,  
  "aggs":\{  
    "job\_gender\_stats":\{  
      "terms": \{  
        "field": "job.keyword"  
      \},  
      "aggs":\{  
        "gender\_stats":\{  
          "terms": \{  
            "field": "gender"  
          \},  
          "aggs": \{  
            "salary\_stats": \{  
              "stats": \{  
                "field": "salary"  
              \}  
            \}  
          \}  
        \}  
      \}  
    \}  
  \}  
\}