2.5 Ntile
- If the number of rows in a partition is not divisible by integer expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
QUERY: - NTILE (integer_expression ) OVER (order_by_clause ) from tbName;
EXAMPLE: - SELECT * , ntile (3) OVER (order by grade desc) as ntil
FROM customer;
WITH PARTITION BY CLAUSE:-
QUERY: - NTILE (integer_expression) OVER ([<partition_by_clause> ]
< order_by_clause >)
EXAMPLE: - SELECT *, ntile (3) OVER (Partition by city order by grade desc)
AS [ntile] FROM customer;
Last modified: Tuesday, 17 December 2019, 12:35 PM