NTILE() OVER ...

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

Returns the bucket number (1 to n) for each row in a partition, where n is the number of buckets.

Syntax

NTILE(expr) [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

NTILE(expr) [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Divides partition rows into n buckets numbered 1 to n
• Requires ORDER BY to determine row ordering
• PARTITION BY creates independent bucket groups
• Distributes rows as evenly as possible across buckets
• If rows don't divide evenly, earlier buckets get one extra row
• Useful for percentile calculations and data sampling
• Example: NTILE(4) creates quartiles, NTILE(100) creates percentiles
• Always returns BIGINT type

See also

• OVER clause

Examples

SELECT
    -- NTILE bucket labels (1..n), not sort_key (100, 110, 120)
    f1 := ZIP(ARRAY(100, 110, 120) AS sort_key, ARRAY(10, 20, 30) AS v).TRANSFORM(
        SELECT NTILE(2) OVER (ORDER BY sort_key ASC)
    ).UNWRAP(),
    -- Buckets are assigned over matching rows only; rows outside WITHIN return NULL
    f2 := ZIP(
        ARRAY(1, 2, 3, 4) AS id,
        ARRAY(20, 10, 20, 40) AS s,
        ARRAY(TRUE, FALSE, TRUE, TRUE) AS keep
    ).TRANSFORM(
        SELECT NTILE(2) WITHIN (WHERE keep) OVER (ORDER BY s ASC, id ASC)
    ).UNWRAP()
;