Elasticsearch 7 - get 95th percentile wihtout outliers

Viewed 107

Given a series of objects and for simplification purposes there I have added only one flight per date, but in reality there are multiple of the same type and this data is merely representative:

[
  {
    flightId: '1',
    flightType: 'it',
    date: "2019-05-13T00:00:00.000+01:00",
    stats: {
      flightSpeed: {
        Minimum: 22.85,
        Maximum: 200.12,
        Mean: 120.65
      },
      flightAltitude: {
        Minimum: 12.85,
        Maximum: 2000.12,
        Mean: 1200.65
      }
    }
  },
  {
    flightId: '2',
    flightType: 'it',
    date: "2019-05-20T00:00:00.000+01:00",
    stats: {
      flightSpeed: {
        Minimum: 12.85,
        Maximum: 300.12,
        Mean: 220.65
      },
      flightAltitude: {
        Minimum: 1.85,
        Maximum: 4000.12,
        Mean: 2200.65
      }
    }
  },
  {
    flightId: '3',
    flightType: 'it',
    date: "2019-05-27T00:00:00.000+01:00",
    stats: {
      flightSpeed: {
        Minimum: 23.85,
        Maximum: 390.12,
        Mean: 110.65
      },
      flightAltitude: {
        Minimum: 10.85,
        Maximum: 1900.12,
        Mean: 1189.65
      }
    }
  },
  {
    flightId: '4',
    flightType: 'it',
    date: "2019-06-03T00:00:00.000+01:00",
    stats: {
      flightSpeed: {
        Minimum: 22.85,
        Maximum: 200.12,
        Mean: 120.65
      },
      flightAltitude: {
        Minimum: 12.85,
        Maximum: 2000.12,
        Mean: 1200.65
      }
    }
  },
  {
    flightId: '5',
    flightType: 'dm',
    date: "2019-05-13T00:00:00.000+01:00",
    stats: {
      flightSpeed: {
        Minimum: 22.85,
        Maximum: 200.12,
        Mean: 120.65
      },
      flightAltitude: {
        Minimum: 12.85,
        Maximum: 2000.12,
        Mean: 1200.65
      }
    }
  }
]

I now want to be able to get the 95th percentile for the "stats" given a "flightType" and date range, excluding the outliers, so anything above the 95% should not be included in the response.

Looking at the elasticsearch percentile aggregation docs, getting the 95th percentile returns all that fall above the 95th percentile. In other words, the outliers I would like to exclude? I had initially run the following query:

GET /test/_search
{
  "aggs": {
      "flight": {
        "terms": {
          "field": "flightType.keyword",
          "size": 15
        },
        "aggs": {
          "Mean": {
            "percentiles": {
              "field": "stats.flightSpeed.Mean",
              "percents": [ 95 ]
            }
          },
          "Maximum": {
            "percentiles": {
              "field": "stats.flightSpeed.Maximum",
              "percents": [ 95 ]
            }
          },
          "Minimum": {
            "percentiles": {
              "field": "stats.flightSpeed.Minimum",
              "percents": [ 95 ]
            }
          },
          "date": {
            "date_histogram": {
              "field": "date",
              "calendar_interval": "1w",
              "time_zone": "Europe/London"
            },
            "aggs": {
              "Mean": {
                "percentiles": {
                  "field": "stats.flightSpeed.Mean",
                  "percents": [ 95 ]
                }
              },
              "Maximum": {
                "percentiles": {
                  "field": "stats.flightSpeed.Maximum",
                  "percents": [ 95 ]
                }
              },
              "Minimum": {
                "percentiles": {
                  "field": "stats.flightSpeed.Minimum",
                  "percents": [ 95 ]
                }
              }
            }
          }
        }
      }
    },
    "size": 0,
    "fields": [
      {
        "field": "date",
        "format": "date_time"
      }
    ],
    "query": {
      "bool": {
        "must": [{
            "term": {
              "flightType": "it"
            }
          }],
        "filter": [
          {
            "range": {
              "date": {
                "gte": "2019-05-16T00:00:00.000Z",
                "lte": "2019-09-16T23:59:59.999Z",
                "format": "strict_date_optional_time"
              }
            }
          }
        ]
      }
    }
}

So if I wanted to get exclude the outliers should I, by elasticsearch's definition above in the docs, use the 5th percentile instead? Also, does the aggregation of these values in my query return the final values I am looking for or would I then need to run a secondary query where I filter by date and Max, Min and Mean values and perform an aggregation on those returned values to get the new averages for Min, Max and Mean less the outliers (Similar to this answer)?

0 Answers
Related