ES聚合统计

1. 以多个字段唯一并去重后统计总数

注意：ES版本要使用7.xx版本

eg：以类名+方法名唯一并去重后统计接口的总数【每条数据都存在类名、方法名，并且相同的类名和方法名会存在多条数据，数据中存在不同的类名+方法名，需要从所有数据中以类名+方法名唯一并去重统计总数】

{
  "query": {
    "bool": {
      "filter": [
        {
          "wildcard": {
            "systemCode.keyword": {
              "wildcard": "hdn-test",
              "boost": 1.0
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "aggregations": {
    "interface_count": {
      "cardinality": {
        "field": "className_and_methodName"
      }
    }
  },
  "runtime_mappings": {
    "className_and_methodName": {
      "type": "keyword",
      "script": "emit(doc['className.keyword'] + ' ' + doc['methodName.keyword']);"
    }
  }
}

java实现以上DSL：

    /**
     * 根据systemCode统计接口接口总数
     *
     * @param systemCode
     * @return
     * @throws IOException
     */
    @Override
    public Long queryServerCountByAgentId(String systemCode) throws IOException {

        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

        //构造查询条件
        SysLogRequestParamDTO sysLogRequestParamDTO = new SysLogRequestParamDTO();
        sysLogRequestParamDTO.setSystemCode(systemCode);
        BoolQueryBuilder queryBuilder = getQueryBuilder(sysLogRequestParamDTO);
        searchSourceBuilder.query(queryBuilder);

        //以className和className作为唯一条件查询总数
        Long count = getCountByClassNameAndMethodName(searchSourceBuilder);
        return count;

    }

    private Long getCountByClassNameAndMethodName(SearchSourceBuilder searchSourceBuilder) throws IOException {
        SearchRequest searchRequest = new SearchRequest("索引INDEX");

        //以className和methodName唯一查询总数

        //runtime_mappings 部分
        HashMap<String, Object> runtimeMappings = new HashMap<>();
        HashMap<String, Object> classNameAndMethodName = new HashMap<>();
        String FALSIFIED_SCRIPT = "emit(doc['className.keyword'] + ' ' + doc['methodName.keyword']);";
        classNameAndMethodName.put("script", FALSIFIED_SCRIPT);
        classNameAndMethodName.put("type", "keyword");
        runtimeMappings.put("className_and_methodName", classNameAndMethodName);
        searchSourceBuilder.runtimeMappings(runtimeMappings);

        //aggregations 部分
        CardinalityAggregationBuilder cardinalityAggregationBuilder =
            AggregationBuilders.cardinality("interface_count").field("className_and_methodName");
        searchSourceBuilder.aggregation(cardinalityAggregationBuilder);

        searchRequest.source(searchSourceBuilder);
        log.info("SysLogStatisticsServiceImpl->invokeCountStatistics->DSL:{}", searchRequest);

        //执行查询
        SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);

        //获取结果
        Aggregation interface_count = response.getAggregations().get("interface_count");
        long count = ((ParsedCardinality)interface_count).getValue();

        return count;
    }

2. 求近15添内日平均数据

{
  "query": {
    "bool": {
      "filter": [
        {
          "wildcard": {
            "systemCode.keyword": {
              "wildcard": "hdn-test",
              "boost": 1.0
            }
          }
        },
        {
          "range": {
            "createdDate": {
              "from": "2022-11-17",
              "to": "2022-12-02",
              "include_lower": true,
              "include_upper": true,
              "boost": 1.0
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "aggregations": {
    "createdDateGroup": {
      "date_histogram": {
        "field": "createdDate",
        "format": "yyyy-MM-dd",
        "fixed_interval": "1d",
        "offset": 0,
        "order": {
          "_key": "asc"
        },
        "keyed": false,
        "min_doc_count": 0
      },
      "aggregations": {
        "average_of_executeTime": {
          "avg": {
            "field": "executeTime"
          }
        }
      }
    }
  }
}

java实现以上DSL：

 /**
     * 日平均耗时
     *
     * @param searchSourceBuilder
     * @return
     */
    private List<JSONObject> getDayAvgExecuteTime(SearchSourceBuilder searchSourceBuilder) throws IOException {
        List<JSONObject> list = new ArrayList<>();

        SearchRequest searchRequest = new SearchRequest(comOperateLogIndex);

        //aggregations
        AvgAggregationBuilder executeTimeAvgAggregationBuilder =
            AggregationBuilders.avg("average_of_executeTime").field("executeTime");

        //date_histogram
        DateHistogramAggregationBuilder dateHistogramAggregationBuilder =
            AggregationBuilders.dateHistogram("createdDateGroup").field("createdDate")
                .fixedInterval(DateHistogramInterval.DAY).format("yyyy-MM-dd")
                .subAggregation(executeTimeAvgAggregationBuilder);

        searchSourceBuilder.aggregation(dateHistogramAggregationBuilder);
        searchRequest.source(searchSourceBuilder);

        log.info("SysLogStatisticsServiceImpl->invokeCountStatistics->DSL:{}", searchRequest.source());

        //执行查询
        SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);

        //获取结果
        Histogram createdDateGroup = response.getAggregations().get("createdDateGroup");
        for (Histogram.Bucket bucket : createdDateGroup.getBuckets()) {
            JSONObject jsonObject = new JSONObject();
            String createDate = bucket.getKeyAsString();
            jsonObject.put("createDate", createDate);    //日期
            jsonObject.put("doc_count", bucket.getDocCount());   //总条数
            Aggregation average_of_executeTime = bucket.getAggregations().get("average_of_executeTime");
            if (average_of_executeTime instanceof ParsedAvg) {
                ParsedAvg avg = (ParsedAvg)average_of_executeTime;
                jsonObject.put("avgExeTime", avg.getValue());
            }
            list.add(jsonObject);
        }
        return list;
    }
    
    public List<DayAvgExecuteTimeVO> getDayAvgExecuteTime(String systemCode) throws IOException {
        List<DayAvgExecuteTimeVO> list = new ArrayList<>();

        SearchSourceBuilder searchSourceBuilder = buildCondition(systemCode);

        //日平均耗时
        List<JSONObject> dayAvgExecuteTimeList = getDayAvgExecuteTime(searchSourceBuilder);

        DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTime = format.format(new Date().getTime() - 15 * 24 * 60 * 60 * 1000);
        String endTime = format.format(new Date());
        Date dStart = null;
        Date dEnd = null;

        try {
            dStart = format.parse(startTime);
            dEnd = format.parse(endTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<Date> dateList = DateUtil.findDates(dStart, dEnd);
        for (Date date : dateList) {
            DayAvgExecuteTimeVO dayAvgExecuteTimeVO = new DayAvgExecuteTimeVO();
            dayAvgExecuteTimeVO.setDate(format.format(date));
            dayAvgExecuteTimeList.forEach(avgTime -> {
                if (StringUtils.equals(format.format(date), avgTime.getString("createDate"))) {
                    dayAvgExecuteTimeVO.setDayAvgExecuteTime(avgTime.getDouble("avgExeTime"));
                } else {
                    dayAvgExecuteTimeVO.setDayAvgExecuteTime(0.0);
                }
            });
            list.add(dayAvgExecuteTimeVO);
        }
        return list;
    }

    /**
     * 构造查询条件
     *
     * @param systemCode
     * @return
     */
    private SearchSourceBuilder buildCondition(String systemCode) {
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

        //构造查询条件
        SysLogRequestParamDTO sysLogRequestParamDTO = new SysLogRequestParamDTO();
        sysLogRequestParamDTO.setSystemCode(systemCode);
        BoolQueryBuilder queryBuilder = getQueryBuilder(sysLogRequestParamDTO);

        //近15天日平均耗时
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTime = format.format(new Date().getTime() - 15 * 24 * 60 * 60 * 1000);
        String endTime = format.format(new Date());
        queryBuilder.filter(QueryBuilders.rangeQuery("createdDate").gte(startTime).lte(endTime));

        searchSourceBuilder.query(queryBuilder);

        return searchSourceBuilder;
    }

    /**
     * JAVA获取某段时间内的所有日期
     * @param dStart 开始时间
     * @param dEnd  结束时间
     * @return
     */
    public static List<Date> findDates(Date dStart, Date dEnd) {
        Calendar cStart = Calendar.getInstance();
        cStart.setTime(dStart);

        List dateList = new ArrayList();
        //别忘了，把起始日期加上
        dateList.add(dStart);
        // 此日期是否在指定日期之后
        while (dEnd.after(cStart.getTime())) {
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
            cStart.add(Calendar.DAY_OF_MONTH, 1);
            dateList.add(cStart.getTime());
        }
        return dateList;
    }

3. 求近15天内平均数据

{
  "query": {
    "bool": {
      "filter": [
        {
          "wildcard": {
            "systemCode.keyword": {
              "wildcard": "hdn-test",
              "boost": 1.0
            }
          }
        },
        {
          "range": {
            "createdDate": {
              "from": "2022-11-17",
              "to": "2022-12-02",
              "include_lower": true,
              "include_upper": true,
              "boost": 1.0
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "aggregations": {
    "average_of_executeTime": {
      "avg": {
        "field": "executeTime"
      }
    }
  }
}

java实现以上DSL：

    public Double getAvgExecuteTime(String systemCode) throws IOException {
        //构造查询条件
        SearchSourceBuilder searchSourceBuilder = buildCondition(systemCode);
        //平均耗时
        JSONObject avgExecuteTime = getAvgExecuteTime(searchSourceBuilder);
        Double avgExeTime = avgExecuteTime.getDouble("avgExeTime");
        return avgExeTime;
    }

    /**
     * 构造查询条件
     *
     * @param systemCode
     * @return
     */
    private SearchSourceBuilder buildCondition(String systemCode) {
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

        //构造查询条件
        SysLogRequestParamDTO sysLogRequestParamDTO = new SysLogRequestParamDTO();
        sysLogRequestParamDTO.setSystemCode(systemCode);
        BoolQueryBuilder queryBuilder = getQueryBuilder(sysLogRequestParamDTO);

        //近15天日平均耗时
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTime = format.format(new Date().getTime() - 15 * 24 * 60 * 60 * 1000);
        String endTime = format.format(new Date());
        queryBuilder.filter(QueryBuilders.rangeQuery("createdDate").gte(startTime).lte(endTime));

        searchSourceBuilder.query(queryBuilder);

        return searchSourceBuilder;
    }

    /**
     * 求平均
     *
     * @param searchSourceBuilder
     * @return
     */
    private JSONObject getAvgExecuteTime(SearchSourceBuilder searchSourceBuilder) throws IOException {
        SearchRequest searchRequest = new SearchRequest(comOperateLogIndex);

        //聚合
        AvgAggregationBuilder executeTimeAvgAggregationBuilder =
            AggregationBuilders.avg("average_of_executeTime").field("executeTime");
        searchSourceBuilder.aggregation(executeTimeAvgAggregationBuilder);
        searchRequest.source(searchSourceBuilder);

        log.info("SysLogStatisticsServiceImpl->invokeCountStatistics->DSL:{}", searchRequest.source());

        //执行查询
        SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);

        //获取结果
        JSONObject jsonObject = new JSONObject();
        Aggregation average_of_executeTime = response.getAggregations().get("average_of_executeTime");
        if (average_of_executeTime instanceof ParsedAvg) {
            ParsedAvg avg = (ParsedAvg)average_of_executeTime;
            jsonObject.put("avgExeTime", avg.getValue());
        }
        return jsonObject;
    }