Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

QUANTILE

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

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

Column : 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