Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 

Description

This function calculates the median per group. Median can be applied to INT, FLOAT or DATE columns.

The median is the middle element of a group. If the group has an even number of elements, the upper value of the two middle values is taken as the median.

Syntax

MEDIAN ( table.column )

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

Median of column with 3 rows.
Query
Column1
MEDIAN ( "Table"."Column" )
Input
Table
Column : INT
1
2
3
Output
Result
Column1 : INT
2



Median of column with 4 rows. If a group has an even number of entries, the upper value of the two middle values is taken.
Query
Column1
MEDIAN ( "Table"."Column" )
Input
Table
Column : INT
1
2
3
4
Output
Result
Column1 : INT
3



Get the median value by country.
Query
Column1
"Table"."Country"
Column2
MEDIAN ( "Table"."Values" )
Input
Table
Country : STRINGValues : INT
'US'
3
'DE'
10
'DE'
5
'FR'
5
'US'
4
'US'
3
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
10
'FR'
5
'US'
3



Get the median value mixed with nulls, grouped by country.
Query
Column1
"Table"."Country"
Column2
MEDIAN ( "Table"."Values" )
Input
Table
Country : STRINGValues : INT
'FR'
10
'FR'
null
'DE'
null
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
null
'FR'
10



  • No labels