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

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 don't influence the result. If all values of a group are null, the result for this group is also null.

Examples


Get 0 quantile of column.

Query
Column1
QUANTILE ( "Table"."Column", 0.0 )
Input
Table
Column : INT
1
-3
4
Output
Result
Column1 : INT
-3



Get 1.0 quantile of column.

Query
Column1
QUANTILE ( "Table"."Column", 1.0 )
Input
Table
Column : INT
1
-3
4
Output
Result
Column1 : INT
4



Get the min quantile by country.

Query
Column1
"Table"."Country"
Column2
QUANTILE ( "Table"."Values", 0.0 )
Input
Table
Country : STRINGValues : INT
'US'
3
'DE'
10
'DE'
5
'FR'
5
'US'
4
'US'
3
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
5
'FR'
5
'US'
3



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

Query
Column1
"Table"."Country"
Column2
QUANTILE ( "Table"."Values" , 0.5)
Input
Table
Country : STRINGValues : INT
'FR'
10
'FR'
null
'DE'
null
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
null
'FR'
10


  • No labels