CUME_DIST OVER - Firebolt Documentation

Returns the cumulative distribution of the current row within its partition. The result is the number of rows that precede or are peers with the current row, divided by the total number of rows in the partition. Values are always in the range (0, 1]. For more information on usage, please refer to Window Functions.

Syntax

CUME_DIST() OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ASC|DESC] )

Parameters

Parameter	Description	Supported input types
`<partition_by>`	An expression used for the `PARTITION BY` clause.	Any
`<order_by>`	An expression used for the `ORDER BY` clause.	Any

Return Type

DOUBLE PRECISION

Remarks

CUME_DIST takes no arguments.
When no ORDER BY clause is specified, all rows within the partition are considered peers and CUME_DIST returns 1 for every row.
Unlike PERCENT_RANK, the minimum value of CUME_DIST is 1/N (not 0), where N is the partition size.
This function respects NULL values, and results will be ordered with default null ordering NULLS LAST unless otherwise specified in the ORDER BY clause.

Example

The following example ranks each level by maxpoints within its leveltype, showing what fraction of levels in the same type have a maxpoints value less than or equal to the current row:

SELECT
    level,
    leveltype,
    maxpoints,
    CUME_DIST() OVER (PARTITION BY leveltype ORDER BY maxpoints) AS cd
FROM levels
ORDER BY leveltype, maxpoints;

level int null	leveltype text null	maxpoints int null	cd double
6	Drift	80	0.5
9	Drift	250	1
1	FastestLap	20	0.25
3	FastestLap	40	0.5
7	FastestLap	70	0.75
8	FastestLap	100	1
2	FirstToComplete	30	0.25
4	FirstToComplete	100	0.5
5	FirstToComplete	150	0.75
10	FirstToComplete	500	1

Rows: 10Execution time: 8.19ms

COUNT OVER

DENSE_RANK OVER

⌘I

​Syntax

​Parameters

​Return Type

​Remarks

​Example

Syntax

Parameters

Return Type

Remarks

Example