A small classic car retailer called Prestige Cars Ltd, company database. The sales manager. wants to group sales into deciles— that is “buckets” of equal size where each group of data contains one tenth of the records in the total dataset. She needs this to look at segments of the customer base.
. Here is one possible solution:
SELECT ST.Color, MakeName + ', ' + ModelName AS MakeAndModel
,SD.SalePrice
,NTILE(10) OVER (ORDER BY SD.SalePrice DESC) AS SalesDecile
FROM Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
ORDER BY SalesDecile, Color, MakeAndModel. Scroll down the result set to see how the SalesDecile field changes every 35 records or so.
NTILE() function to group data into segments
Of course, you need to understand the PrestigeCars database to comprehend this code – especially the FROM/JOIN clauses.Hhere is the ERD (Entity Relationship Diagram)
for this database:
The Prestige Cars database Entity Relationship Diagram
You can see from the output that this query added a field that indicates the decile (or segment, or data bucket,) to which each record belongs. This query is, a simple SELECT query to join the necessary tables and to select the required fields. It then adds a final field using the NTILE() function.
This query delivers an easy solution to a potentially complex question thanks to the NTILE() function. This windowing function segments the dataset into as many separate subgroups. Whatever the number of records in the dataset, the NTILE() function subdivides those into the number of groups that you specify as a parameter of the function.
Just like the RANK() and DENSE_RANK() functions, the NTILE() function requires you to add both the OVER keyword and an ORDER BY clause inside parentheses—in addition to a value that is used for classifying the data.
The NTILE() function requires one further element—the number of “buckets” to appear in the output - defined by adding a number inside the parentheses just after the NTILE() function to specify the number of groups into which the dataset will be divided.
e have only one comment to make here. NTILE() can also contain a PARTITION BY clause to add a further level of segmentation should you need it.
