Calculates a percentile based on a continuous distribution of the column value in the SQL Server Database Engine. The result is interpolated, and might not equal any of the specific values in the column. This function requires both the WITHIN GROUP and the OVER clause, and the result set is closer to the typical of a Window Function, rather than the one we would expect from an Aggregate Function.
Syntax
The syntax of this
functions is also a different compared to the other window functions. We are
using OVER clause, but inside the OVER clause we only specify the PARTITION BY
expression if one is needed. There is a new clause called WITHIN GROUP, where
the ORDER BY will go, and the ORDER BY expression must evaluate to a numeric
type like INT or DECIMAL.
Example
SELECT COUNT(*) NumberOfOrders, Month(OrderDate) AS orderMonth, PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY COUNT(*)) OVER() AS PercentileCont FROM Sales.SalesOrderHeader WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01' GROUP BY Month(OrderDate); |
No comments:
Post a Comment
If you have any doubt, please let me know.