CODEWITHSUNDEEP
sql-servert-sqlstatisticsanalyticspercentile
user1956801
user1956801

Reputation: 135

T-SQL: aggregate function for calculating Nth percentile

I am trying to calculate the Nth percentile of all of the values in a single column in a table. All I want is a scalar, aggregate value for which N percent of the values are below. For instance, If the table has 100 rows where the value is the same as the row index plus one (1 to 100 consecutively), then I'd want this value to tell me that 95% of the values are below 95.

The PERCENTILE_CONT analytic function looks closest to what I want. But if I try to use it like this:

SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ValueColumn) OVER () AS P95

I get one row per row in the table, all with the same value. I could use TOP 1 to just give me one of those rows, but now I've done an additional table scan.

I am not trying to create a wizbang table of results partitioned by some other column in the original table. I just want an aggregate, scalar value.

Edit: I have been able to use PERCENTILE_CONT in a query with a WHERE clause. For example:

DECLARE @P95 INT

SELECT TOP 1 @P95 = (PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ValueColumn) OVER ())
FROM ExampleTable
WHERE LOWER(Color) = 'blue'

SELECT @P95

Including the WHERE clause gives a different result than I got without it.

Upvotes: 2

Views: 2383

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

From what I can tell, you will need to do a subquery here. For example, to find the number of records strictly below the 95 percentile we can try:

WITH cte AS (
    SELECT ValueColumn,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ValueColumn) OVER () AS P95
    FROM yourTable
)

SELECT COUNT(*)
FROM cte
WHERE ValueColumn < P95;

Upvotes: 1

Related Questions