集成ES分组查询统计求平均值(1)

2401_83946044

于 2024-04-29 21:05:30 发布

阅读量140

点赞数 6

分类专栏：程序员文章标签： elasticsearch 数据库 java

本文链接：https://blog.csdn.net/2401_83946044/article/details/138325568

版权

程序员专栏收录该内容

143 篇文章 1 订阅

订阅专栏

    HttpHost[] httpHostArray = new HttpHost[1];
    httpHostArray[0] = new HttpHost(host, port);
    RestClientBuilder restClientBuilder = RestClient.builder(httpHostArray)
            .setHttpClientConfigCallback(httpClientBuilder -> {
                httpClientBuilder.disableAuthCaching();
                return httpClientBuilder.setDefaultCredentialsProvider(credentialsProvider);
            });
    restClientBuilder.setRequestConfigCallback(requestConfigBuilder -> requestConfigBuilder
            .setConnectTimeout(60000)
            .setSocketTimeout(150000));
    client = new RestHighLevelClient(
            restClientBuilder
    );
    return client;
}

}




---


### 3.使用

@Resource
private RestHighLevelClient restHighLevelClient;

/**
* 查询温湿度24小时平均值
* @param deviceCode 设备编码
* @param startTime 开始时间
* @param endTime 结束时间
* @param humName 湿度字段名
* @param tempName 温度字段名
* @return 温湿度24小时平均值
*/
private TreeMap<String, Map<String, Double>> queryTempHumDayAvg(String deviceCode, Date startTime, Date endTime, String humName, String tempName) {
TreeMap<String, Map<String, Double>> treeMap = new TreeMap<>();
//ES查询
String index = EsCalendar.getDeviceFlowIndex(startTime, endTime);
SearchRequest searchRequest = new SearchRequest(index);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//忽略不可用索引，允许索引不不存在，通配符表达式将扩展为打开的索引
searchRequest.indicesOptions(IndicesOptions.fromOptions(true, true, true, false));

    String timeFmt = "yyyy-MM-dd";

    // 组装ES请求数据
    String startTimeStr = DateUtil.format(startTime, DatePattern.NORM\_DATETIME\_PATTERN);
    String endTimeStr = DateUtil.format(endTime, DatePattern.NORM\_DATETIME\_PATTERN);
    QueryBuilder rangeQuery = QueryBuilders.rangeQuery("createTime").lte(endTimeStr).gte(startTimeStr);

    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
    // 必须为deviceCode
    boolQueryBuilder.must(QueryBuilders.termQuery("deviceCode", deviceCode));
    rangeQuery = QueryBuilders.boolQuery().must(rangeQuery).must(boolQueryBuilder);
    QueryBuilder boolQuery = QueryBuilders.boolQuery().must(rangeQuery);

    searchSourceBuilder.query(boolQuery).size(0);


    //平均值 温度
    //String tempName = "temp\_avg";
    String tempAvgName = tempName + "\_avg";
    String tempFactorName = "data." + tempName;
    AvgAggregationBuilder tempAvgAggregationBuilder = AggregationBuilders.avg(tempAvgName).field(tempFactorName);

    //平均值 湿度
    //String humName = "hygrometer\_avg";
    String humAvgName = humName + "\_avg";
    String humFactorName = "data." + humName;
    AvgAggregationBuilder humAvgAggregationBuilder = AggregationBuilders.avg(humAvgName).field(humFactorName);


    String createTimeGroup = "createTimeGroup";
    DateHistogramAggregationBuilder aggregation = AggregationBuilders.dateHistogram(createTimeGroup)
            .field("createTime").fixedInterval(DateHistogramInterval.DAY)
            .format(timeFmt)
            //过滤掉count为0的数据
            .minDocCount(1).subAggregation(tempAvgAggregationBuilder).subAggregation(humAvgAggregationBuilder);

    //分组条件
    searchSourceBuilder.aggregation(aggregation);
    searchRequest.source(searchSourceBuilder);


    // 按照因子列表查询
    searchRequest.source(searchSourceBuilder);
    SearchResponse searchResponse = null;
    Map<String, Map<String, Double>> mp = new HashMap<>();
    try {
        log.info("方法getCabinetTempHum24HourAvg查询ES请求数据：" + searchRequest);
        searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
        log.info("方法getCabinetTempHum24HourAvg查询ES响应数据：" + searchResponse.toString());
        Aggregations aggregations = searchResponse.getAggregations();
        if (aggregations != null) {
            //组织出参数
            aggregations.forEach(agg -> {
                ParsedDateHistogram parsedDateHistogram = (ParsedDateHistogram) agg;
                List buckets = parsedDateHistogram.getBuckets();
                if (CollectionUtil.isNotEmpty(buckets)) {
                    buckets.forEach(bucket -> {
                        ParsedDateHistogram.ParsedBucket timeGroupTerm = (ParsedDateHistogram.ParsedBucket) bucket;
                        String timeStr = timeGroupTerm.getKeyAsString();

                        Aggregations subAggregations = timeGroupTerm.getAggregations();
                        if (subAggregations != null) {
                            Map<String, Double> tempHumMap = new HashMap<>();
                            Map<String, Aggregation> subAggMap = subAggregations.asMap();
                            if (subAggMap != null) {
                                Aggregation tempAgg = subAggMap.get(tempAvgName);
                                if (tempAgg != null) {
                                    ParsedAvg tempAggPdh = (ParsedAvg) tempAgg;
                                    tempHumMap.put(tempName, tempAggPdh.getValue());
                                }
                                Aggregation humAgg = subAggMap.get(humAvgName);
                                if (humAgg != null) {
                                    ParsedAvg humAggPdh = (ParsedAvg) humAgg;
                                    tempHumMap.put(humName, humAggPdh.getValue());
                                }

                            }
                            mp.put(timeStr, tempHumMap);
                        }

                    });
                }

            });

        }
        //数据补全
        List<DateTime> dateTimeList = DateUtil.rangeToList(startTime, DateUtil.offsetHour(endTime, -1), DateField.HOUR\_OF\_DAY);
        if (CollectionUtil.isNotEmpty(dateTimeList)) {
            String finTempName = "temp\_avg";
            String finHumName = "hum\_avg";
            dateTimeList.forEach(dateTime -> {
                String timeStr = DateUtil.format(dateTime, timeFmt);
                Map<String, Double> finTempHumMap = new HashMap<>();
                Map<String, Double> tempHumMap = mp.get(timeStr);
                if (tempHumMap == null) {
                    finTempHumMap.put(finTempName, 0.0);
                    finTempHumMap.put(finHumName, 0.0);
                } else {
                    Double tempAvg = tempHumMap.get(tempName);
                    Double humAvg = tempHumMap.get(humName);
                    finTempHumMap.put(finTempName, tempAvg);
                    finTempHumMap.put(finHumName, humAvg);
                }
                treeMap.put(timeStr, finTempHumMap);
            });
        }

    } catch (Exception e) {
        log.error("方法countByEs查询ES异常", e);
    }

    return treeMap;
}


关键点注意：


1. QueryBuilders.rangeQuery传入的时间精度，需要yyyy-MM-dd HH:mm:ss,否则会报错![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/cf97703ff14a4a7bb81871a2b91a8276.png)
2. 这里对时间格式化分组，使用的是DateHistogramAggregationBuilder  
 这个在EsApi7+就废弃了calendarInterval，替换新的fixedInterval
![](https://img-blog.csdnimg.cn/img_convert/9a8cb5f8c0ec69e6499adead0da6e95b.png)



最全的Linux教程，Linux从入门到精通

======================

1.  **linux从入门到精通(第2版)**

2.  **Linux系统移植**

3.  **Linux驱动开发入门与实战**

4.  **LINUX 系统移植 第2版**

5.  **Linux开源网络全栈详解 从DPDK到OpenFlow**



![华为18级工程师呕心沥血撰写3000页Linux学习笔记教程](https://img-blog.csdnimg.cn/img_convert/59742364bb1338737fe2d315a9e2ec54.png)



第一份《Linux从入门到精通》466页

====================

内容简介

====

本书是获得了很多读者好评的Linux经典畅销书**《Linux从入门到精通》的第2版**。本书第1版出版后曾经多次印刷，并被51CTO读书频道评为“最受读者喜爱的原创IT技术图书奖”。本书第﹖版以最新的Ubuntu 12.04为版本，循序渐进地向读者介绍了Linux 的基础应用、系统管理、网络应用、娱乐和办公、程序开发、服务器配置、系统安全等。本书附带1张光盘，内容为本书配套多媒体教学视频。另外,本书还为读者提供了大量的Linux学习资料和Ubuntu安装镜像文件，供读者免费下载。



![华为18级工程师呕心沥血撰写3000页Linux学习笔记教程](https://img-blog.csdnimg.cn/img_convert/9d4aefb6a92edea27b825e59aa1f2c54.png)



**本书适合广大Linux初中级用户、开源软件爱好者和大专院校的学生阅读，同时也非常适合准备从事Linux平台开发的各类人员。**

> 需要《Linux入门到精通》、《linux系统移植》、《Linux驱动开发入门实战》、《Linux开源网络全栈》电子书籍及教程的工程师朋友们劳烦您转发+评论




**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以点击这里获取！](https://bbs.csdn.net/topics/618542503)**

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**