Elasticsearch implements the division, addition, subtraction, multiplication and calculation of the two fields after aggregation

There is a requirement, the calculation method in sql is:

sum(cost) / 100 / sum(view_count) * 1000

Calculate the average price per thousand times, and the cost is stored by points.

In es, cost and view_count need to be aggregated before calculation. After tossing for a long time, record:

"aggs" => [
    'total_cost_count' => [
        'sum' => [
            'field' => 'cost'
        ]
    ],
    'total_view_count' => [
        'sum' => [
            'field' => 'view_count'
        ]
    ],
    '(total_cost / total_view)' => [
        'bucket_script' => [
            'buckets_path' => [
                'cost' => 'total_cost_count',
                'view_count' => 'total_view_count',
            ],
            'script' => [
                'source' => "params.cost / params.unit / params.view_count * params.days",
                'params' => [
                    'unit' => 100,
                    'days' => 1000,
                ]
            ]
        ]
    ]
],

Error message during debugging:

• Only sibling pipeline aggregations are allowed at the top level

  • Only sibling pipeline aggregation is allowed at the top level. That is, aggregation operations can only be in the same bucket.

Reference document: https://www.elastic.co/guide/en/elasticsearch/painless/7.13/painless-bucket-script-agg-context.html