Elasticsearch教程(5) 指标聚合 SQL DSL JavaAPI

1 准备数据

参考上一篇博客在kibana里造好数据
数据结果如下：

2 AVG（平均值）

2.1 不带where条件求平均

2.1.1 SQL描述

#=======求平均值=======
POST /_xpack/sql?format=txt
{
    "query": "SELECT AVG(level) AS avg_level FROM dept" 
}

2.1.2 DSL描述

POST /dept/_doc/_search?size=0
{
  "aggs":{
    "avg_level": {
      "avg":{
        "field": "level"
      }
    }
  }
}

2.2 带where条件求平均

2.2.1 SQL描述

POST /_xpack/sql?format=txt
{
    "query": "SELECT AVG(level) AS avg_level FROM dept WHERE parentId = 3" 
}

2.2.2 DSL描述

POST /dept/_doc/_search
{
  "size":0,
  "query":{
      "term":{
        "parentId.keyword": "3"
      }
  },
  "aggs":{
    "avg_level": {
      "avg":{
        "field": "level"
      }
    }
  }
}

2.2.3 Java API 描述

public static Double getAvg(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
    getClient();
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(queryBuilder);
    searchSourceBuilder.size(0);
    
    AvgAggregationBuilder aggregation  = AggregationBuilders.avg("agg").field(field);
    searchSourceBuilder.aggregation(aggregation);
    
    SearchRequest searchRequest = new SearchRequest(indexs).types(type);
    searchRequest.source(searchSourceBuilder);
    
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    Avg agg = searchResponse.getAggregations().get("agg");
    return agg.getValue();
}

3 MAX(最大值)

3.1 SQL描述

POST /_xpack/sql?format=txt
{
    "query": "SELECT MAX(level) AS max_level FROM dept" 
}

3.2 DSL描述

POST dept/_doc/_search?size=0
{
  "aggs": {
    "max_level": {
      "max": {
        "field": "level"
      }
    }
  }
}

3.3 Java API 描述

public static Double getMax(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
    getClient();
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(queryBuilder);
    searchSourceBuilder.size(0);
    
    AggregationBuilder aggregationBuilder = AggregationBuilders.max("agg").field(field);
    searchSourceBuilder.aggregation(aggregationBuilder);
    
    SearchRequest searchRequest = new SearchRequest(indexs).types(type);
    searchRequest.source(searchSourceBuilder);
    
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    Max agg = searchResponse.getAggregations().get("agg");
    return agg.getValue();
}

4 MIN(最小值)

4.1 SQL描述

POST /_xpack/sql?format=txt
{
    "query": "SELECT MIN(level) AS min_level FROM dept" 
}

4.2 DSL描述

POST dept/_doc/_search?size=0
{
  "aggs": {
    "min_level": {
      "min": {
        "field": "level"
      }
    }
  }
}

4.3 Java API 描述

public static Double getMin(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
    getClient();
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(queryBuilder);
    searchSourceBuilder.size(0);
    
    MinAggregationBuilder aggregation = AggregationBuilders.min("agg").field(field);
    searchSourceBuilder.aggregation(aggregation);
    
    SearchRequest searchRequest = new SearchRequest(indexs).types(type);
    searchRequest.source(searchSourceBuilder);
    
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    Min agg = searchResponse.getAggregations().get("agg");
    return agg.getValue();
}

5 SUM(求和)

5.1 SQL描述

POST /_xpack/sql?format=txt
{
    "query": "SELECT SUM(level) AS sum_level FROM dept" 
}

5.2 DSL描述

POST dept/_doc/_search?size=0
{
  "aggs": {
    "sum_level": {
      "sum": {
        "field": "level"
      }
    }
  }
}

5.3 Java API 描述

 public static Double getSum(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
     getClient();
     SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
     searchSourceBuilder.query(queryBuilder);
     searchSourceBuilder.size(0);
     
     SumAggregationBuilder aggregation = AggregationBuilders.sum("agg").field(field);
     searchSourceBuilder.aggregation(aggregation);
     
     SearchRequest searchRequest = new SearchRequest(indexs).types(type);
     searchRequest.source(searchSourceBuilder);
     
     SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
     Sum agg = searchResponse.getAggregations().get("agg");
     return agg.getValue();
 }

6 COUNT(个数)

6.1 SQL描述

6.1.1 COUNT(*)

COUNT(*),包括null和丢失的字段

#=======COUNT(*),包括null和丢失的字段=======
POST /_xpack/sql?format=txt
{
    "query": "SELECT COUNT(*) AS all_count FROM dept" 
}

上一篇博客有这一类count的java api写法

6.1.2 COUNT(ALL field_name),只统计非null的值

#=======COUNT(ALL field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
    "query": "SELECT COUNT(ALL status) AS status_count FROM dept" 
}

因为有一条数据的status=null，所以不统计它

6.1.3 COUNT(field_name),只统计非null的值

COUNT(field_name)和上面COUNT(ALL field_name)等效

#=======COUNT(field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
    "query": "SELECT COUNT(status) AS status_count FROM dept" 
}

6.1.4 COUNT(DISTINCT field_name),只统计非null的值

#=======COUNT(DISTINCT field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
    "query": "SELECT COUNT(DISTINCT status) AS status_count FROM dept" 
}

status的有效值只有0或者1

6.2 DSL描述

统计包含某一字段（比如status）的文档数,只统计非null的值

POST dept/_doc/_search?size=0
{
    "aggs" : {
        "status_count" : { 
          "value_count" : { "field" : "status" } }
    }
}

6.3 Java API 描述

统计包含某一字段（比如status）的文档数,只统计非null的值

public static long getCount(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
    getClient();
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(queryBuilder);
    searchSourceBuilder.size(0);
    
    ValueCountAggregationBuilder aggregation  = AggregationBuilders.count("agg").field(field);
    searchSourceBuilder.aggregation(aggregation);
    
    SearchRequest searchRequest = new SearchRequest(indexs).types(type);
    searchRequest.source(searchSourceBuilder);
    
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    ValueCount agg = searchResponse.getAggregations().get("agg");
    return agg.getValue();
}

7 STATS(统计)

7.1 DSL描述

POST /dept/_doc/_search
{
  "size":0,
  "aggs":{
    "stats_level": {
      "stats":{
        "field": "level"
      }
    }
  }
}

7.2 Java API 描述

public static Stats getStats(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
    getClient();
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(queryBuilder);
    searchSourceBuilder.size(0);
    
    StatsAggregationBuilder aggregation = AggregationBuilders.stats("agg").field(field);
    searchSourceBuilder.aggregation(aggregation);
    
    SearchRequest searchRequest = new SearchRequest(indexs).types(type);
    searchRequest.source(searchSourceBuilder);
    
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    Stats stats = searchResponse.getAggregations().get("agg");
    return stats;
}