ES对应mysql的groupby分组查询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.sQuery("ftradeStatus", Arrays.asList("1", "2", "3", "4")));
//⽤户，⽀付⽅式不能为空
boolQueryBuilder.istsQuery("fmerchantId"));
boolQueryBuilder.istsQuery("fchannelId"));
boolQueryBuilder.istsQuery("fchannelName"));
String startDate = ("startDate") == null ? null : ("startDate").toString();
String endDate = ("endDate") == null ? null : ("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，s("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 = s("fchannelTypeId").field(
"fchannelId").size(searchSize).order(BucketOrder.key(true));//⽀付⽅式ID
TermsAggregationBuilder termsAggregationBuilder2 = s("fmerchantTypeId")
.field(
"fmerchantId");//⽤户ID编号
TermsAggregationBuilder termsAggregationBuilder3 = s("fbankTypeCode").field(
"fbankCode");//渠道商编号
TermsAggregationBuilder termsAggregationBuilder4 = s("fbankTypeName").field(
"fbankName.keyword");//渠道商名称
TermsAggregationBuilder termsAggregationBuilder5 = s("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 = Aggregations();
Sum totalAmtSum = ("totalAmt");
//总⾦额
double totalAmt = Value();
DecimalFormat df = new DecimalFormat("0.00");
String totalMoney = df.format(totalAmt / 100);
Map<String, Aggregation> aggMap = Aggregations().asMap();
ParsedStringTerms codeTerms = (ParsedStringTerms) ("fchannelTypeId");
Iterator<Terms.Bucket> codeBucketIt = (Iterator<Terms.Bucket>) Buckets().iterator();
while (codeBucketIt.hasNext()) {
Terms.Bucket codeBucket = ();
//⽤户编号的分组terms对象
ParsedStringTerms nameTerms = (ParsedStringTerms) Aggregations().asMap().get("fmerchantTypeId");
//渠道商名称
ParsedStringTerms nameTerms1 = (ParsedStringTerms) Aggregations().asMap().get("fbankTypeName");
String fbankTypeName = Buckets().get(0).getKey().toString();
//渠道商编号
ParsedStringTerms nameTerms2 = (ParsedStringTerms) Aggregations().asMap().get("fbankTypeCode");
String fbankTypeCode = Buckets().get(0).getKey().toString();
groupby分组/
/⽀付渠道名称
ParsedStringTerms nameTerms3 = (ParsedStringTerms) Aggregations().asMap().get("fchannelTypeName");
String fchannelTypeName = Buckets().get(0).getKey().toString();
Iterator<Terms.Bucket> nameBucketIt = (Iterator<Terms.Bucket>) Buckets().iterator();
while (nameBucketIt.hasNext()){
Terms.Bucket nameBucket = ();
//⾦额
Sum term = Aggregations().get("money");
String money = df.Value() / 100);
//⽤户编号
String fmerchantTypeId = Key().toString();
//统计笔数
Long count = DocCount();
esDataList.add(new MerchantChannelCountModel(fmerchantTypeId,null,fbankTypeCode,fbankTypeName, String.Key()),fchannelTypeName,money,
count.intValue()));
}
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论