Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

QUANTILE

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

This function calculates the given quantile value per group. QUANTILE can be applied to INT, FLOAT or DATE columns.

The given quantile has to be a float number between 0 (same as MIN) and 1.0 (same as MAX).

Syntax
QUANTILE ( table.column, quantile )
NULL handling

NULL values are ignored, meaning that they do not influence the result. If all values of a group are NULL, the result for this group is also NULL.

Examples

[1] Get 0 quantile of column.

Query

Column1

QUANTILE ( "Table"."Column" , 0.0 )

Input

Output

Table

Column : INT

1

-3

4

Result

Column1 : INT

-3

[2] Get 1.0 quantile of column.

Query

Column1

QUANTILE ( "Table"."Column" , 1.0 )

Input

Output

Table

Column : INT

1

-3

4

Result

Column1 : INT

4

[3] Get the min quantile by country.

Query

Column1

"Table"."Country"

Column2

QUANTILE ( "Table"."Values" , 0.0 )

Input

Output

Table

Country : STRING

Values : INT

'US'

3

'DE'

10

'DE'

5

'FR'

5

'US'

4

'US'

3

Result

Column1 : STRING

Column2 : INT

'DE'

5

'FR'

5

'US'

3

[4] Get the 0.5 quantile mixed with nulls, grouped by country.

Query

Column1

"Table"."Country"

Column2

QUANTILE ( "Table"."Values" , 0.5 )

Input

Output

Table

Country : STRING

Values : INT

'FR'

10

'FR'

null

'DE'

null

Result

Column1 : STRING

Column2 : INT

'DE'

null

'FR'

10

See also: