APPX_MEDIAN Function

An aggregate function that returns a value that is approximately the median (midpoint) of values in the set of input values.

Syntax:

  1. APPX_MEDIAN([DISTINCT | ALL] expression)

This function works with any input type, because the only requirement is that the type supports less-than and greater-than comparison operators.

Usage notes:

Because the return value represents the estimated midpoint, it might not reflect the precise midpoint value, especially if the cardinality of the input values is very high. If the cardinality is low (up to approximately 20,000), the result is more accurate because the sampling considers all or almost all of the different values.

Return type: Same as the input value, except for CHAR and VARCHAR arguments which produce a STRING result

The return value is always the same as one of the input values, not an “in-between” value produced by averaging.

Restrictions:

This function cannot be used in an analytic context. That is, the OVER() clause is not allowed at all with this function.

The APPX_MEDIAN function returns only the first 10 characters for string values (string, varchar, char). Additional characters are truncated.

Examples:

The following example uses a table of a million random floating-point numbers ranging up to approximately 50,000. The average is approximately 25,000. Because of the random distribution, we would expect the median to be close to this same number. Computing the precise median is a more intensive operation than computing the average, because it requires keeping track of every distinct value and how many times each occurs. The APPX_MEDIAN() function uses a sampling algorithm to return an approximate result, which in this case is close to the expected value. To make sure that the value is not substantially out of range due to a skewed distribution, subsequent queries confirm that there are approximately 500,000 values higher than the APPX_MEDIAN() value, and approximately 500,000 values lower than the APPX_MEDIAN() value.

  1. [localhost:21000] > select min(x), max(x), avg(x) from million_numbers;
  2. +-------------------+-------------------+-------------------+
  3. | min(x)            | max(x)            | avg(x)            |
  4. +-------------------+-------------------+-------------------+
  5. | 4.725693727250069 | 49994.56852674231 | 24945.38563793553 |
  6. +-------------------+-------------------+-------------------+
  7. [localhost:21000] > select appx_median(x) from million_numbers;
  8. +----------------+
  9. | appx_median(x) |
  10. +----------------+
  11. | 24721.6        |
  12. +----------------+
  13. [localhost:21000] > select count(x) as higher from million_numbers where x > (select appx_median(x) from million_numbers);
  14. +--------+
  15. | higher |
  16. +--------+
  17. | 502013 |
  18. +--------+
  19. [localhost:21000] > select count(x) as lower from million_numbers where x < (select appx_median(x) from million_numbers);
  20. +--------+
  21. | lower  |
  22. +--------+
  23. | 497987 |
  24. +--------+

The following example computes the approximate median using a subset of the values from the table, and then confirms that the result is a reasonable estimate for the midpoint.

  1. [localhost:21000] > select appx_median(x) from million_numbers where x between 1000 and 5000;
  2. +-------------------+
  3. | appx_median(x)    |
  4. +-------------------+
  5. | 3013.107787358159 |
  6. +-------------------+
  7. [localhost:21000] > select count(x) as higher from million_numbers where x between 1000 and 5000 and x > 3013.107787358159;
  8. +--------+
  9. | higher |
  10. +--------+
  11. | 37692  |
  12. +--------+
  13. [localhost:21000] > select count(x) as lower from million_numbers where x between 1000 and 5000 and x < 3013.107787358159;
  14. +-------+
  15. | lower |
  16. +-------+
  17. | 37089 |
  18. +-------+

Parent topic: Impala Aggregate Functions