es总结记录

1、二次过滤“平均金额”

根据动态算出来的“平均金额”，通过外面来的“最大值”和“最小值”，去比对“平均金额”是否处在“最大值”和“最小值”之间，然后再根据“平均金额”排序

        //1-最低档，2-低档，3-高档，4-最高档
        List<Integer> priceGrade = Arrays.asList(1, 2, 3, 4);
//        List<Integer> priceGrade = Arrays.asList(1);

        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

        // 构建布尔查询
        BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();

        //时间范围筛选
        boolQueryBuilder.must(QueryBuilders.rangeQuery("settlementTime")
                .gte(startDate.toInstant(ZoneOffset.of("+8")).toEpochMilli())
                .lte(endDate.toInstant(ZoneOffset.of("+8")).toEpochMilli() + 999));

        // 查询未删除订单
        boolQueryBuilder.must(QueryBuilders.matchQuery("deleted", false));

        // 行程单，主状态为“已结算”，子状态为“已转账”，2023-09-13确认
        boolQueryBuilder.must(QueryBuilders.matchQuery("status", ItineraryStatusEnum.HAD_SETTLED.getStatus()));
        boolQueryBuilder.must(QueryBuilders.matchQuery("childStatus", HadSettledChildStatusEnum.HAD_APPLY_TRANSFER.getStatus()));

        //查询非模板的数据
        QueryBuilder queryBuilder1 = QueryBuilders.boolQuery()
                .mustNot(QueryBuilders.existsQuery("isTemplate"));
        boolQueryBuilder.should(queryBuilder1);
        boolQueryBuilder.should(QueryBuilders.termQuery("isTemplate", false));
        //团单数
        AggregationBuilder countOidAgg = AggregationBuilders.count("count_oid").field("oid");
        //计算人数
        AggregationBuilder touristCountSumAgg = AggregationBuilders.sum("sum_touristCount").field("touristCount");
        //计算金额（相加）
        AggregationBuilder totalFeeSumAgg = AggregationBuilders.sum("sum_totalFee").field("totalFee");
        // 人均费用排序
        BucketSortPipelineAggregationBuilder bucketSortDesc = PipelineAggregatorBuilders.bucketSort(
                "sort_by_average_spending",
                Collections.singletonList(new FieldSortBuilder("average_spending").order(SortOrder.DESC))
        );
        // 映射map
        Map<String, String> params = new HashMap<>();
        params.put("totalFee", "sum_totalFee");
        params.put("touristCount", "sum_touristCount");
        // 锁定average_spending的值，使其大于1000，小于5000
        Map<String, String> bucketSelectorParams = new HashMap<>();
        bucketSelectorParams.put("average_spending", "average_spending");
        bucketSelectorParams.put("totalFee", "sum_totalFee");
        bucketSelectorParams.put("touristCount", "sum_touristCount");

        for (Integer integer : priceGrade) {
            // 动态参数
            Map<String, Object> scriptParams = new HashMap<>();
//            scriptParams.put("minSpending", 0);  // 动态赋值的最小值
//            scriptParams.put("maxSpending", 800000);  // 动态赋值的最大值
            //1-最低档，2-低档，3-高档，4-最高档
            switch (integer) {
                case 1:
                    if (contractPriceRange.getMinPrice() > 0) {
                        if (contractPriceRange.getMinPrice() > 1) {
                            scriptParams.put("minSpending", (contractPriceRange.getMinPrice() - 1) * 100);  // 动态赋值的最小值
                        } else {
                            scriptParams.put("minSpending", (contractPriceRange.getMinPrice()) * 100);  // 动态赋值的最小值
                        }
                    } else {
                        scriptParams.put("minSpending", 0);  // 动态赋值的最小值
                    }
                    scriptParams.put("maxSpending", contractPriceRange.getMedianPrice() * 100);  // 动态赋值的最大值
                    break;
                case 2:
                    scriptParams.put("minSpending", contractPriceRange.getMedianPrice() * 100);  // 动态赋值的最小值
                    scriptParams.put("maxSpending", contractPriceRange.getMinimumPrice() * 100);  // 动态赋值的最大值
                    break;
                case 3:
                    scriptParams.put("minSpending", contractPriceRange.getMinimumPrice() * 100);  // 动态赋值的最小值
                    scriptParams.put("maxSpending", contractPriceRange.getTopPrice() * 100);  // 动态赋值的最大值
                    break;
                case 4:
                    scriptParams.put("minSpending", contractPriceRange.getTopPrice() * 100);  // 动态赋值的最小值
                    scriptParams.put("maxSpending", (contractPriceRange.getMaxPrice() + 1) * 100);  // 动态赋值的最大值
                    break;
            }


            // 内存统计，根据酒店区分
            // bucket_selector 聚合，用于过滤平均花费在 minSpending 到 maxSpending 之间的桶
            BucketSelectorPipelineAggregationBuilder bucketSelector = PipelineAggregatorBuilders.bucketSelector(
                    "spending_filter",
                    bucketSelectorParams,
                    new Script(
                            ScriptType.INLINE,
                            "painless",
                            "params.totalFee > 0 && params.touristCount > 0 && (params.totalFee / params.touristCount) > params.minSpending && (params.totalFee / params.touristCount) <= params.maxSpending",
                            scriptParams
                    )
            );
            //聚合
            AggregationBuilder aggregationBuilder = AggregationBuilders
                    .terms("term_subTravelOid" + integer).field("subTravelOid")
                    .size(10000)
                    .subAggregation(AggregationBuilders.topHits("subTravelName").fetchSource(new String[]{"subTravelName"}, null).size(1))
                    .subAggregation(countOidAgg)
                    .subAggregation(touristCountSumAgg)
                    .subAggregation(totalFeeSumAgg)
                    .subAggregation(PipelineAggregatorBuilders.bucketScript(
                            "average_spending",
                            params,
                            new Script("params.totalFee / params.touristCount")))
                    .subAggregation(bucketSelector)
                    .subAggregation(bucketSortDesc);

            searchSourceBuilder.aggregation(aggregationBuilder);
        }

        searchSourceBuilder.query(boolQueryBuilder);

        SearchResponse searchResponse = esOperationUtil.getSearchResponse(searchSourceBuilder, ElasticIndexConstants.TRAVEL_ITINERARY_VISUALIZATION_INDEX);

2、使用es动态计算的值，不能使用AVG计算平均值

            Script script = new Script("doc['totalFee'].size() > 0 " +
                    "&& doc['touristCount'].size() > 0 " +
                    "&& doc['totalFee'].value > 0 " +
                    "&& doc['touristCount'].value > 0 ? doc['totalFee'].value / doc['touristCount'].value : 0");
            // 平均消费
            AvgAggregationBuilder averageFeePerTouristAgg = AggregationBuilders.avg("average_spending").script(script);

“minPrice”和“maxPrice”是使用es的AVG计算的平均价格（金额/人数）

而“perCapitaConsumption”这里的平均值，是使用的script手动先sum拿到全部的“金额”和sum拿到全部的“人数”去计算的

总结：动态计算的数值，不要单纯的使用AVG计算