Calculate Exact Values Of Lower and Upper Quartiles

Question

I have been looking all around internet and it seems like there is no answer that match my case.

I am struggling with calculating the exact Lower and Upper Quartile in SQL Server. I am aware that SQL Server has a built in function that facilitates calculation of quartiles, the NTILE function. But that is not enough for my case.

Given the following table with values (note that the table containes more products and prices than in the table below):

AveragePrice	ProductNumber	Year
45.7820	2	2015
46.0142	2	2016
59.0133	2	2017
60.1707	2	2018
62.6600	2	2019

I am running the following query:

SELECT 
    AveragePrice
    ,NTILE(4) OVER (
        PARTITION BY ProductNumber ORDER BY AveragePrice
        ) AS Quartile
FROM products

Which gives the following result:

AveragePrice	Quartile
45.7820	1
46.0142	1
59.0133	2
60.1707	3
62.6600	4

For full context the query in it's whole looks like this:

    SELECT ProductNumber
    ,MIN(AveragePrice) Minimum
    ,MAX(CASE 
            WHEN Quartile = 1
                THEN AveragePrice
            END) AS Quartile_1
    ,
    MAX(CASE 
            WHEN Quartile = 3
                THEN AveragePrice
            END) AS Quartile_3
    ,MAX(AveragePrice) Maximum
    ,COUNT(Quartile) AS 'Number of items'
FROM (
    SELECT ProductNumber
        ,AveragePrice   
        ,NTILE(4) OVER (
            PARTITION BY ProductNumber ORDER BY ProductNumber
            ) AS Quartile
    FROM #temp_products
    
    ) Vals
GROUP BY ProductNumber
ORDER BY ProductNumber

But when I manually calculate the quartile the first quartile should be: 45.8981 (average of the first and second row in this particular case) not 46.0142.

The third quartile should be 61.41535 (average of the third and second quartile in this particular case) not 60.1707 .

So to make it clear. This is a part of a stored procedure where multiple pricegroups is calculated and aggregated into groups containing average prices. I need to calculate the upper and lower quartiles from these average prices grouped by product number. The result set should contain the productnumber, lower quartile and upper quartile. Can someone help me or guide me in the correct direction?

Answer 1

Okay, inspired by this post I managed to build a query which actually calculates the exact quartiles:

  -- ; since it is being used in a sp
    ;WITH quartile_data AS (
    
        SELECT Price,
             ProductNumber
        FROM (values(29.4785,2,2015),(30.0000,2,2016),(33.4762,2,2017),(35.2917,2,2018),(35.8731,2,2018),(36.2475,2,2018),(37.9790,2,2018),(39.5846,2,2018),(67.4443,2,2018))sales(Price,ProductNumber)
     
    )
   --Aggregate into a single record for each group, using MAX to select the non-null 
   --detail value for each column
   -- ISNULL check to include groups with three values as well
    SELECT ProductNumber, 
        (Max(Q1NextVal) - MAX(Q1Val)) * Max(Q1Frac) + Max(Q1Val) as [Q1],
        (Max(MidVal1) + Max(MidVal2)) / 2 [Median],
        (ISNULL(Max(Q3NextVal),0) - MAX(Q3Val)) * Max(Q3Frac) + Max(Q3Val) as [Q3]
    
    -- save the result into a temp table
    INTO #my_temp_table
    FROM (
--Expose the detail values for only the records at the index values 
--generated by the summary subquery. All other values are left as NULL. som NULL.
        SELECT detail.ProductNumber, 
            CASE WHEN RowNum = Q1Idx THEN Price ELSE NULL END Q1Val,
            CASE WHEN RowNum = Q1Idx + 1 THEN Price ELSE NULL END Q1NextVal,
            CASE WHEN RowNum = Q3Idx THEN Price ELSE NULL END Q3Val,
            CASE WHEN RowNum = Q3Idx + 1 THEN Price ELSE NULL END Q3NextVal,
            Q1Frac,
            Q3Frac,
            CASE WHEN RowNum = MidPt1 THEN Price ELSE NULL END MidVal1,
            CASE WHEN RowNum = MidPt2 THEN Price ELSE NULL END MidVal2
        FROM
               --Calculate a row number sorted by measure for each group.
            (SELECT *,  ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY Price) RowNum
            FROM  quartile_data) AS detail
    
        INNER JOIN (
    --Summarize to find index numbers and fractions we need to use to locate 
    --the values at the quartile points.
    -- The modulus operator is used to sum the correct number if the number of rows in the group is even or uneven
            SELECT ProductNumber, 
                FLOOR((COUNT(*) + IIF((COUNT(*) % 2 = 0), 2,1)) / 4.0) Q1Idx,
                ((COUNT(*) + IIF((COUNT(*) % 2 = 0), 2,1)) / 4.0) - FLOOR((COUNT(*) + IIF((COUNT(*) % 2 = 0), 2,1)) / 4.0) Q1Frac,
                (COUNT(*) + 1) / 2 AS MidPt1,
                (COUNT(*) + 2) / 2 AS Midpt2,
                  FLOOR((COUNT(*) * 3 + IIF((COUNT(*) % 2 = 0), 2,3)) / 4.0) Q3Idx,
                ((COUNT(*) * 3 + IIF((COUNT(*) % 2 = 0), 2,3)) / 4.0) - FLOOR((COUNT(*) * 3 + IIF((COUNT(*) % 2 = 0), 2,3)) / 4.0) Q3Frac
            FROM  quartile_data
            GROUP BY ProductNumber
            HAVING COUNT(*) > 1
        
        ) AS summary ON detail.ProductNumber  = summary.ProductNumber
    
    ) AS step_two
    GROUP BY ProductNumber
   -- Include only groups with more than 2 rows
    HAVING count(*) > 2

The following prices: 29.4785 30.0000 33.4762 35.2917 35.8731 36.2475 37.9790 39.5846 67.4443

Gives the correct value: Q1 = 31.7381000000 and Q3 = 38.7818000000

Verified using this online tool

Answer 2

NTILE() is rounding up in some strange ways, in some cases. I'd rather use Integer division with ranks for grouping. This solution works with any number of values, with pondered average when needed.

LEAD is the the magic windowed function to catch next row's value

select *
    ,[Q] = case when [rank] in ((N+3)/4 ,(N+1)/2, (3*N+1)/4) then
                case [decimal] 
                when 0    then AveragePrice
                when 0.25 then /*pondered avg*/(3*AveragePrice +  LEAD(AveragePrice,1,null)over(PARTITION BY ProductNumber ORDER BY AveragePrice)) / 4
                when 0.5  then /*simple avg*/(    AveragePrice +  LEAD(AveragePrice,1,null)over(PARTITION BY ProductNumber ORDER BY AveragePrice)) / 2
                when 0.75 then /*pondered avg*/(  AveragePrice +3*LEAD(AveragePrice,1,null)over(PARTITION BY ProductNumber ORDER BY AveragePrice)) / 4 
                end
           end
from(
    select  *
        ,[rank]     = ROW_NUMBER()over(PARTITION BY ProductNumber ORDER BY AveragePrice)
        ,[N]        = SUM(1)over()
        ,[group4]   = ((ROW_NUMBER()over(PARTITION BY ProductNumber ORDER BY AveragePrice)-1 )*4 / SUM(1)over())
        ,[decimal]  = case /*rank*/ROW_NUMBER()over(PARTITION BY ProductNumber ORDER BY AveragePrice) 
                        when /*Q1*/  (SUM(1)over()+3)/4   then   (SUM(1)over()+3)/4.0 - FLOOR((SUM(1)over()+3)/4.0) 
                        when /*Q2*/  (SUM(1)over()+1)/2   then   (SUM(1)over()+1)/2.0 - FLOOR((SUM(1)over()+1)/2.0)
                        when /*Q3*/(3*SUM(1)over()+1)/4   then (3*SUM(1)over()+1)/4.0 - FLOOR((3*SUM(1)over()+1)/4.0)
                      end
    from
    (values(45.7820,2,2015),(46.0142,2,2016),(59.0133,2,2017),(60.1707,2,2018),(62.6600,2,2019))a(AveragePrice,ProductNumber,Year)
  )a

AveragePrice	ProductNumber	Year	rank	N	group4	decimal	Q
45.7820	2	2015	1	5	0	NULL	NULL
46.0142	2	2016	2	5	0	0.000000	46.014200
59.0133	2	2017	3	5	1	0.000000	59.013300
60.1707	2	2018	4	5	2	0.000000	60.170700
62.6600	2	2019	5	5	3	NULL	NULL