es常用聚合查询及案例

1、单值输出 常用有：min 、max、sum、avg、cardinality(去重求和)

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "avg_sal": {
      "avg": {
        "field": "sal"  //工资平均值
      }
    },
    "max_val": {
      "max": {
        "field": "sal"   //工资最大值
      }
    },
    "min_val": {
      "min": {
        "field": "sal"    //工资最小值
      }
    },
    "cardibality_count": {
      "cardinality": {
        "field": "job"       //岗位数
      }
    }
  }
}

2、多值输出  可以一次统计处多个数据：terms 、stats

2.1 stats:根据查询的字段求查询的数量、最大值、最小值、平均值、总和

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "stats_info": {
      "stats": {
        "field": "sal"
      }
    }
  }
}

查询结果

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 16,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "stats_info": {
      "count": 16,
      "min": 2000,
      "max": 23000,
      "avg": 13281.25,
      "sum": 212500
    }
  }
}

2.2 terms查询：根据查询条件进行分组并统计每一组的总数

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "job_count": {
      "terms": {
        "field": "job"  //查询每个岗位有多少人
      }
    }
  }
}

返回值

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 16,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "job_count": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "java",
          "doc_count": 7
        },
        {
          "key": "dba",
          "doc_count": 5
        },
        {
          "key": "html",
          "doc_count": 4
        }
      ]
    }
  }
}

2.3 terms查询嵌套  

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "job_count": {
      "terms": {
        "field": "job"  //查询不同岗位下的人数
      },
      "aggs": {
        "gender_count": {
          "terms": {
            "field": "gender" //查询不同岗位下不同性别的人数
          }
        }
      }
    }
  }
}

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "job_count": {
      "terms": {
        "field": "job"  //查询不同岗位下工资的详情（总数、最大值、最小值、平均值、和）
      },
      "aggs": {
        "stats_count": {
          "stats": {
            "field": "sal"
          }
        }
      }
    }
  }
}

还可以套3层 4层。。。。

2.3 top_hits查询 查询年龄最大的两名员工信息

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "top_age_infot": {
      "top_hits": {
        "size": 2,
        "sort": [
              {
              "age": {
              "order":"desc"
              }
            }
          ]
      }
    }
  }
}

2.4 range查询 范围统计查询

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "range_infot": {
      "range": {
        "field": "sal",  //统计每个工资段的员工数量
        "ranges": [
          {
            "key": "0-5000", 
            "from": 0,
            "to": 5000
          },
          {
            "key": "5000-10000", 
            "from": 5000,
            "to": 10000
          },
          {
            "key": "10000<", 
            "from": 10000
          }
        ]
      }
    }
  }
}

2.5 histogram直方图区间统计

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "histogram_info": {
      "histogram": {
        "field": "sal",
        "interval": 5000  //以5000一个区间统计人数
      }
    }
  }
}

 2.6 max_bucket查询每个桶的最大值，min_bucket查询每个桶的最小值

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "job_info": {
      "terms": {
        "field": "job"
      },
      "aggs": {
        "job_avg_info": {
          "avg": {
            "field": "sal"
          }
        }
      }
    },
    "min_avg_sal_job":{
      "max_bucket": {
        "buckets_path": "job_info>job_avg_info"  //查询平均工资最高的岗位
      }
    }
  }
}

2.7 查询年龄大于30岁的员工平均工资

GET cwtest/employee/_search
{
  "size": 0, 
  "query": {
    "range": {
      "age": {
        "gte": 30
      }
    }
  }, 
  "aggs": {
    "sal_info": {
      "avg": {
        "field": "sal"
      }
    }
  }
}

2.8 查询java员工的平均工资

GET cwtest/employee/_search
{
  "size": 0, 
  "query": {
    "constant_score": {  //不会计算分数，用于聚合查询时可以提高效率
      "filter": {
        "term": {
          "job": "java"
        }
      }
    }
  }, 
  "aggs": {
    "java_avg_sal_info": {
      "avg": {
        "field": "sal"
      }
    }
  }
}

2.9 局部过滤：查询所有员工平均工资和年龄大于30岁员工平均工资

GET cwtest/employee/_search
{
  "size": 0, 
  "aggs": {
    "sal_avg": {
      "avg": {
        "field": "sal"
      }
    },
    "gt_30_avg_info": {
      "filter": {   //局部过滤
        "range": {
          "age": {
            "gt": 30
          }
        }
      },
      "aggs": {
        "gt_30_avg_sal": {
          "avg": {
            "field": "sal"
          }
        }
      }
    }
  }
}