PERCENTILE_CONT

Description

This function calculates the exact percentile of a specified column. It 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 column for which to calculate the percentile. It must be a numeric type. For more information about the supported types, see [Supported types](#Supported types).

  info

  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 consistent with the type of expr.

It returns a value at the specified percentile. If no input value exactly matches 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 (0.002 sec)