Best Usage of NTILE function in SQL Server and other RDBMSs

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

NTILE Example

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.

 

3 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s