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

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博客 ” ，链接：http://blog.itpub.net/101629/viewspace-748564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101629/viewspace-748564/