ElasticSearch在java中提供的API一聚合查询

如题 需要条件查询某个区间每天的平均值，按照日期分组 ：SQL:    select date,avg(temp) from table where date>=to_date('20120215','yyyy-MM-dd') and date<=to_date('20121230','yyyy-MM-dd) group by date

 

//过滤时间 if (map.get("startTime") != null && map.get("endTime") != null) { RangeQueryBuilder rangequerybuilder = QueryBuilders .rangeQuery("LASTUPDATETIME").from(map.get("startTime").toString()).to(map.get("endTime").toString());//.format("yyyy-MMdd HH:ss:mm") queryBuilder.must(rangequerybuilder); } else if (map.get("endTime") != null) { RangeQueryBuilder rangequerybuilder = QueryBuilders .rangeQuery("LASTUPDATETIME").to(map.get("endTime").toString());//.format("yyyy-MM-dd HH:ss:mm") queryBuilder.must(rangequerybuilder); } else if (map.get("startTime") != null) { RangeQueryBuilder rangequerybuilder = QueryBuilders .rangeQuery("LASTUPDATETIME").from(map.get("startTime").toString());//.format("yyyy-MM-dd HH:ss:mm") queryBuilder.must(rangequerybuilder); }

 

if (map.get("tem") != null) { //B teamAgg1 = AggregationBuilders.avg("TEM_AS").field("TEM"); teamAgg2=AggregationBuilders.dateHistogram("lastupdatetime_Agg").field("LASTUPDATETIME").dateHistogramInterval(DateHistogramInterval.DAY).subAggregation(teamAgg1).format("yyyy-MM-dd"); teamAgg4= AggregationBuilders.filter("range",queryBuilder); teamAgg4.subAggregation(teamAgg2); }

 

searchResponse = client.prepareSearch(Constant.ESConstant.ES_INDEX_SENSOR).setQuery(queryBuilder).setSize(10000).addAggregation(teamAgg4).execute().actionGet(); SearchHits hits=searchResponse.getHits(); Map aggMap = searchResponse.getAggregations().asMap(); InternalFilter teamAgg= (InternalFilter) aggMap.get("range"); JSONObject json=JSONObject.parseObject(teamAgg.toString()); JSONObject json2=(JSONObject) json.get("range"); JSONObject json3=(JSONObject) json2.get("lastupdatetime_Agg"); JSONArray array=JSONArray.parseArray(json3.get("buckets").toString()); for (int i = 0; i < array.size(); i++) { JSONObject jo = array.getJSONObject(i); if(Integer.parseInt(jo.get("doc_count").toString())!=0){ JSONObject jsonData=(JSONObject) jo.get("TEM_AS"); Map fix = new HashMap(); fix.put("temperature",new BigDecimal(jsonData.get("value").toString()).floatValue());//温度 fix.put("inputdate", jo.get("key_as_string"));//时间 list.add(fix); } }

 统计每个班级最小/总/平均的同学的年龄

select team, avg(age)as avg_age, sum(salary) as total_salary from player group by team;

TermsBuilder teamAgg= AggregationBuilders.terms("team"); AvgBuilder ageAgg= AggregationBuilders.avg("avg_age").field("age"); SumBuilder salaryAgg= AggregationBuilders.avg("total_salary ").field("salary"); sbuilder.addAggregation(teamAgg.subAggregation(ageAgg).subAggregation(salaryAgg)); SearchResponse response = sbuilder.execute().actionGet();

 

对多个field求max/min/sum/avg

select team, avg(age)as avg_age, sum(salary) as total_salary from player group by team;

TermsBuilder teamAgg= AggregationBuilders.terms("team"); AvgBuilder ageAgg= AggregationBuilders.avg("avg_age").field("age"); SumBuilder salaryAgg= AggregationBuilders.avg("total_salary ").field("salary"); sbuilder.addAggregation(teamAgg.subAggregation(ageAgg).subAggregation(salaryAgg)); SearchResponse response = sbuilder.execute().actionGet();

 

聚合后对Aggregation结果排序

select team, sum(salary) as total_salary from player group by team order by total_salary desc;

TermsBuilder teamAgg= AggregationBuilders.terms("team").order(Order.aggregation("total_salary ", false); SumBuilder salaryAgg= AggregationBuilders.avg("total_salary ").field("salary"); sbuilder.addAggregation(teamAgg.subAggregation(salaryAgg)); SearchResponse response = sbuilder.execute().actionGet();

 Aggregation结果的解析/输出

Map<String, Aggregation> aggMap = response.getAggregations().asMap(); StringTerms teamAgg= (StringTerms) aggMap.get("keywordAgg"); Iterator<Bucket> teamBucketIt = teamAgg.getBuckets().iterator();while (teamBucketIt .hasNext()) { Bucket buck = teamBucketIt .next();//班级名称 String team = buck.getKey();//记录数long count = buck.getDocCount();//得到所有子聚合 Map subaggmap = buck.getAggregations().asMap();//avg值获取方法double avg_age= ((InternalAvg) subaggmap.get("avg_age")).getValue();//sum值获取方法double total_salary = ((InternalSum) subaggmap.get("total_salary")).getValue(); }