SQL PERCENTILE_DISC

SQL Server 的 PERCENTILE_DISC 函数将计算一行集或表内分区中排序值的百分位数。PERCENTILE_DISC 分析函数的语法基本如下所示

SELECT PERCENTILE_DISC(Numerical_Literal)
       WITHIN GROUP ( ORDER BY_Clause)
        OVER ( PARTITION_BY_Clause )
FROM [Source]

• Numerical_Literal: 指定要计算的百分位数。此值应介于 0.0 和 1.0 之间
• WITHIN Group (Order By_Clause): 这将在组内按指定顺序对列数据进行排序。请访问 Order By Clause 以更好地了解。
• Over (Partition_By_Clause): 它将 SELECT 语句选定的记录分隔到 SQL Server 分区中。

我们将使用下面显示的数据进行此百分位数演示

SQL PERCENTILE_DISC 示例

此示例显示了如何计算表中分区记录的百分位数。以下查询将按销售额升序对数据进行排序，按职业对数据进行分区。然后，独立计算每个分区的百分位数。

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]

让我将 Order by 从升序更改为降序。

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]

PERCENTILE_DISC 示例 2

在这种情况下，我们将在 SQL PERCENTILE_DISC 函数中使用不同的数字值。

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]