GBase 8a Percentile Functions: percentile_cont and percentile_disc

GBase 8a supports two percentile functions — percentile_cont (continuous, with linear interpolation) and percentile_disc (discrete, returning an actual data value). These are especially useful for statistical analysis in a gbase database. This article demonstrates their syntax and behaviour with a concrete example.

Test Environment and Data

Version: 9.5.3.28.18. The sample table t1 contains 10 rows across three groups.

SELECT name, id FROM t1 ORDER BY name, id;
+-------+------+
| name  | id   |
+-------+------+
| Name0 |    3 |
| Name0 |    6 |
| Name0 |    9 |
| Name1 |    1 |
| Name1 |    4 |
| Name1 |    7 |
| Name1 |   10 |
| Name2 |    2 |
| Name2 |    5 |
| Name2 |    8 |
+-------+------+

percentile_cont – Continuous Percentile

Syntax

PERCENTILE_CONT ( numeric_literal )
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

• numeric_literal: Percentile value between 0.0 and 1.0.
• WITHIN GROUP (ORDER BY ...) specifies the sorted list.
• OVER (PARTITION BY ...) partitions the data.

Example

SELECT name, id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY id)
           OVER (PARTITION BY name) AS contId
FROM t1 ORDER BY name, id;
+-------+------+--------+
| name  | id   | contId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |    5.5 |
| Name1 |    4 |    5.5 |
| Name1 |    7 |    5.5 |
| Name1 |   10 |    5.5 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+

Interpolation Logic

When the exact percentile position falls on a data point, that value is returned (e.g., Name0 with 3 values — position 0.5 returns the second value, 6). When the position falls between two points, linear interpolation is applied.

For Name1 (4 values), the positions are 0, 1/3, 2/3, 1. The requested percentile 0.5 lies between 1/3 (value=4) and 2/3 (value=7). The interpolated value is calculated as:

4 + (7-4) × ((0.5 - 1/3) / (2/3 - 1/3)) = 5.5

percentile_disc – Discrete Percentile

Syntax

Identical to percentile_cont, except the function name.

PERCENTILE_DISC ( numeric_literal )
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

Example

SELECT name, id,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY id)
           OVER (PARTITION BY name) AS discId
FROM t1 ORDER BY name, id;
+-------+------+--------+
| name  | id   | discId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |      4 |
| Name1 |    4 |      4 |
| Name1 |    7 |      4 |
| Name1 |   10 |      4 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+

Nearest-Value Logic

percentile_disc picks the nearest actual data value. When the distance to two adjacent positions is equal, the smaller value is chosen. For Name1, 0.5 is equidistant from 1/3 and 2/3, so it returns 4. If you specify 0.500000001, it leans toward 2/3 and returns 7.

SELECT name, id,
       PERCENTILE_DISC(0.500000001) WITHIN GROUP (ORDER BY id)
           OVER (PARTITION BY name) AS discId
FROM t1 ORDER BY name, id;
+-------+------+--------+
| name  | id   | discId |
+-------+------+--------+
| Name0 |    3 |      6 |
| Name0 |    6 |      6 |
| Name0 |    9 |      6 |
| Name1 |    1 |      7 |
| Name1 |    4 |      7 |
| Name1 |    7 |      7 |
| Name1 |   10 |      7 |
| Name2 |    2 |      5 |
| Name2 |    5 |      5 |
| Name2 |    8 |      5 |
+-------+------+--------+

Summary

Both functions support window partitioning, making them ideal for grouped percentile analysis in a gbase database. percentile_cont provides a smooth, interpolated estimate, while percentile_disc restricts results to actual data values. Choose the one that best fits your analytical needs in GBASE's MPP environment.