NTILE
Purpose
This function distributes the rows of an ordered partition into a specified number of buckets. For each row, this function returns the index of the bucket to which the row belongs.
Syntax
ntile::=
Usage Notes
- NTILE is only available as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
- The expr has to be a numeric value with scale 0. The expr has to be constant within a partition. This implies that expr may depend on the partitioning attribute. Thus expr may vary in different partitions but must remain constant for all rows within a partition. Exasol returns an error if this condition is not satisfied.
- NTILE requires an order_clause.
- NTILE does not allow the use of a window_frame_clause. It uses the entire partition as window for the computation of the result.
Example
Analytic Function
SELECT
id, department, hire_date,
NTILE(3) OVER (PARTITION BY department ORDER BY hire_date) NTILE
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | NTILE |
2005 | ACCOUNTS | 2013-01-01 | 1 |
2003 | ACCOUNTS | 2015-07-01 | 1 |
2002 | ACCOUNTS | 2017-01-01 | 2 |
2004 | ACCOUNTS | 2017-01-01 | 2 |
2001 | ACCOUNTS | 2018-07-01 | 3 |
1003 | HR | 2014-01-01 | 1 |
1002 | HR | 2016-01-01 | 1 |
1004 | HR | 2016-01-01 | 2 |
1001 | HR | 2018-07-01 | 3 |