Apply PERCENTILE_CONT with table of percentile values

Question

I would like to apply the PERCENTILE_CONT() function to compute many percentiles in SQL Server.

The solution I tried was to create a table of percentiles to calculate, cross-join with this table, and then apply the function. I am encountering an error

Input parameter of PERCENTILE_CONT function must be a constant.

What is the simplest way to calculate the results for many percentiles?

Sample data of normally distributed values:

-- Create table with normal distribution
IF OBJECT_ID('tempdb..#tblNormalDistribution') IS NOT NULL
    DROP TABLE #tblNormalDistribution
GO

CREATE TABLE #tblNormalDistribution (x FLOAT)
GO

DECLARE @pi FLOAT
    ,@2pi FLOAT
    ,@randNum1 FLOAT
    ,@randNum2 FLOAT
DECLARE @value1 FLOAT
    ,@value2 FLOAT
DECLARE @iteration INT
    ,@numberOfIterations INT
DECLARE @mean FLOAT
DECLARE @stdDev FLOAT --standard deviation
DECLARE @precision INT --number of places to the right of the decimal point

SELECT @iteration = 0

SELECT @pi = pi()

SELECT @2pi = 2.0 * @pi

SELECT @mean = 75.0

SELECT @stdDev = 5.0

SELECT @precision = 1

SELECT @numberOfIterations = 500 --Two values will be generated each iteration

WHILE (@iteration < @numberOfIterations)
BEGIN
    SELECT @randNum1 = rand()

    SELECT @randNum2 = rand()

    SELECT @value1 = round((sqrt(- 2.0 * log(@randNum1)) * cos(@2pi * @randNum2)) * @stdDev, @precision) + @mean

    SELECT @value2 = round((sqrt(- 2.0 * log(@randNum1)) * sin(@2pi * @randNum2)) * @stdDev, @precision) + @mean

    INSERT INTO #tblNormalDistribution (x)
    VALUES (@value1)

    INSERT INTO #tblNormalDistribution (x)
    VALUES (@value2)

    SELECT @iteration = @iteration + 1
END

SELECT TOP 1000 *
FROM #tblNormalDistribution

Table with percentiles to calculate:

-- Create table with percentiles to calculate
CREATE TABLE #Percentiles (P INT)

DECLARE @i INT

SET @i = 1

WHILE @i <= 100
BEGIN
    INSERT #Percentiles (P)
    VALUES (@i)

    SET @i = @i + 1
END

SELECT TOP 1000 *
FROM #Percentiles

Attempted solutions:

-- Calculate median
SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN
GROUP (
        ORDER BY x
        ) OVER (PARTITION BY 1) AS Median
FROM #tblNormalDistribution

-- Attempt 1: Calculate percentiles
-- Input parameter of PERCENTILE_CONT function must be a constant.
SELECT DISTINCT PERCENTILE_CONT(P * 0.1) WITHIN
GROUP (
        ORDER BY x
        ) OVER (PARTITION BY 1) AS Perc
FROM (
    SELECT x
        ,P
    FROM #tblNormalDistribution
    CROSS JOIN #Percentiles
    ) a

-- Attempt 2: Calculate percentiles
-- Column 'a.x' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT DISTINCT PERCENTILE_CONT(P * 0.1) WITHIN
GROUP (
        ORDER BY x
        ) OVER (PARTITION BY 1) AS Perc
FROM (
    SELECT x
        ,P
    FROM #tblNormalDistribution
    CROSS JOIN #Percentiles
    ) a
GROUP BY P

user_4755863 · Answer

Here's a dynamic SQL option that would work. Loops through your percentile values (could loop through a table of them instead of just iterating easily enough) and inserts each percentile into a table.

drop table #pct
create table #pct (p int, val decimal(10,5)) --or whatever precision you want

declare @i int = 1
declare @statement nvarchar(max) = 'insert into #pct values (' + cast(@i as varchar(3)) + ', (select distinct percentile_cont(' + cast(cast(@i/100.0 as decimal(3,2)) as varchar(5)) + ') within group (order by num) over (partition by 1) from #sampledata))'

while @i < 10 --for demonstration purposes, this does 1-10th percentiles.  Could easily adjust.
begin
    exec sp_executesql @statement
    set @i = @i + 1
    set @statement = 'insert into #pct values (' + cast(@i as varchar(3)) + ', (select distinct percentile_cont(' + cast(cast(@i/100.0 as decimal(3,2)) as varchar(5)) + ') within group (order by num) over (partition by 1) from #sampledata))'
end

select *
from #pct

user_1144035 · Answer

If you just want the 100 percentiles, you can use various approaches. One method that might be "good enough" uses ntile():

select tile, max(x)
from (select nd.*,
             ntile(100) over (order by x) as tile
      from tblNormalDistribution nd
     ) nd
group by tile;

If you need a more exact value, you can manually calculate it:

with nd as (
      select nd.*,
             percent_rank() over (order by x) as prank
      from tblNormalDistribution nd 
     )
select p.*, ndl.*, ndu.*,
       ndl.x + (ndu.x - ndl.x) * (p.p - ndl.prank) / nullif(ndu.prank - ndl.prank, 0)
from percentiles p outer apply
     (select top (1) nd.*
      from nd
      where nd.prank <= p.p
      order by nd.x desc
     ) ndl outer apply
     (select top (1) nd.*
      from nd
      where nd.prank >= p.p
      order by nd.x asc
     ) ndu;

Here is a db<>fiddle.

Apply PERCENTILE_CONT with table of percentile values

2 Answers