es 对a分组对b聚合 javaAPI

es7.1聚合查询 javaAPI (三)

  es复杂聚合查询 — select a,count(distinct b)
    对a分组对b聚合

类似如下sql

select a,count(distinct b) as count from myIndex
	where date between '2020-01-01 00:00:00' and '2020-01-02 00:00:00'
	group by a 
	order by count desc 
	limit 5;

核心聚合方法

// 去重聚合
CardinalityAggregationBuilder distinct_count = AggregationBuilders.cardinality("distinct_count")
		.field("b.keyword");										//去重字段
// 分组聚合
TermsAggregationBuilder aggregation = AggregationBuilders.terms("by_group")	//别名
        .field("a.keyword")											//分组字段(keyword类型)
        .subAggregation(distinct_count)								//聚合组合
        .size(5)													//获取数据量
        .order(BucketOrder.aggregation("distinct_count", false));	//排序

ps:及其消耗资源

完整代码

/**
 *      getClient():  获取es查询的连接对象(不做赘述)
 * 		closeClient(): 关闭连接
 *  	Util.date(): 把日期字符串 减八小时 添加 TZ 样式的方法
 * 			2020-01-02 00:00:00  -->  2020-01-01T16:00:00.000Z
 */
public viod query(){
	RestHighLevelClient client = getClient();
	try {
		QueryBuilder queryBuilder = QueryBuilders.boolQuery()
              	.must(QueryBuilders.rangeQuery(big_time)
                       	.from(Util.date("2020-01-01 00:00:00"))
                       	.to(Util.date("2020-01-02 00:00:00")));
		// 1、创建search请求
        SearchRequest searchRequest = new SearchRequest("myIndex");
        // 2、用SearchSourceBuilder来构造查询请求体 ,请仔细查看它的方法，构造各种查询的方法都在这。
        SearchSourceBuilder sourceBuilder = new SearchSourceBuilder().size(0).query(queryBuilder);

        // 加入聚合
		// 去重聚合
		CardinalityAggregationBuilder distinct_count = AggregationBuilders.cardinality("distinct_count")
				.field("b.keyword");										//去重字段
		// 分组聚合
		TermsAggregationBuilder aggregation = AggregationBuilders.terms("by_group")	//别名
		        .field("a.keyword")											//分组字段(keyword类型)
		        .subAggregation(distinct_count)								//聚合组合
		        .size(5)													//获取数据量
		        .order(BucketOrder.aggregation("distinct_count", false));	//排序

        sourceBuilder.aggregation(aggregation);
        searchRequest.source(sourceBuilder);
        //3、发送请求
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        if (RestStatus.OK.equals(searchResponse.status())) {
            // 获取聚合结果
            Aggregations aggregations = searchResponse.getAggregations();
            Terms byAgeAggregation = aggregations.get("by_group");
            for (Terms.Bucket buck : byAgeAggregation.getBuckets()) {
                String ip = buck.getKeyAsString();
                Aggregations aggregations1 = buck.getAggregations();
                Aggregation distinct_count1 = aggregations1.get("distinct_count");
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(distinct_count1));
                String cardinalityValue = jsonObject.getString("value");
                System.out.println(ip +"\t\t" + cardinalityValue);
            }
        }
	}catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            closeClient(client);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

结果展示

57.81.154.52		2
0.0.174.249		1
0.0.191.195		1
0.0.22.117		1
0.1.38.212		1

es其他聚合

1. es7.1按时间聚合查询 javaApI
   .
2. es7.1聚合查询 javaAPI
   .