Books Online对该函数的描述：

计算 SQL Server 2012 中整个行集内或行集的非重复分区内已排序值的特定百分位数。对于给定的百分位数的值 P，PERCENTILE_DISC 对 ORDER BY 子句中表达式的值进行排序，并返回具有最小 CUME_DIST 值且大于或等于 P 的值（遵照相同的排序规范）。例如，PERCENTILE_DISC (0.5) 将计算表达式的第 50 百分位数（也即中值）。PERCENTILE_DISC 基于列值的离散分布来计算百分位数；结果等于列中的一个特定值。

Example:

USE AdventureWorks2012
GO
SELECT SalesOrderID, OrderQty, ProductID,
CUME_DIST() OVER(PARTITION BY SalesOrderID
ORDER BY ProductID ) AS CDist,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ProductID)
OVER (PARTITION BY SalesOrderID) AS PercentileDisc
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
GO
以下为取0.5得到的结果

Result:

若取PERCENTTILE_DISC(0.75),则有以下结果：

USE AdventureWorks2012
GO
SELECT SalesOrderID, OrderQty, ProductID,
CUME_DIST() OVER(PARTITION BY SalesOrderID
ORDER BY ProductID ) AS CDist,
PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY ProductID)
OVER (PARTITION BY SalesOrderID) AS PercentileDisc
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
GO

Capture.JPG

ITPub博客

介绍SQl server 2012的新分析函数--PERCENTILE_DISC()

最新文章