Elasticsearch 7：指定航班类型与日期范围下排除异常值计算95百分位数的技术疑问

Hey there, let's break down your two questions one by one and figure out the right approach for your query needs:

疑问1：是否应该改用5th百分位数来排除异常值？ #

Nope, that's not the right fit for your use case. Let's clarify what these percentiles mean:

• The 95th percentile value is exactly the threshold you need: it represents the number where 95% of your data points are less than or equal to it. The remaining 5% (values higher than this threshold) are the outliers you want to exclude.
• The 5th percentile would target the bottom 5% of your data (values lower than this threshold) — which is the opposite of what you're trying to achieve.

So you were correct to start with the 95th percentile as your outlier cutoff.

疑问2：当前查询结果是否是最终值？是否需要二次查询？ #

Your current query only calculates the 95th percentile values for your target fields (Mean/Maximum/Minimum flight speed) across the specified flight type and date range. It does not filter out the outliers (values above the 95th percentile) and then recalculate the new Min/Max/Mean for the cleaned dataset.

To get the final stats you need, you'll need a two-step process:

Step 1: Fetch the 95th percentile thresholds #

First, run a query to get the 95th percentile values for each of your target fields. This gives you the cutoff points to filter out outliers later:

GET /test/_search
{
  "size": 0,
  "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"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "flight_speed_mean_95": {
      "percentiles": {
        "field": "stats.flightSpeed.Mean",
        "percents": [95]
      }
    },
    "flight_speed_max_95": {
      "percentiles": {
        "field": "stats.flightSpeed.Maximum",
        "percents": [95]
      }
    },
    "flight_speed_min_95": {
      "percentiles": {
        "field": "stats.flightSpeed.Minimum",
        "percents": [95]
      }
    }
  }
}

Step 2: Filter outliers and recalculate aggregations #

Take the 95th percentile values from the first query (let's call them mean_95_val, max_95_val, min_95_val) and use them to filter out any documents where flight speed stats exceed these thresholds. Then run your desired aggregations on the cleaned dataset:

GET /test/_search
{
  "size": 0,
  "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"
            }
          }
        },
        {
          "range": {
            "stats.flightSpeed.Mean": {
              "lte": mean_95_val
            }
          }
        },
        {
          "range": {
            "stats.flightSpeed.Maximum": {
              "lte": max_95_val
            }
          }
        },
        {
          "range": {
            "stats.flightSpeed.Minimum": {
              "lte": min_95_val
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "flight": {
      "terms": {
        "field": "flightType.keyword",
        "size": 15
      },
      "aggs": {
        "filtered_mean_stats": {
          "stats": {
            "field": "stats.flightSpeed.Mean"
          }
        },
        "filtered_max_stats": {
          "stats": {
            "field": "stats.flightSpeed.Maximum"
          }
        },
        "filtered_min_stats": {
          "stats": {
            "field": "stats.flightSpeed.Minimum"
          }
        },
        "date": {
          "date_histogram": {
            "field": "date",
            "calendar_interval": "1w",
            "time_zone": "Europe/London"
          },
          "aggs": {
            "filtered_mean_stats": {
              "stats": {
                "field": "stats.flightSpeed.Mean"
              }
            },
            "filtered_max_stats": {
              "stats": {
                "field": "stats.flightSpeed.Maximum"
              }
            },
            "filtered_min_stats": {
              "stats": {
                "field": "stats.flightSpeed.Minimum"
              }
            }
          }
        }
      }
    }
  }
}

Note on single-query alternatives #

While it's technically possible to use pipeline aggregations (like bucket_selector) or scripted metrics to try and do this in one query, those approaches get complex fast — especially when dealing with multiple fields and nested aggregations. The two-step method is far more straightforward and easier to debug/maintain.

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