springboot + elasticsearch 实现聚合查询_springboot elasticsearch 聚合查询

需求背景：

终端上报表读数 记录在elasticsearch

统计每天 最大值最小值

springboot版本：2.2.6   默认的elasticsearch

 <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-elasticsearch</artifactId>
      <version>3.2.6.RELEASE</version>

版本 elasticsearch

{
  "name" : "node1",
  "cluster_name" : "docker-cluster",
  "cluster_uuid" : "Lg0bD-E-Thuaw4cDN5uQrQ",
  "version" : {
    "number" : "7.4.2",
    "build_flavor" : "default",
    "build_type" : "tar",
    "build_hash" : "2f90bbf7b93631e52bafb59b3b049cb44ec25e96",
    "build_date" : "2019-10-28T20:40:44.881551Z",
    "build_snapshot" : false,
    "lucene_version" : "8.2.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

根据springdata官网

Spring Data Elasticsearch - Reference Documentation

 版本符合

这里为了方便 采用的是 api创建索引

切记这里没有采用spring-data 去创建索引 而是采用的是 api 接口创建 原因后面再提

    @PutMapping("/createIndex")
    @ApiOperation(value = "创建索引")
    public R<Object> createIndex() {
        boolean index = elasticsearchRestTemplate.createIndex(DataUploadInfo.class);
        elasticsearchRestTemplate.putMapping(DataUploadInfo.class);
        return R.success();
    }

 
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.TypeAlias;
import org.springframework.data.elasticsearch.annotations.DateFormat;
import org.springframework.data.elasticsearch.annotations.Document;
import org.springframework.data.elasticsearch.annotations.Field;
import org.springframework.data.elasticsearch.annotations.FieldType;
 
/**
 *  {
 *   "busId": "1000010814",
 *   "createTime": 1649408879000,
 *   "deviceNum": "AE0007A1GMBC00047P",
 *   "gunNo": "1",
 *   "rdChargeCurrent": 5.617,
 *   "rdChargingPower": 1220.0,
 *   "rdChargingVoltage": 225.11,
 *   "totalElectricalPower": 270305.0
 * }
 *
 *
 *
 * @Field(type=FieldType.Text, analyzer=“ik_max_word”) 表示该字段是一个文本，并作最大程度拆分，默认建立索引
 * @Field(type=FieldType.Text,index=false) 表示该字段是一个文本，不建立索引
 * @Field(type=FieldType.Date) 表示该字段是一个文本，日期类型，默认不建立索引
 * @Field(type=FieldType.Long) 表示该字段是一个长整型，默认建立索引
 * @Field(type=FieldType.Keyword) 表示该字段内容是一个文本并作为一个整体不可分，默认建立索引
 * @Field(type=FieldType.Float) 表示该字段内容是一个浮点类型并作为一个整体不可分，默认建立索引
 * <p>
 * date 、float、long都是不能够被拆分的
 */
@Data
@Document(indexName = "charging-monitor-data", indexStoreType = "_doc", useServerConfiguration = true, createIndex = false)
@TypeAlias("_doc")
public class DataUploadInfo {
    /**
     * 主键
     */
    @Id
    @ApiModelProperty(value = "主键", example = "11", hidden = true)
    private String id;
 
    @ApiModelProperty(value = "枪号", example = "1")
    @Field(type = FieldType.Keyword )
    private String gunNo;
 
    @ApiModelProperty(value = "桩号", example = "DG1120B1CN1C000125")
    @Field(type = FieldType.Keyword)
    private String deviceNum;
 
    @ApiModelProperty(value = "流水ID", example = "AU22188888888888")
    @Field(type = FieldType.Keyword )
    private String busId;
 
    @ApiModelProperty(value = "充电电流（毫安）", example = "21.21")
    @Field(type = FieldType.Double, index = false)
    private Double rdChargeCurrent;
 
    @ApiModelProperty(value = "充电电压(毫伏)", example = "212.21")
    @Field(type = FieldType.Double, index = false)
    private Double rdChargingVoltage;
 
    @ApiModelProperty(value = "充电电能（瓦）", example = "212.21")
    @Field(type = FieldType.Double, index = false)
    private Double rdChargingPower;
 
    @ApiModelProperty(value = "剩余时间（分钟）", example = "21")
    @Field(type = FieldType.Integer, index = false)
    private Integer rdTimeLeft;
 
    @ApiModelProperty(value = "电量百分比(soc)", example = "29")
    @Field(type = FieldType.Integer, index = false)
    private Integer rdCurrentSoc;
 
    @ApiModelProperty(value = "电表读数 单位kwh 保留三位小数,启动成功时才有", example = "2.292")
    @Field(type = FieldType.Double, index = false)
    private Double totalElectricalPower;
 
    @ApiModelProperty(value = "正极温度", example = "22")
    @Field(type = FieldType.Integer, index = false)
    private Integer gunPositiveTemperature;
 
    @ApiModelProperty(value = "负极温度", example = "83")
    @Field(type = FieldType.Integer, index = false)
    private Integer gunNegativeTemperature;
 
    @ApiModelProperty(value = "电量上报时间", example = "1648646486000")
    @Field(type = FieldType.Date, format = DateFormat.custom, pattern = "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis")
    private Long createTime;
 
}

这里是创建 映射的Java文件 注意配置   createIndex = false

这里不自动创建索引

我们调用上面的rest 接口创建 索引

查看_mapping 会发现

{
-"charging-monitor-data": {
-"mappings": {
-"properties": {
-"busId": {
"type": "keyword"
},
-"createTime": {
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
"type": "date"
},
-"deviceNum": {
"type": "keyword"
},
-"gunNegativeTemperature": {
"index": false,
"type": "integer"
},
-"gunNo": {
"type": "keyword"
},
-"gunPositiveTemperature": {
"index": false,
"type": "integer"
},
-"id": {
-"fields": {
-"keyword": {
"ignore_above": 256,
"type": "keyword"
}
},
"type": "text"
},
-"rdChargeCurrent": {
"index": false,
"type": "double"
},
-"rdChargingPower": {
"index": false,
"type": "double"
},
-"rdChargingVoltage": {
"index": false,
"type": "double"
},
-"rdCurrentSoc": {
"index": false,
"type": "integer"
},
-"rdTimeLeft": {
"index": false,
"type": "integer"
},
-"totalElectricalPower": {
"index": false,
"type": "double"
}
}
}
}
}

keyword类型会增加keyword属性 而不是直接增加到我定义gunNegativeTemperature的属性下面

"pojo里面定义的属性": {
   -"fields": {
     -"keyword": {
        "ignore_above": 256,
        "type": "keyword"
      }
    },
   "type": "text"
}

下面按照桩枪做每日电表的最大最小值

上代码

 
import com.haoran.cloud.app.monitor.entity.DataUploadInfo;
import com.haoran.cloud.app.monitor.ocpp.OcppResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.log4j.Log4j2;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.aggregations.Aggregation;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.Aggregations;
import org.elasticsearch.search.aggregations.bucket.terms.ParsedStringTerms;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.max.MaxAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.max.ParsedMax;
import org.elasticsearch.search.aggregations.metrics.min.MinAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.min.ParsedMin;
import org.joda.time.DateTime;
import org.springframework.data.elasticsearch.core.ElasticsearchRestTemplate;
import org.springframework.data.elasticsearch.core.query.NativeSearchQuery;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
 
import javax.annotation.Resource;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
 
/**
 * @author wenhaoran
 * @version 1.0
 */
@RestController
@RequestMapping("/test")
@Api(value = "test", tags = "test")
@Log4j2
@Validated
public class PileMonitorController1 {
 
    /**
     * @return
     */
    @GetMapping("/dailySummaryEnergy")
    @ApiOperation(value = "每日电力汇总")
    public OcppResult<Object> dailySummaryEnergy() throws ParseException {
 
        BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
 
        DateTime now = DateTime.now();
        DateTime plus = now.plusDays(1);
 
        String fromDateStr = df.format(now.toDate());
        Date fromDate = df.parse(fromDateStr);
        String toDateStr = df.format(plus.toDate());
        Date toDate = df.parse(toDateStr);
        queryBuilder.must(QueryBuilders.rangeQuery("createTime").gte(fromDate.getTime()).lt(toDate.getTime()));
 
        NativeSearchQueryBuilder nativeSearchQueryBuilder = new NativeSearchQueryBuilder().withQuery(queryBuilder);
        TermsAggregationBuilder pile = AggregationBuilders.terms("group_deviceNum").field("deviceNum");
        TermsAggregationBuilder gun = AggregationBuilders.terms("group_gunNo").field("gunNo");
        MinAggregationBuilder minNumber = AggregationBuilders.min("minNumber").field("totalElectricalPower");
        MaxAggregationBuilder maxNumber = AggregationBuilders.max("maxNumber").field("totalElectricalPower");
        gun.subAggregation(minNumber).subAggregation(maxNumber);
        pile.subAggregation(gun);
        nativeSearchQueryBuilder.addAggregation(pile);
        NativeSearchQuery nativeSearchQuery = nativeSearchQueryBuilder.build();
        Aggregations query = elasticsearchRestTemplate.query(nativeSearchQuery, searchResponse -> searchResponse.getAggregations());
        Map<String, Aggregation> stringAggregationMap = query.asMap();
        ParsedStringTerms stringTerms = (ParsedStringTerms) stringAggregationMap.get("group_deviceNum");
        List<? extends Terms.Bucket> buckets = stringTerms.getBuckets();
        for (Terms.Bucket bucket : buckets) {
            Map<String, Aggregation> pileMap = bucket.getAggregations().asMap();
            ParsedStringTerms gunAgg = (ParsedStringTerms) pileMap.get("group_gunNo");
            List<? extends Terms.Bucket> gunBucketList = gunAgg.getBuckets();
            for (Terms.Bucket gunBucket : gunBucketList) {
                Aggregations aggregations = gunBucket.getAggregations();
                Map<String, Aggregation> asMap = aggregations.getAsMap();
                if (asMap.containsKey("minNumber")) {
                    ParsedMin aggregation = (ParsedMin) asMap.get("minNumber");
                    System.out.println("minNumber=" + aggregation.getValue());
                }
                if (asMap.containsKey("maxNumber")) {
                    ParsedMax aggregation = (ParsedMax) asMap.get("maxNumber");
                    System.out.println("maxNumber=" + aggregation.getValue());
                }
            }
        }
        return OcppResult.success("success");
    }
 
    @DeleteMapping("/deleteIndex")
    @ApiOperation(value = "删除索引")
    public OcppResult<Object> deleteIndex() {
        elasticsearchRestTemplate.deleteIndex(DataUploadInfo.class);
        return OcppResult.success();
    }
 
    @PutMapping("/createIndex")
    @ApiOperation(value = "创建索引")
    public OcppResult<Object> createIndex() {
        boolean index = elasticsearchRestTemplate.createIndex(DataUploadInfo.class);
        elasticsearchRestTemplate.putMapping(DataUploadInfo.class);
        return OcppResult.success();
    }
 
    final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
 
    
 
    @Resource
    private ElasticsearchRestTemplate elasticsearchRestTemplate;
}