ES对应mysql的group by分组查询javaApi，多对多关系的分组查询

ES对应mysql的group by分组查询javaApi，多对多关系的分组查询

比如我这边有个下列订单索引数据，现在的需求是按用户(fmerchantId)和支付方式(fchannelId)进行分组统计订单总金额(famt)和总笔数，其中用户和支付方式是多对多的关系，就是一个用户会对应多个支付方式，一个支付方式会对应多个用户

{
          "famt": "2",
          "fbankCode": "0000_0002",
          "fbankName": "支付宝",
          "fchannelCode": "ALIPAY",
          "fchannelId": "993",
          "fchannelName": "支付宝",
          "fchannelTradeNo": "2020072222001439181419030679",
          "fchgAgenCode": "111111",
          "fcreateDate": "2020-07-22",
          "fcreateDay": "22",
          "fcreateMonth": "07",
          "fcreateTime": "2020-07-22 14:46:57",
          "fcreateYear": "2020",
          "fdeviceType": "phone",
          "fmerchantId": "5200002020072001",
          "fmerchantName": "测试用户",
          "forderNo": "483325941654679552",
          "fpayCode": "36000019115000000287",
          "fthirdpayTradeNo": "2007221446570296",
          "ftradeStatus": "1"
        }

1.创建查询条件，相当于mysql的where条件

其中SearchSourceBuilder相当于mysql中的一条完整sql语句，BoolQueryBuilder相当于where条件，根据需求自行添加where条件，最后把boolQueryBuilder的where条件添加到SearchSourceBuilder的sql中

//查询条件
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder =  QueryBuilders.boolQuery();
//成功状态的支付订单
boolQueryBuilder.must(QueryBuilders.termsQuery("ftradeStatus", Arrays.asList("1", "2", "3", "4")));
//用户，支付方式不能为空
boolQueryBuilder.must(QueryBuilders.existsQuery("fmerchantId"));
boolQueryBuilder.must(QueryBuilders.existsQuery("fchannelId"));
boolQueryBuilder.must(QueryBuilders.existsQuery("fchannelName"));

String startDate = queryMap.get("startDate") == null ? null : queryMap.get("startDate").toString();
String endDate = queryMap.get("endDate") == null ? null : queryMap.get("endDate").toString();
//大于等于
if (!StringUtils.isEmpty(startDate)) {
    boolQueryBuilder.must(QueryBuilders.rangeQuery("fcreateDate").gte(startDate));
}
//小于
if (!StringUtils.isEmpty(endDate)) {
    boolQueryBuilder.must(QueryBuilders.rangeQuery("fcreateDate").lte(endDate));
}
//添加查询条件
searchSourceBuilder.query(boolQueryBuilder);

2.添加分组条件，相当于group by条件

TermsAggregationBuilder相当于mysql中的group by分组查询条件字段，创建要分组的各个字段TermsAggregationBuilder，AggregationBuilders.terms("fchannelTypeId").field(
"fchannelId").size(searchSize).order(BucketOrder.key(true))中terms是创建的别名字段（类似mysql select a as "A"）,field是索引中的字段，size可设置查询数量大小，order进行排序。

然后进行group by字段的拼接，用termsAggregationBuilder.subAggregation(termsAggregationBuilder3)，相当于group by a,b字段，a和b都是字段.

注意ES中是可以拼接对象的，比如我先执行termsAggregationBuilder2.subAggregation(AggregationBuilders.sum("money").field("famt"))，这是根据用户ID和用户总金额分组了；再执行termsAggregationBuilder.subAggregation(termsAggregationBuilder2)，相当于用户ID和用户总金额分组当作一个对象b和支付方式a字段一起分组了（group by a,b），其中a是一个字段，b是一个对象（b中包含用户和总金额的分组），这就是ES的分组内再分组

//按聚合名称标识对桶进行升序排序
TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("fchannelTypeId").field(
    "fchannelId").size(searchSize).order(BucketOrder.key(true));//支付方式ID
TermsAggregationBuilder termsAggregationBuilder2 = AggregationBuilders.terms("fmerchantTypeId").field(
    "fmerchantId");//用户ID编号
TermsAggregationBuilder termsAggregationBuilder3 = AggregationBuilders.terms("fbankTypeCode").field(
    "fbankCode");//渠道商编号
TermsAggregationBuilder termsAggregationBuilder4 = AggregationBuilders.terms("fbankTypeName").field(
    "fbankName.keyword");//渠道商名称
TermsAggregationBuilder termsAggregationBuilder5 = AggregationBuilders.terms("fchannelTypeName").field(
    "fchannelName.keyword");//支付渠道名称

//1.先按支付渠道，渠道商编号，渠道商名称，支付渠道名称进行分组
termsAggregationBuilder.subAggregation(termsAggregationBuilder3).subAggregation(termsAggregationBuilder4).subAggregation(termsAggregationBuilder5);
//2.再在用户编号里统计金额分组
termsAggregationBuilder2.subAggregation(AggregationBuilders.sum("money").field("famt"));
//2.1按金额倒序排列
List<FieldSortBuilder> fieldSorts=new ArrayList<>();
fieldSorts.add(new FieldSortBuilder("money").order(SortOrder.DESC));
termsAggregationBuilder2.subAggregation(new BucketSortPipelineAggregationBuilder("bucket_field", fieldSorts).size(searchSize));
//3.拼接分组
termsAggregationBuilder.subAggregation(termsAggregationBuilder2);

3.执行查询语句

这个总的语句相当与 select（支付方式，其他字段，(用户，sum(amt) ) as b ）as a,sum(amt) from 表 group by a ,其中a是以支付方式为主键的一个分组对象，a对象中包含了支付方式，其他字段和用户对象b的分组。b对象是以用户为主键的用户，用户总金额分组。

//总的分组，把第二步创建的分组看作一个对象，在进行总分组
searchSourceBuilder.aggregation(termsAggregationBuilder);
searchSourceBuilder.aggregation(AggregationBuilders.sum("totalAmt").field("famt"));
//执行ES的查询
SearchResponse response = ESUtils.findAll(payTradeIndex, payTradeType, searchSourceBuilder, null);

4.取值对象

取值总金额，上面最外层的sum(amt)就是所用订单的总金额

取值a对象的分组，可以获取分组字段的值和b对象

再取值b对象里面的用户和金额（这个金额就是对应的每个用户和支付方式分组统计的总金额了）

Aggregations aggregations = response.getAggregations();
Sum totalAmtSum = aggregations.get("totalAmt");
//总金额
double totalAmt = totalAmtSum.getValue();
DecimalFormat df = new DecimalFormat("0.00");
String totalMoney = df.format(totalAmt / 100);

Map<String, Aggregation> aggMap = response.getAggregations().asMap();
ParsedStringTerms codeTerms = (ParsedStringTerms) aggMap.get("fchannelTypeId");
Iterator<Terms.Bucket> codeBucketIt = (Iterator<Terms.Bucket>) codeTerms.getBuckets().iterator();
while (codeBucketIt.hasNext()) {
    Terms.Bucket codeBucket = codeBucketIt.next();

    //用户编号的分组terms对象
    ParsedStringTerms nameTerms = (ParsedStringTerms) codeBucket.getAggregations().asMap().get("fmerchantTypeId");
    //渠道商名称
    ParsedStringTerms nameTerms1 = (ParsedStringTerms) codeBucket.getAggregations().asMap().get("fbankTypeName");
    String fbankTypeName = nameTerms1.getBuckets().get(0).getKey().toString();
    //渠道商编号
    ParsedStringTerms nameTerms2 = (ParsedStringTerms) codeBucket.getAggregations().asMap().get("fbankTypeCode");
    String fbankTypeCode = nameTerms2.getBuckets().get(0).getKey().toString();
    //支付渠道名称
    ParsedStringTerms nameTerms3 = (ParsedStringTerms) codeBucket.getAggregations().asMap().get("fchannelTypeName");
    String fchannelTypeName = nameTerms3.getBuckets().get(0).getKey().toString();


    Iterator<Terms.Bucket> nameBucketIt = (Iterator<Terms.Bucket>) nameTerms.getBuckets().iterator();
    while (nameBucketIt.hasNext()){
        Terms.Bucket nameBucket = nameBucketIt.next();
        //金额
        Sum term = nameBucket.getAggregations().get("money");
        String money = df.format(term.getValue() / 100);
        //用户编号
        String fmerchantTypeId = nameBucket.getKey().toString();
        //统计笔数
        Long count = nameBucket.getDocCount();

        esDataList.add(new MerchantChannelCountModel(fmerchantTypeId,null,fbankTypeCode,fbankTypeName,
                                                     String.valueOf(codeBucket.getKey()),fchannelTypeName,money,
                                                     count.intValue()));
    }

}