PERCENT_RANK() OVER ...

All functions > WINDOW FUNCTION > PERCENT_RANK() OVER ...

Returns the relative rank of the current row: (rank - 1) / (total rows - 1).

Syntax

PERCENT_RANK() OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

PERCENT_RANK() OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Returns a value between 0 and 1 inclusive
• First row in each partition always has PERCENT_RANK of 0
• Requires ORDER BY clause to determine ranking order
• PARTITION BY creates independent ranking groups

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1,2,3] AS id, ARRAY[10,20,30] AS v).TRANSFORM(SELECT PERCENT_RANK() OVER (ORDER BY v ASC)).UNWRAP() -- Relative rank in [0, 1]
;