percentile_cont
percentile_cont
Description
Computes the percentile value of expr
with linear interpolation.
Syntax
PERCENTILE_CONT (expr, percentile)
Parameters
-
expr
: the expression by which to order the values. It must be of numeric data types, DATE, or DATETIME. For example, if you want to find the median score of physics, specify the column that contains the physics scores. -
percentile
: the percentile of the value you want to find. It is a constant floating-point number from 0 to 1. For example, if you want to find the median value, set this parameter to0.5
.
Return value
Returns a value that is at the specified percentile. If no input value lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values.
The data type is the same as expr
.
Usage notes
This function ignores NULLs.
Examples
Suppose there is a table named exam
with the following data.
select * from exam order by Subject;
+-----------+-------+
| Subject | Score |
+-----------+-------+
| chemistry | 80 |
| chemistry | 100 |
| chemistry | NULL |
| math | 60 |
| math | 70 |
| math | 85 |
| physics | 75 |
| physics | 80 |
| physics | 85 |
| physics | 99 |
+-----------+-------+
Calculate the median score of each subject while ignoring NULLs.
Query:
SELECT Subject, PERCENTILE_CONT (Score, 0.5) FROM exam group by Subject;
Result:
+-----------+-----------------------------+
| Subject | percentile_cont(Score, 0.5) |
+-----------+-----------------------------+
| chemistry | 90 |
| math | 70 |
| physics | 82.5 |
+-----------+-----------------------------+