PERCENTILE_CONT

Purpose

This function is used to calculate the exact percentile 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 and considerations

The current version of PERCENTILE_CONT does not support the DATETIME type.

Syntax

PERCENTILE_CONT(expr, percentile)

Parameters

• expr: the target column for which the percentile is to be calculated. 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, indicating 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) of column col1 for each category.

   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