How to calculate the standard deviation in a transform?

Elastic Stack Elasticsearch
1

I am trying to create a transform report, but I'm unable to create a range based on the avg(total).

For example, I want to assign customers into a bucket based on their average total spend, like the range $0-$100.

Can you help me generate a list of customers whose average total falls within this range?

I wanted to create the monthly report of customers.

image
image1177×383 14.3 KB

transforms Elastic Stack

2

From Kibana to Elasticsearch

3

Removed visualisation

4

What does the field look like in the mapping? If it is just a number, you could use avg on it as is: Avg aggregation | Elasticsearch Guide [8.15] | Elastic

Something like this would create buckets maintaining every customers averages, and then you could search over it for the desired range

POST _transform/_preview
{
  "source": {
    "index": "customer-orders"
  },
  "pivot": {
    "group_by": {
      "customer_bucket": {
        "terms": {
          "field": "customer",
          "missing_bucket": true
        }
      }
    },
    "aggs": {
      "avg_total": {
        "avg": {
          "field": "total"
        }
      }
    }
  }
}

Or something like this would use Bucket Selector to record the value in the range that you want: Bucket selector aggregation | Elasticsearch Guide [8.15] | Elastic

POST _transform/_preview
{
  "source": {
    "index": "customer-orders"
  },
  "pivot": {
    "group_by": {
      "customer_bucket": {
        "terms": {
          "field": "customer",
          "missing_bucket": true
        }
      }
    },
    "aggs": {
      "avg_total": {
        "avg": {
          "field": "total"
        }
      },
      "avg_total_bucket_filter": {
        "bucket_selector": {
          "buckets_path": {
            "avg_total": "avg_total"
          },
          "script": "params.avg_total >= 0 && params.avg_total <= 100"
        }
      }
    }
  }
}
5

Hi @Patrick_Whelan

i think i have missed the point i wanted to calculate the standard devitition in transfrom.

i have achieved the average and sum.

Thank you for this guide.

6

Transforms does not support extended_stats which calculate the standard deviation in the search request. Though standard deviation can be approximated via the supported percentiles or median_absolute_deviation, both are probably the easiest approach to this? Alternatively, they can be recalculated using a scripted_metric, though that can significantly reduce a search request's performance depending on the script.

If a normal distribution is given, percentiles will return about the same as the standard deviation in extended_stats.

9

Using scripted_metric its possibe to calculate that thanks for the guide