Skip to main content

QUANTILE

Description

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

The quantile parameter passed to this function has to be an expression that results in a constant float number between 0 (same as MIN) and 1.0 (same as MAX).

To calculate a quantile value for a group, we first sort all values of this group and calculate the index at which the quantile value is located. The value at this index is then returned. If the calculated index is a decimal number (e.g., 1.5), it is rounded up. For example, suppose you want to calculate the 0.25 quantile for the following group: [ 1 , 3 , 3 , 4 , 4 , 5 ]. The calculated index in this case is 1.5, which means that the final quantile value cannot be clearly determined. We therefore round the calculated index up to 2 and return 3 as the final quantile value for this group.

Syntax

QUANTILE ( table.column, quantile )

NULL handling

NULL values are ignored, so they do not influence the result. If all the values of a group are NULL, the result for this group is also NULL. The quantile parameter is not allowed to be 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

[5]

Get the 0.5 quantile using a division as the quantile parameter.

Query

Column1

QUANTILE ( "Table"."Column" , 50 / 100 )

Input

Output

Table

Column : int

3

1

4

2

1

5

Result

Column1 : int

3

See also: