SQL Server NTILE() is a window function that distributes rows of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The use of the function is described with a real time problem scenario.
Problem Scenario: An agency is doing unique sort of promotional events on 1st of each month. The administration wanted to peer, which promotional event has benefited the organization in each region.
USE dbTest
GO
CREATE TABLE tbSalesSummary(
Id INT IDENTITY(1,1),
YearMonth INT,
SummaryMonth VARCHAR(20),
TotalSales DECIMAL(20,2));
INSERT INTO tbSalesSummary(YearMonth,SummaryMonth,TotalSales) VALUES
(201901, ‘January’, 339000),
(201902,’February’,449000),
(201903,’March’,393000),
(201904,’April’,320000),
(201905,’May’,343430),
(201906,’June’,444000),
(201907,’July’,303000),
(201908,’August’,200990),
(201909,’September’,299900),
(201910,’October’,393000),
(201911,’November’,420000),
(201912,’December’,415000)
GO
The above code helps to create the table and insert the data to examine the behavior of NTILE function.
–To retrieve quarterly sales info
WITH CTE(Qtr, TotalSales) AS(
SELECT Qtr, MAX(TotalSales) FROM(
SELECT NTILE(4) OVER(ORDER BY YearMonth) Qtr, * FROM tbSalesSummary)
Results GROUP BY Qtr)
SELECT S.* FROM CTE
JOIN (
SELECT NTILE(4) OVER(ORDER BY YearMonth) Qtr, * FROM tbSalesSummary) S
ON CTE.Qtr = S.Qtr AND CTE.TotalSales = S.TotalSales
If you look at the results, the maximum sales from every quarter has been retrieved. This function is available in most of the RDBMS applications along with Big Data technologies like Hive & Impala. The functionality and behavior is same.
Hope you like this article.
Big Data and SQL 
3 comments