嵌套聚合值公式的弹性搜索排序

Question

我想对一个从嵌套聚合中获取值的公式进行排序，并使用父聚合文档计数来计算它。

我想根据这个公式的结果来排序：

national_averages_9_10.avg * national_averages_9_10.count / key.doc_count 其中key.doc_count =根桶文档计数

更具体地说，对于第一份成果文件，公式是：

9.543799991607665 * 100 / 194 = 4.919484

我有以下搜索：

GET student-grade/_search
{
  "size": 0,
  "aggs": {
    "schools": {
      "terms": {
        "field": "SCHOOL_NAME.keyword",
        "size": 2,
        "shard_size": 250,
        "min_doc_count": 20
      },
      "aggs": {
        "national_averages_9_10": {
          "filter": {
            "range": {
              "STUDENT_NATIONAL_AVERAGE_GRADE": {
                "gte": 9,
                "lte": 10
              }
            }
          },
          "aggs": {
            "range_stats": {
              "stats": {
                "field": "STUDENT_NATIONAL_AVERAGE_GRADE"
              }
            }
          }
        }
      }
    }
  }
}

这将产生以下示例输出：

  "aggregations": {
    "schools": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 10790,
      "buckets": [
        {
          "key": "Școala Gimnazială nr. 195",
          "doc_count": 194,
          "national_averages_9_10": {
            "doc_count": 100,
            "range_stats": {
              "count": 100,
              "min": 9.020000457763672,
              "max": 10,
              "avg": 9.543799991607665,
              "sum": 954.3799991607666
            }
          }
        },
        {
          "key": "Școala Gimnazială nr. 56",
          "doc_count": 178,
          "national_averages_9_10": {
            "doc_count": 110,
            "range_stats": {
              "count": 110,
              "min": 9,
              "max": 10,
              "avg": 9.566909139806574,
              "sum": 1052.3600053787231
            }
          }
        }
      ]
    }
  }

Answer 1

{
  "size": 0,
  "aggs": {
    "schools": {
      "terms": {
        "field": "SCHOOL_NAME.keyword"
      },
      "aggs": {
        "national_averages_9_10": {
          "filter": {
            "range": {
              "STUDENT_NATIONAL_AVERAGE_GRADE": {
                "gte": 9,
                "lte": 10
              }
            }
          },
          "aggs": {
            "range_stats": {
              "stats": {
                "field": "STUDENT_NATIONAL_AVERAGE_GRADE"
              }
            }
          }
        },
        "my_formula": {
          "bucket_script": {
            "buckets_path": {
              "school_total_count": "_count",
              "average_9_10_total_count": "national_averages_9_10._count",
              "average": "national_averages_9_10>range_stats.avg"
            },
            "script": "params.average * params.average_9_10_total_count / params.school_total_count"
          }
        }
      }
    }
  }
}