NTILE

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns an integer ranging from 1 to <num_buckets> and divides the partition as equally as possible.

1ntile(<num_buckets>) OVER ( [ PARTITION BY <expression> [, ...] ] ORDER BY <expression> [, ...] )

For the full OVER clause syntax, including frame clauses and the WINDOW clause, see Window Function Call Syntax and WINDOW Clause.

• <num_buckets>: An integer specifying how many buckets to divide the partition’s rows into.

Returns an integer.

• ntile depends on the ORDER BY clause of the window definition to determine the order of rows before dividing them into buckets.
• Rows are divided into <num_buckets> buckets, numbered 1 through <num_buckets>, as equally as possible. If the number of rows in the partition doesn’t divide evenly by <num_buckets>, some buckets contain one more row than others.

Divide employees into four buckets by salary, from highest to lowest.

1SELECT depname, empno, salary, ntile(4) OVER (ORDER BY salary DESC)
2FROM empsalary;

Divide each department’s employees into two buckets by salary.

1SELECT depname, empno, salary, ntile(2) OVER (PARTITION BY depname ORDER BY salary DESC)
2FROM empsalary;

• ROW_NUMBER
• PERCENT_RANK
• CUME_DIST
• Aggregate Functions
• Window Functions