MEDIAN_q

Updated 2024-02-13 19:33:47.367000

Syntax

SELECT [westclintech].[wct].[MEDIAN_q](
  <@Values_RangeQuery, nvarchar(max),>)

Description

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.

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

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

{"columns":[{"field":"Median","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Median":"7.5"}]}

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.

{"columns":[{"field":"m","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"MEDIAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"m":"0","MEDIAN":"1001"},{"m":"1","MEDIAN":"1000"},{"m":"2","MEDIAN":"1000"},{"m":"3","MEDIAN":"997"},{"m":"4","MEDIAN":"996"}]}