median()

Calculates the median value (50th percentile) of the values returned by an expression.

Syntax

Parameters

EXPR

An expression that returns the values from which to calculate the median.

INTERPOLATION

(Optional) The method for handling two adjacent values when no data falls exactly at the midpoint. 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 midpoint.

Description

The median() function internally behaves identically to percentile(EXPR, 50.0 [, INTERPOLATION]). It sorts all values and returns the value at the 50th percentile. Null values are ignored. Non-numeric values can also be sorted to find a median, but interpolation (INTERPOLATION) is applied only to numeric values.

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

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

Error codes

Usage examples

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

1. Calculate the median response size for all requests

   table WEB_APACHE_SAMPLE | stats median(bytes)
   

2. Calculate the median response size per HTTP method

   table WEB_APACHE_SAMPLE | stats median(bytes) by method
   

3. Use the midpoint interpolation option

   table WEB_APACHE_SAMPLE | stats median(bytes, "midpoint") by method
   

4. Null value handling

   json "[{'val': 10}, {'val': null}, {'val': 30}]"
   | stats median(val)
   | # median(val): 20
   

Compatibility

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