percentile()

Calculates the value at a specified percentile among the values returned by an expression. perc() is an alias for percentile().

Syntax

percentile(EXPR, PERCENTILE [, INTERPOLATION])

Parameters

EXPR
An expression that returns the values from which to calculate the percentile.
PERCENTILE
The percentile to compute. Must be a numeric constant between 0.0 and 100.0. A value of 0.0 corresponds to the minimum, and 100.0 corresponds to the maximum.
INTERPOLATION
(Optional) The method for handling two adjacent values when no data falls exactly at the specified percentile position. The default is "linear".
  • "linear": Computes a weighted average based on the position between the two values.
  • "midpoint": Computes the average of the two values.
  • "lower": Selects the smaller of the two values.
  • "higher": Selects the larger of the two values.
  • "nearest": Selects the value closest to the specified percentile position.

Description

The percentile() function sorts all values and returns the value at the specified percentile position. When the position falls between two values, the result is computed from the adjacent values according to the INTERPOLATION method. For "linear" interpolation, the weighted average is applied only to numeric types; non-numeric values return the nearer of the two values. Null values are ignored.

The result of percentile(EXPR, 0.0) is identical to min(EXPR), percentile(EXPR, 100.0) is identical to max(EXPR), and percentile(EXPR, 50.0) is identical to median(EXPR).

Use this function in aggregation commands such as stats and timechart.

The percentile() function internally uses an external merge sort. It collects and sorts all values in the group before reading the value at the specified percentile position, so it uses memory and temporary disk space proportional to the number of records processed.

Error codes

CodeDescription
91020A required parameter is missing, PERCENTILE is not a number or is out of range, or INTERPOLATION is invalid.

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

  1. Calculate the 95th percentile of response sizes

    table WEB_APACHE_SAMPLE | stats percentile(bytes, 95)

  2. Calculate the 95th percentile of response sizes per HTTP method

    table WEB_APACHE_SAMPLE | stats percentile(bytes, 95) by method

  3. Compare lower/higher interpolation options

    table WEB_APACHE_SAMPLE | stats percentile(bytes, 50, "lower") as p50_lower, percentile(bytes, 50, "higher") as p50_higher

  4. Null value handling

    json "[{'val': 10}, {'val': null}, {'val': 30}]"
| stats percentile(val, 50.0)
| # Null values are ignored.

Compatibility

The percentile() function is available from Sonar 4.0.2404.0-u3072.