Using Percentile_Disc to compute Median/Interquartiles

Question

SQL Server Developer Edition 2014

I have a table that looks like:

+ ID + CC +  Xa  +
| 1  | 0  | 53.1 |
| 1  | 1  | 46.1 |
| 1  | 2  | 107.5|
| 2  | 0  | 36.5 |
| 2  | 1  | 41.5 |
| 2  | 2  | 61.8 |

Xa are floats in the table, but I rounded to make it less messy. I had heard of PERCENTILE_DISC from MSDN, and made a query that I thought would give me the median of Xa for each ID:

SELECT DISTINCT s.ExperimentID, 
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY s.ExperimentID) 
FROM Simulations s

I threw in the DISTINCT ExperimentID because when I ran the query without it, it was looking to return the median of all ~150M rows for each individual row, which doesn't really make any sense. I did get results, and I think they're right, but I'm not certain. I compared the results of some individual IDs vs what I got from MEDIAN() in LibreOffice, and the results are slightly different. Any idea why?

Answer 1

Median functions vary in a few ways, particularly when there's an even number of items.

I've blogged about medians, Aaron Bertrand has, Paul White has... I'm in good company. :) I'd recommend you find Aaron's material, which shows better ways to do median than PERCENTILE_CONT.

PERCENTILE_DISC will only choose a value from the list. This is fine if you have an odd number of values, but what if your list is (1, 2, 3, 4)? And then, do you choose 2 or 3? It's valid, but you should define what you need.

If you go with PERCENTILE_CONT, the answer is 2.5.

But if your set is (1, 2, 3, 3) then some would argue that your answer should be weighted by the two threes.

So you should make sure that you've defined what you mean by median. :)

Answer 2

It's always a good idea to check your results using a different technique, eg Excel, or if you have access to SQL Server 2016 (CTP 3 at this point) with Advanced Analytics enabled, then you could use R, eg:

-- Use Advanced Analytics Extensions to validate your median calc
EXEC dbo.sp_execute_external_script
    @language = N'R', 
    @script = N'OutputDataSet <- aggregate(InputDataSet$Xa, by=list(InputDataSet$ExperimentID), FUN=median)',
    @input_data_1 = N'SELECT ExperimentID, Xa FROM dbo.Simulations'
WITH RESULT SETS ( ( ExperimentID INT NOT NULL, median FLOAT NOT NULL ) );
GO

Mainly just for fun, and some exposure to the technique : )