Purpose
This function calculates the exact percentile of a specified column. It sorts the input column in ascending order and returns the corresponding interpolated value based on the given percentile (percentile).
Limitations
The current version of PERCENTILE_CONT does not support the DATETIME data type.
Syntax statement
PERCENTILE_CONT(expr, percentile)
Parameters
expr: The target column for which the percentile needs to be calculated. It must be a numeric type. For specific supported types, see Supported types.Note
PERCENTILE_CONTautomatically ignoresNULLvalues in the input column.percentile: the specified percentile value, indicating the position of the target value in the data distribution. The value must be a floating-point constant in the range [0, 1].
Supported types
- Integer types:
TINYINT,SMALLINT,MEDIUMINT,INT/INTEGER, andBIGINT. - Fixed-point types:
DECIMALandNUMERIC. - Floating-point types:
FLOATandDOUBLE. - Bit-Value type:
BIT.
Return type
The return type is the same as that of expr.
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 between the two nearest input values.
Examples
Create a test table named
test_tbl1containing columnscol1andcol2.CREATE TABLE test_tbl1(col1 VARCHAR(10), col2 INT);Insert test data into the
test_tbl1table.INSERT INTO test_tbl1 VALUES ('A1', 80), ('A1', 100), ('A1', NULL), ('B1', 60), ('B1', 70), ('B1', 85), ('C1', 75), ('C1', 80), ('C1', 85), ('C1', 99);Use
PERCENTILE_CONTto calculate the median (i.e., the 50th percentile) for each category in columncol1.SELECT col1, PERCENTILE_CONT(col2, 0.5) FROM test_tbl1 GROUP BY col1;The return result is as follows:
+------+----------------------------+ | col1 | PERCENTILE_CONT(col2, 0.5) | +------+----------------------------+ | A1 | 90 | | B1 | 70 | | C1 | 82.5 | +------+----------------------------+ 3 rows in set