MEDIAN_q
Updated: 16 February 2017
Use the scalar function MEDIAN_q to return the median of the given numbers. The median is the number in the middle of an ordered set of numbers. For large groups of data MEDIAN_q may provide better performance than the aggregate function MEDIAN, provided that the query string includes an ORDER BY. For smaller groups, the ORDER BY adds overhead which will cause comparatively slower performance.
Syntax
SELECT [wct].[MEDIAN_q](
<@Values_RangeQuery, nvarchar(max),>)
<@Behavior, float,>)
Arguments
@Values_RangeQuery
the SELECT statement, as text, used to determine the values to be used in the MEDIAN_q calculation.
Return Type
float
Remarks
· MEDIAN_q only includes values of type float or of a type that can be implicitly converted to float.
· If the resultant table returned by @Values_RangeQuery has an even number of rows then the function calculates the average of the 2 number in the middle.
Examples
Example #1
CREATE TABLE #m1(
[num] [float] NOT NULL
)
INSERT INTO #m1 VALUES (1)
INSERT INTO #m1 VALUES (2)
INSERT INTO #m1 VALUES (4)
INSERT INTO #m1 VALUES (7)
INSERT INTO #m1 VALUES (8)
INSERT INTO #m1 VALUES (9)
INSERT INTO #m1 VALUES (10)
INSERT INTO #m1 VALUES (12)
SELECT wct.MEDIAN_q('SELECT num FROM #m1') as [Median]
This produces the following result
Example #2
In this example we use the XLeratorDB table-valued function SeriesInt to create 2,389,000 rows of random integers with values between 1 and 2000. The data are grouped in 5 categories and we calculate the median for each category. Note the use of the ORDER BY in @Values_RangeQuery.
SELECT
n.m,
k.SeriesValue as x
INTO #t
FROM (VALUES (0,500000),(1,1100000),(2,650000),(3,130000),(4,9000))n(m,p)
CROSS APPLY wct.SeriesInt(1,2000,NULL,n.p,'R')k
SELECT
m,
wct.MEDIAN_q('SELECT x FROM #t WHERE m = '+CONVERT(varchar(max), n.m)+' ORDER BY x') as [MEDIAN]
FROM (SELECT DISTINCT m FROM #t)n
ORDER BY m
This produces the following result. Your results will be different.