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
Diagram(
Sequence(
Terminal("NTILE"),
Terminal("("),NonTerminal('expr'),Terminal(")"),
Terminal("OVER"),
Terminal("("),
NonTerminal("over_clause"),
Terminal(")"),
)
)| Parameter | Type | Required | Description |
|---|---|---|---|
expr | EXPRESSION | Yes | Number of buckets (must be positive integer) |
over_clause | OVER CLAUSE | Yes | OVER (PARTITION BY ... ORDER BY ... [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