ES nested 一对多 聚合分组统计、bucket_script脚本

1.类似sql

select sum(item.paymentAmount) from order_main main
left join order_item item on main.id = item.main_id
where item.sku in (100000047)
group by orderItemList.cityName
order by  sum(item.paymentAmount)  desc

2.es queryDSL

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "orderMain.createTime": {
                            "gt": "2020-06-01 00:00:00",
                            "lt": "2020-12-01 00:00:00"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
      //先根据主表的区域来分组
        "areaGroup": {
            "terms": {
                "field": "orderMain.cityName.keyword",
                "order" : [
                    {
                        "itemNest >> itemFilter >> sumSku" : "desc"
                    }
                ]
            },
            "aggs": {
            //nested进到item进行筛选和操作
                "itemNest": {
                    "nested": {
                        "path": "orderItemList"
                    },
                    "aggs": {
                        "itemFilter": {
                            "filter": {
                                "bool": {
                                    "must": []
                                }
                            },
                            "aggs": {
                            //统计过滤后的金额
                                "sumSku": {
                                    "sum": {
                                        "field": "orderItemList.paymentAmount"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

客单价
SQL

SELECT COUNT(DISTINCT(main.member_id)),SUM(item.paymentAmount) / count(DISTINCT(main.member_id)) FROM order_main main
LEFT JOIN order_item item ON main.id = item.order_id
group by main.city_name

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "orderMain.createTime": {
                            "gt": "2020-06-01 00:00:00",
                            "lt": "2020-12-01 00:00:00"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "itemNest": {
            "nested": {
                "path": "orderItemList"
            },
            "aggs": {
                "itemFilter": {
                    "filter": {
                        "bool": {
                            "must": [
                                {
                                    "terms": {
                                        "orderItemList.skuId": [
                                            100000047
                                        ]
                                    }
                                }
                            ]
                        }
                    },
                    "aggs": {
                     //先根据主表的区域来分组
                        "areaGroup": {
                            "terms": {
                                "field": "orderItemList.cityName.keyword"
                            },
                            "aggs": {
                              //统计符合条件的子项的金额
                                "sumSku": {
                                    "sum": {
                                        "field": "orderItemList.paymentAmount"
                                    }
                                },
                                "reverseMain": {
                                //reverse_nested回到主表统计会员数
                                    "reverse_nested": {},
                                    "aggs": {
                                        "memberCnt": {
                                            "cardinality": {
                                                "field": "orderMain.memberId"
                                            }
                                        }
                                    }
                                },
                                "perTicketPrice": {
                                 //bucket_script脚本计算
                                    "bucket_script": {
                                        "buckets_path": {
                                            "sumSku": "sumSku",
                                            "memberCnt" : "reverseMain >> memberCnt"
                                        },
                                        "script": {
                                            "source": "params.sumSku / params.memberCnt"
                                        },
                                        "format": "#.####"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

对应的java代码:

WrapperQueryBuilder wrapperQuery = getBaseQuery(request);

                //nest统计
                NestedAggregationBuilder orderItemNest = AggregationBuilders.nested("orderItemNest", "orderItemList");

                //aggs过滤条件
                BoolQueryBuilder aggsFilterQuery = QueryBuilders.boolQuery();
                if(CollectionUtils.isNotEmpty(request.getSkuIdList())){
                    TermsQueryBuilder skuTermQuery = QueryBuilders.termsQuery("orderItemList.skuId", request.getSkuIdList());
                    aggsFilterQuery.must(skuTermQuery);
                }


                //filter过滤
                FilterAggregationBuilder filterAggregationBuilder = AggregationBuilders.filter("filterItem", aggsFilterQuery);
                orderItemNest.subAggregation(filterAggregationBuilder);


                //按区域分组
                TermsAggregationBuilder groupAreaAggs = AggregationBuilders
                        .terms("area")
                        .order(BucketOrder.aggregation("sumSku", Boolean.FALSE))
                        .field("orderItemList.cityName.keyword");

                filterAggregationBuilder.subAggregation(groupAreaAggs);

                if (Objects.nonNull(request.getTopSize())) {
                    groupAreaAggs.size(request.getTopSize());
                }

                //统计商品金额
                SumAggregationBuilder sumSkuAgg = AggregationBuilders.sum("sumSku").field("orderItemList.paymentAmount");

                //会员数(去重)
                CardinalityAggregationBuilder memberAgg = AggregationBuilders.cardinality("memberCount").field("orderMain.memberId");

                //回到根目录统计会员数
                ReverseNestedAggregationBuilder reverseNestedRoot = AggregationBuilders.reverseNested("reverseNestedRoot");
                reverseNestedRoot.subAggregation(memberAgg);

                //脚本路径
                HashMap<String, String> bucketsPathHashMap = new HashMap<>(2);
                bucketsPathHashMap.put("sumSku", "sumSku");
                bucketsPathHashMap.put("memberCount", "reverseNestedRoot >> memberCount");


                groupAreaAggs
                        .subAggregation(sumSkuAgg)
                        .subAggregation(reverseNestedRoot)
                        .subAggregation(PipelineAggregatorBuilders.bucketScript("perTicketSales", bucketsPathHashMap, new Script("params.sumSku / params.memberCount")).format("#.####"));


                Aggregations aggregations = executeQuery(wrapperQuery, orderItemNest);

                InternalNested orderItemNestResult = (InternalNested)aggregations.getAsMap().get("orderItemNest");

                InternalFilter filterItemResult = (InternalFilter)orderItemNestResult.getAggregations().getAsMap().get("filterItem");


                StringTerms histogram = (StringTerms) filterItemResult.getAggregations().asMap().get("area");