Numbering data into specific number of groups using NTILE function...

Sometimes we may need to cluster data into particular group according to the particular value.
As an example as per the SQL Rank... secenario, we may need to distribute rows to 10 groups depending on the AvgRate value. 
For that we can use NTILE function as we used the Rank function. The difference in the NTILE function is that we need to provide the number of groups we want.

Sample query is as below

   1: SELECT YearKey, SupplierKey, ItemKey, NTILE(5) OVER (PARTITION BY ItemKey, YearKey ORDER BY AvgRate ASC) AS GroupByAverageRate FROM FactPurchases GROUP BY YearKey, SupplierKey, ItemKey

No comments:

Post a Comment

How the transformed data is written to an output file with column headers in U-SQL...

While working with U-SQL language, I noticed that there are few ways of writing data to an output file. Let's assume the SalesDetails....