Sql-server – How to pass column to function in sql

aggregatesql serversql-server-2012

I'm calculating median as:

DECLARE @TEMP TABLE
(
    ID INT
)

Select 
(
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) + 
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

Above query I want to use. But, in my case there are so many columns for those I want to calculate MEDIAN. But I think it would be bad to repeat above block of code for each column.
So, I'm trying to define separate function which would accept column values, process and return median.
Should I have to define table-value-funtion for that or there is another optimized way to do so??

This question is related to the following questions:

Best Answer

There are much more efficient ways to calculate a simple or grouped median than the one shown in your question:

What is the fastest way to calculate the median?
Best approaches for grouped median

The general winner for 2012 is a method by Peter Larsson. The pattern is:

Simple Median

SELECT
    Median = AVG(1.0 * SQ.YourColumn)
FROM 
(
    SELECT NumRows = COUNT_BIG(*) 
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
) AS C
CROSS APPLY 
(
    SELECT YT.ColumnName
    FROM dbo.YourTable AS YT
    WHERE YT.ColumnName IS NOT NULL
    ORDER BY YT.ColumnName ASC
    OFFSET (C.NumRows - 1) / 2 ROWS
    FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ;

Grouped Median

SELECT
    SQ2.GroupingColumn,
    SQ2.Median
FROM 
(
    SELECT
        GroupingColumn,
        NumRows = COUNT_BIG(*) 
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
    GROUP BY
        GroupingColumn
) AS C
CROSS APPLY 
(
    SELECT 
        Median = AVG(1.0 * SQ1.YourColumn)
    FROM
    (
        SELECT YT.ColumnName
        FROM dbo.YourTable AS YT
        WHERE 
            YT.GroupingColumn = C.GroupingColumn
            AND YT.ColumnName IS NOT NULL
        ORDER BY 
            YT.ColumnName ASC
            OFFSET (C.NumRows - 1) / 2 ROWS
            FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
    ) AS SQ1
) AS SQ2;

To maximize the performance of the OFFSET method above, you may need to add a locking hint (advanced topic). Suitable indexing will also be required, of course.

Code reuse

This is hard to achieve directly with a T-SQL function, since these do not allow the execution of dynamic SQL (assuming you were thinking of passing in the column name).

There are several ways around this, including using a function to generate the dynamic SQL text itself, which can then be executed by the caller. There aren't really enough details in the question to say which approach would be most suitable for you.

Related Question