ntile ranking window function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Divides the rows for each window partition into n buckets ranging from 1 to at most n.

Syntax 

ntile([n])

Arguments

  • n: An optional INTEGER literal greater than 0.

Returns

An INTEGER.

The default for n is 1. If n is greater than the actual number or rows in the window You must use the ORDER BY clause with this function.

If the order is non-unique, the result is non-deterministic.

Examples 

> SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   1
 A1 2   2
 A2 3   1