文章目录

• 1. 以多个字段唯一并去重后统计总数
• 2. 求近15添内日平均数据
• 3. 求近15天内平均数据

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*/@Overridepublic 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 runtimeMappings = new HashMap<>();HashMap 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 getDayAvgExecuteTime(SearchSourceBuilder searchSourceBuilder) throws IOException {List list = new ArrayList<>();SearchRequest searchRequest = new SearchRequest(comOperateLogIndex);//aggregationsAvgAggregationBuilder executeTimeAvgAggregationBuilder =AggregationBuilders.avg("average_of_executeTime").field("executeTime");//date_histogramDateHistogramAggregationBuilder 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 getDayAvgExecuteTime(String systemCode) throws IOException {List list = new ArrayList<>();SearchSourceBuilder searchSourceBuilder = buildCondition(systemCode);//日平均耗时List 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 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 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;}