CODEWITHSUNDEEP
mysqloracle-databasemariadbanalytic-functions
sushma
sushma

Reputation: 71

Alternative for PERCENTILE_CONT in MySQL/MariaDB

enter image description here

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?

Upvotes: 4

Views: 13200

Answers (4)

Lukas Eder
Lukas Eder

Reputation: 221106

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.

Upvotes: 4

Rick James
Rick James

Reputation: 142366

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?

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

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;

Upvotes: 3

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

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

Upvotes: 1

Related Questions