spring-data-elasticSearch聚合sum查询

素颜马尾好姑娘i 2022-05-29 05:55 255阅读 0赞

有时需要统计一段时间内，订单的总金额。类似于sql的sum，针对某一字段求和。这就涉及到es的聚合查询，来看看用spring-data-elasticSearch怎么写：

QueryBuilder queryBuilder = QueryBuilders.boolQuery()
                    .must(QueryBuilders.rangeQuery("orderTime").gte(from).lte(to));
            // 聚合查询。goodsSales是要统计的字段，sum_sales是自定义的别名
            SumAggregationBuilder sumBuilder = AggregationBuilders.sum("sum_sales").field("goodsSales");
    
            SearchQuery searchQuery = new NativeSearchQueryBuilder()
                    .withQuery(queryBuilder)
                    .addAggregation(sumBuilder)
                    .build();
    
            double saleAmount = elasticsearchTemplate.query(searchQuery, response -> {
                InternalSum sum = (InternalSum)response.getAggregations().asList().get(0);
                return sum.getValue();
            });

对应es查询语句：

{
      "query": {
        "bool": {
          "must": [
            {
              "range": {
                "orderTime": {
                  "gte": 20180313,
                  "lte": 20180314
                }
              }
            },
            {
              "match": {
                "skuNo": "888"
              }
            }
          ]
        }
      },
      "aggs": {
        "sum_value": {
          "sum_sales": {
            "field": "goodsSales"
          }
        }
      }
    }

以下还有一些常用聚合查询（参考http://blog.csdn.net/u010454030/article/details/63266035）：

（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 ");