Alternative for PERCENTILE_CONT in MySQL/MariaDB

Question

I want to calculate percentile_cont on this table. In Oracle, the query would be

SELECT PERCENTILE_CONT(0.05) FROM sometable;

What would be it's alternative in MariaDB/MySQL?

Answer 1

While MariaDB 10.3.3 has support for these functions in the form of window functions (see Lukasz Szozda's answer), you can emulate them using window functions in MySQL 8 as well:

SELECT DISTINCT first_value(matrix_value) OVER (
  ORDER BY CASE WHEN p <= 0.05 THEN p END DESC /* NULLS LAST */
) x,
FROM (
  SELECT
    matrix_value,
    percent_rank() OVER (ORDER BY matrix_value) p,
  FROM some_table
) t;

I've blogged about this more in detail here.

Answer 2

MariaDB 10.3.3 introduced PERCENTILE_CONT, PERCENTILE_DISC, and MEDIAN windowed functions.

PERCENTILE_CONT

PERCENTILE_CONT() (standing for continuous percentile) is an ordered set aggregate function which can also be used as a window function. It returns a value which corresponds to the given fraction in the sort order. If required, it will interpolate between adjacent input items.

SELECT name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY star_rating) 
        OVER (PARTITION BY name) AS pc 
FROM book_rating;

Answer 3

There is no built in function for this in either MariaDB or MySQL, so you have to solve this on the SQL level (or by adding a user defined function written in C ...)

This might help with coming up with a SQL solution:

http://rpbouman.blogspot.de/2008/07/calculating-nth-percentile-in-mysql.html

Answer 4

MariaDB 10.2 has windowing functions.

For MySQL / older MariaDB, and assuming you just want the Nth percentile for a single set of values.

This is best done form app code, but could be built into a stored routine.

1. Count the total number of rows: SELECT COUNT(*) FROM tbl.
2. Construct and execute a SELECT with LIMIT n,1 where n is computed as the percentile times the count, then filled into the query.

If you need to interpolate between two values, it gets messier. Do you need that, too?