PERCENTILE_CONT

Purpose

This function calculates the exact percentile value of a specified column. The function sorts the input column in ascending order and returns the interpolated result based on the given percentile value (percentile).

Limitations

The PERCENTILE_CONT function in the current version does not support the DATETIME type.

Syntax

PERCENTILE_CONT(expr, percentile)

Parameters

• expr: the target column for which to calculate the percentile value. It must be a numeric type. For more information about the supported types, see Supported types.

  Note

  PERCENTILE_CONT automatically ignores NULL values in the input column.

• percentile: the specified percentile value, which indicates the position of the target value in the data distribution. It is a floating-point constant in the range [0, 1].

Supported types

• Integer types: TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT.
• Fixed-point types: DECIMAL and NUMERIC.
• Floating-point types: FLOAT and DOUBLE.
• Bit-value type: BIT.

Return type

The return type is the same as that of expr.

It returns a value at the specified percentile. If no input value is exactly at the required percentile, it calculates the result using linear interpolation between the two closest input values.

Examples

1. Create a test table test_tbl1 with columns col1 and col2.

   CREATE TABLE test_tbl1(col1 VARCHAR(10), col2 INT);
   

2. Insert test data into the test_tbl1 table.

   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);
   

3. Use PERCENTILE_CONT to calculate the median (50th percentile) for each category in the col1 column.

   SELECT col1, PERCENTILE_CONT(col2, 0.5)
   FROM test_tbl1
   GROUP BY col1;
   

   The result is as follows:

   +------+----------------------------+
   | col1 | PERCENTILE_CONT(col2, 0.5) |
   +------+----------------------------+
   | A1   |                         90 |
   | B1   |                         70 |
   | C1   |                       82.5 |
   +------+----------------------------+
   3 rows in set