SQL PERCENTILE_CONT

SQL Server 的 PERCENTILE_CONT 是分析函数之一，它将根据表中列值的连续分布来计算百分位数。PERCENTILE_CONT 的基本语法是：

SELECT PERCENTILE_CONT(Numerical_Literal)
       WITHIN GROUP ( ORDER BY_Clause)
        OVER ( PARTITION_BY_Clause )
FROM [Source]
  • Numerical_Literal：要计算的百分位数。此值应介于 0.0 和 1.0 之间。
  • WITHIN Group (Order By_Clause)：此子句将在组内以指定的顺序对数据进行排序。请参阅 SQL Order By Clause 以获得更好的理解。
  • Over (Partition_By_Clause)：它将 SELECT 语句选择的 SQL Server 记录划分为分区。

我们将使用下面显示的数据来进行此 PERCENTILE_CONT 演示。

Employee Table

SQL PERCENTILE_CONT 示例

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

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY [Sales] ASC)
			   OVER (PARTITION BY [Occupation]) AS Percentile 
  FROM [Employee]
PERCENTILE_CONT 2

PERCENTILE_CONT 示例 2

在这里，我们在 PERCENTILE_CONT 函数中使用不同的数值。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [Sales] ASC)
			     OVER (PARTITION BY [Occupation]) AS Percentile1  
      ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [Sales] ASC)
			     OVER (PARTITION BY [Occupation]) AS Percentile2 
      ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [Sales] ASC)
			     OVER (PARTITION BY [Occupation]) AS Percentile3 
      ,PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY [Sales] ASC)
			      OVER (PARTITION BY [Occupation]) AS Percentile4 
  FROM [Employee]
SQL PERCENTILE_CONT 3