The SQL PERCENTILE_DISC will calculate a percentile of the sorted values within an entire row set, or within the partitions in a table. This article will show you, How to use this analytic function called PERCENTILE_DISC in SQL Server with example. Before we get into the example, let us see the syntax behind this:
SQL PERCENTILE_DISC Syntax
The basic syntax of the PERCENTILE_DISC in SQL Server is as shown below:
|
1 2 3 4 |
SELECT PERCENTILE_DISC(Numerical_Literal) WITHIN GROUP ( ORDER BY_Clause) OVER ( PARTITION_BY_Clause ) FROM [Source] |
- Numerical_Literal: Specify the Percentile to compute. This value should be between 0.0 and 1.0
- WITHIN Group (Order By_Clause): This will sort the column data in a specified order within the group. Please refer Order By Clause for better understanding.
- Over (Partition_By_Clause): It divide the records selected by the SELECT Statement into partitions.
We are going to use the below shown data for this demonstration
SQL PERCENTILE_DISC Example
This example show you, How to calculate the percentile of the partitioned records present in a table. The following Query will Order the Data by Sales in Ascending Order, partition the data by Occupation. And then calculate the percentile for each partition independently.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENTILE_DISC(0.0) WITHIN GROUP (ORDER BY [Sales] ASC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc] FROM [Employee] |
OUTPUT
Let me change the Order by clause from Ascending order to Descending.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENTILE_DISC(0.0) WITHIN GROUP (ORDER BY [Sales] DESC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc] FROM [Employee] |
OUTPUT
SQL PERCENTILE_DISC Example 2
In this example we will use different numerical values inside the PERCENTILE_DISC function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY [Sales] ASC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc 1] ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY [Sales] ASC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc 2] ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [Sales] ASC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc 3] ,PERCENTILE_DISC(1.0) WITHIN GROUP (ORDER BY [Sales] ASC) OVER (PARTITION BY [Occupation]) AS [Percentile Disc 4] FROM [Employee] |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply