elasticsearch java实现类似sql group by

最新推荐文章于 2024-08-21 02:26:10 发布

蓝风遥遥

最新推荐文章于 2024-08-21 02:26:10 发布

阅读量1.1w

点赞数 7

分类专栏： ELK 文章标签： java elasticsearch sql

本文链接：https://blog.csdn.net/xr568897472/article/details/73826255

版权

ELK 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

学习es查询总是想往sql方面靠齐，学习了一段时间，总结一些如下

BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
//添加时间范围过滤
        boolQueryBuilder.must(QueryBuilders.rangeQuery("@timestamp").format("yyyy-MM-dd HH:mm:ss").gte(conditionMap.get("createTimeB")).lte(conditionMap.get("createTimeE")));
        AggregationBuilder aggregationBuilder = AggregationBuilders
                .terms(查询字段别名).field(分组字段)
                .order(Terms.Order.aggregation(ES_COUNT_ALIAS, false))
                .size(10)
                .subAggregation(AggregationBuilders.count(统计字段别名).field(统计的字段));
        SearchRequestBuilder searchRequestBuilder = ElasticsearchHelper.getInstance().getClient().prepareSearch(ES_INDEX).setTypes(ES_TYPE)
                .setQuery(boolQueryBuilder)
                .addAggregation(aggregationBuilder)
                .setSize(0);

        SearchResponse sr = searchRequestBuilder.execute().actionGet();
        Terms genders = sr.getAggregations().get(统计字段别名);
for (Terms.Bucket entry : genders.getBuckets()) { list.add((String) entry.getKey()+"-("+entry.getDocCount()+")"); }

现在对上面进行修改，例如想group by 时间，并且按天来进行分组

AggregationBuilder aggregation = AggregationBuilders
                .dateHistogram("agg")
                .field("@timestamp")
                .format("yyyy-MM-dd")
                .dateHistogramInterval(DateHistogramInterval.DAY);

然后现在可能有新需求，group by 时间，姓名

AggregationBuilder nameAgg = AggregationBuilders.terms(姓名别名).field(姓名).size(10);
aggregation.subAggregation(nameAgg);

如果可以能需要进行名称统计，但是需要distinct

nameAgg.subAggregation(AggregationBuilders.cardinality(别名).field(姓名))

其他的总结如下

（1）统计某个字段的数量
  ValueCountBuilder vcb=  AggregationBuilders.count("count_uid").field("uid");
（2）去重统计某个字段的数量（有少量误差）
 CardinalityBuilder cb= AggregationBuilders.cardinality("distinct_count_uid").field("uid");
（3）聚合过滤
FilterAggregationBuilder fab= AggregationBuilders.filter("uid_filter").filter(QueryBuilders.queryStringQuery("uid:001"));
（4）按某个字段分组
TermsBuilder tb=  AggregationBuilders.terms("group_name").field("name");
（5）求和
SumBuilder  sumBuilder=	AggregationBuilders.sum("sum_price").field("price");
（6）求平均
AvgBuilder ab= AggregationBuilders.avg("avg_price").field("price");
（7）求最大值
MaxBuilder mb= AggregationBuilders.max("max_price").field("price"); 
（8）求最小值
MinBuilder min=	AggregationBuilders.min("min_price").field("price");
（9）按日期间隔分组
DateHistogramBuilder dhb= AggregationBuilders.dateHistogram("dh").field("date");
（10）获取聚合里面的结果
TopHitsBuilder thb=  AggregationBuilders.topHits("top_result");
（11）嵌套的聚合
NestedBuilder nb= AggregationBuilders.nested("negsted_path").path("quests");
（12）反转嵌套
AggregationBuilders.reverseNested("res_negsted").path("kps ");