Using a custom aggregate function in a GROUP BY?

Question

I have a simple MEDIAN calculation function:

IF OBJECT_ID(N'COMPUTEMEDIAN', N'FN') IS NOT NULL
    DROP FUNCTION dbo.COMPUTEMEDIAN;
GO
CREATE FUNCTION dbo.COMPUTEMEDIAN(@VALUES NVARCHAR(MAX))
RETURNS DECIMAL
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @MEDIAN DECIMAL
    SET @MEDIAN = 0.0;

    DECLARE @MEDIAN_TEMP TABLE (RawValue DECIMAL);

    -- This is the Killer!
    INSERT INTO @MEDIAN_TEMP
    SELECT s FROM master.dbo.Split(',', @VALUES) OPTION(MAXRECURSION 0)  

    SELECT @MEDIAN =
    (
     (SELECT MAX(RawValue) FROM
       (SELECT TOP 50 PERCENT RawValue FROM @MEDIAN_TEMP ORDER BY RawValue) AS BottomHalf)
     +
     (SELECT MIN(RawValue) FROM
       (SELECT TOP 50 PERCENT RawValue FROM @MEDIAN_TEMP ORDER BY RawValue DESC) AS TopHalf)
    ) / 2

    --PRINT @SQL
    RETURN @MEDIAN;
END;
GO

However, my table is of the following form:

CREATE TABLE #TEMP (GroupName VARCHAR(MAX), Value DECIMAL)
INSERT INTO #TEMP VALUES ('A', 1.0)
INSERT INTO #TEMP VALUES ('A', 2.0)
INSERT INTO #TEMP VALUES ('A', 3.0)
INSERT INTO #TEMP VALUES ('A', 4.0)
INSERT INTO #TEMP VALUES ('B', 10.0)
INSERT INTO #TEMP VALUES ('B', 11.0)
INSERT INTO #TEMP VALUES ('B', 12.0)

SELECT * FROM #TEMP

DROP TABLE #TEMP

What is the best way to invoke the MEDIAN function on this table using a GROUP BY on the id column? So, I am looking for something like this:

SELECT id, COMPUTEMEDIAN(Values)
FROM #TEMP
GROUP BY id

My current approach involves using XMLPATH to combine all values resulting from a GROUP BY operation into a large string and then passing it to the function but this involves the String splitting operation and for large strings this just slows down everything. Any suggestions?

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

Since you're using SQL Server 2008, I would suggest writing the aggregate function as a CLR function.

http://msdn.microsoft.com/en-us/library/91e6taax(v=vs.80).aspx

Also, people have asked this question before. Perhaps their answers would be helpful

Function to Calculate Median in Sql Server

edited May 23 '17 at 12:12

Community

1
1

answered Oct 07 '11 at 20:52

Tom Halladay

5,651
6
46
65

Yes. I saw that but doesn't that require special privileges? I will executing this script in a highly restricted environment so am not sure CLRs are an option for me. – Legend Oct 07 '11 at 20:52
Correct, there are special permissions required to be able to load CLR functions: "Requires CREATE AGGREGATE permission and also REFERENCES permission on the assembly that is specified in the EXTERNAL NAME clause." http://msdn.microsoft.com/en-us/library/ms182741.aspx – Tom Halladay Oct 07 '11 at 20:54
Yes. That was my concern. But +1 for your time. Thank you. – Legend Oct 07 '11 at 21:02

score 1 · Accepted Answer · edited May 23 '17 at 11:47

EDIT: I can confirm this works very very well on a large database (30,000 values)

Hmm... Just came across this so the following works perfectly fine but am not sure how expensive it can turn out to be:

SELECT
   GroupName,
   AVG(Value)
FROM
(
   SELECT
      GroupName,
      cast(Value as decimal(5,2)) Value,
      ROW_NUMBER() OVER (
         PARTITION BY GroupName
         ORDER BY Value ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY GroupName 
         ORDER BY Value DESC) AS RowDesc
   FROM #TEMP SOH
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY GroupName
ORDER BY GroupName;

Slider345 · Answer 3 · 2011-10-07T21:38:35.227

No need to use a user defined function! Here's how I would do it:

CREATE TABLE #TEMP (id VARCHAR(MAX), Value DECIMAL)

INSERT INTO #TEMP VALUES('A', 1.0)

INSERT INTO #TEMP VALUES('A', 2.0)
INSERT INTO #TEMP VALUES('A', 3.0)
INSERT INTO #TEMP VALUES('A', 4.0)
INSERT INTO #TEMP VALUES('B', 10.0)
INSERT INTO #TEMP VALUES('B', 11.0)
INSERT INTO #TEMP VALUES('B', 12.0)

SELECT 
    (SELECT TOP 1 Value 
        FROM (SELECT TOP(calcs.medianIndex) Value 
                FROM #temp 
                WHERE #temp.ID = calcs.ID ORDER BY Value ASC) AS subSet
        ORDER BY subSet.Value DESC), ID
FROM
(SELECT 
    CASE WHEN count(*) % 2 = 1 THEN count(*)/2 + 1
        ELSE count(*)/2
    END AS medianIndex,
 ID
FROM #TEMP 
GROUP BY ID) AS calcs

DROP TABLE #TEMP

Might want to double check the behavior when there is an even number of records.

EDIT: After reviewing your work in your Median function, I realize that my answer basically just moved your work out of the function and into your regular query. So... why does your median calculation have to be inside of the user-defined function? It seems alot more difficult that way.

+1 Actually, you are right. I was thinking it would be more elegant having an aggregate function but apparently I can get this done without one. But nevertheless thank you for your efforts. — Legend, Oct 07 '11 at 22:02

Using a custom aggregate function in a GROUP BY?

3 Answers3