如何在Elasticsearch查询中对avg聚合结果向上取整？

针对你的需求，提供两种可行的实现方式，按需选择：

方案1：对Avg聚合结果直接向上取整（推荐） #

通过bucket_script聚合在avg计算完成后，对结果应用向上取整函数，这种方式能保证先得到精确平均值，再做取整处理。

核心修改点： #

1. 在原有avg聚合下新增一个bucket_script聚合，用于处理取整逻辑
2. 若需要按取整后的值排序，修改terms聚合的排序字段为新的聚合名称

代码示例（整数向上取整）：

将原聚合部分替换为以下内容：

"aggregations": {
    "2": {
        "terms": {
            "field": "tag.country.keyword",
            "size": 20,
            "min_doc_count": 1,
            "shard_min_doc_count": 0,
            "show_term_doc_count_error": false,
            "order": [
                {
                    "avg_rounded": "desc"
                },
                {
                    "_key": "asc"
                }
            ]
        },
        "aggregations": {
            "1": {
                "avg": {
                    "field": "my_field"
                }
            },
            "avg_rounded": {
                "bucket_script": {
                    "buckets_path": {
                        "avgVal": "1"
                    },
                    "script": "Math.ceil(params.avgVal)"
                }
            }
        }
    }
}

保留指定小数位向上取整：

如果需要保留1位小数后向上取整（例如5.12→5.2），修改bucket_script的脚本内容即可：

"avg_rounded": {
    "bucket_script": {
        "buckets_path": {
            "avgVal": "1"
        },
        "script": "Math.ceil(params.avgVal * 10) / 10"
    }
}

• 保留2位小数则将10替换为100，以此类推。

方案2：先对每个字段值取整再计算平均值 #

若你的需求是先对每条数据的my_field值向上取整，再计算平均值，直接修改avg聚合为脚本模式：

"1": {
    "avg": {
        "script": {
            "source": "Math.ceil(doc['my_field'].value)"
        }
    }
}

完整查询示例（方案1-整数取整） #

{
    "size": 0,
    "query": {
        "bool": {
            "filter": [
                {
                    "match_all": {
                        "boost": 1
                    }
                },
                {
                    "range": {
                        "@timestamp": {
                            "from": "{{period_end}}||-24h",
                            "to": "{{period_end}}",
                            "include_lower": true,
                            "include_upper": true,
                            "format": "epoch_millis",
                            "boost": 1
                        }
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1
        }
    },
    "_source": {
        "includes": [],
        "excludes": []
    },
    "stored_fields": "*",
    "docvalue_fields": [
        {
            "field": "@timestamp",
            "format": "date_time"
        },
        {
            "field": "timestamp",
            "format": "date_time"
        }
    ],
    "script_fields": {},
    "aggregations": {
        "2": {
            "terms": {
                "field": "tag.country.keyword",
                "size": 20,
                "min_doc_count": 1,
                "shard_min_doc_count": 0,
                "show_term_doc_count_error": false,
                "order": [
                    {
                        "avg_rounded": "desc"
                    },
                    {
                        "_key": "asc"
                    }
                ]
            },
            "aggregations": {
                "1": {
                    "avg": {
                        "field": "my_field"
                    }
                },
                "avg_rounded": {
                    "bucket_script": {
                        "buckets_path": {
                            "avgVal": "1"
                        },
                        "script": "Math.ceil(params.avgVal)"
                    }
                }
            }
        }
    }
}

内容的提问来源于stack exchange，提问作者Marius Kunauskas