PERCENTILE_DISC - Exclude Nulls-CodePudding

I'm having trouble with PERCENTILE_DISC where the below query is returning the "Median" as 310, but the actual Median is 365. It's returning 310 as it's including the NULL value.

Is there a way to have PERCENTILE_DISC exclude NULLS? Gordon Lindoff mentioned in this post that NULLs are excluded from PERCENTILE_DISC but this doesn't seem to be the case.

Here's the simple example showing the problem:

SELECT
    DISTINCT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Numbers) OVER (PARTITION BY Category)
from 
    (
        select 1 as Category,420 as Numbers
        union all
        select 1,425
        union all
        select 1,NULL
        union all 
        select 1,310
        union all
        select 1,300
    ) t1

CodePudding user response：

According to the documentation for PERCENTILE_DISC, the result is always equal to a specific column value.

PERCENTILE_DISC does ignore the NULL-s: if you ask for PERCENTILE_DISC(0) you would get 300, not NULL.

To get the value you want (365, which is the average between 310 and 420), you need to use PERCENTILE_CONT. See the first example in the documentation mentioned above, where it highlights the difference between PERCENTILE_CONT and PERCENTILE_DISC.