Saturday 20 July 2024

PERCENTILE_CONT in SQL server

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.

Popular Posts