数据库语句用mysql ，es是2.3版本 ，同时加一些5.3的功能实现和思路，5.3的研究还不是很透彻。
首先给出数据库表结构 ：
实现日期直方图 每天的数量 sql: select count(*) as dateCount, DATE_FORMAT(create_time,'%Y-%m-%d') as dateStr from crm_customer where create_time bettwen @from and @to (from, to 为起止时间) and userId in (....) es: SearchRequestBuilder requestBuilder = new SearchRequestBuilder(client, SearchAction.INSTANCE); SearchResponse searchResponse = requestBuilder.setIndices("pc") .setTypes("crmCustomer") .setSize(0) .setQuery( QueryBuilders .boolQuery() .must(QueryBuilders.termsQuery("userId",userIds)) .must(QueryBuilders.rangeQuery(search) .format("yyyy-MM-dd") .from(from) .to(to) .includeLower(true)//默认为true 可不加 .includeUpper(true)默认为true 可不加 ) ) .addAggregation(AggregationBuilders.dateHistogram("agg") .format("yyyy-MM-dd") .interval(DateHistogramInterval.DAY) .field(search) .extendedBounds("2017-04-23", "2017-04-25")//这个区间会强制生成值 //.minDocCount(0)) .get(); Histogram histogram = searchResponse.getAggregations().get("agg"); //解析 for (Histogram.Bucket bucket : histogram.getBuckets()) { result.put(bucket.getKeyAsString(),bucket.getDocCount()); }2.实现group by 同时 order by
sql: select progress,count(*) as a where userKeys in (....) and lastActionTime bettwen from and to group by progress order by progress es: SearchResponse searchResponse = new SearchRequestBuilder(client,SearchAction.INSTANCE).setIndices("webindex") .setTypes("pc") .setSize(0) .setQuery( QueryBuilders .boolQuery() .must(QueryBuilders.termsQuery("userKey",userKeys)) .must( QueryBuilders.rangeQuery("lastActionTime") .from(from) .to(to) .includeLower(true) ) ) .addAggregation( AggregationBuilders .terms("agg") .field("progress") .minDocCount(0) .size(8) .order(Terms.Order.term(true)) ) .get();