Add Median() function to SQL Server - by johnlightfoot_yahoo.com

Status : 

 


28
0
Sign in
to vote
ID 344916 Comments
Status Active Workarounds
Type Suggestion Repros 4
Opened 5/19/2008 12:33:22 PM
Access Restriction Public

Description

Currently we have to write a function in SQL using rank() to get a median value back.  The problems with this are that we can only do one median at a time, it is not as performant as it could be, it can not be pre-written for users, and it is difficult to use.  Medians are currently a critical function in Financial institutions.  Microsoft needs to develop a median() function.  Oracle has this function and has had it a long time, so it makes it difficult to adopt SQL Server over Oracle.
Sign in to post a comment.
Posted by SAinCA on 11/5/2012 at 10:48 AM
This workbench article has many and various solutions: http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

Roll on the day when MS gives us a MEDIAN() and we don't have to pick and choose another way...
Posted by Umachandar [MSFT] on 7/13/2011 at 4:49 PM
Hello John,
I am happy to announce that SQL Server Denali has added support for analytic version of PERCENTILE_CONT and PERCENTILE_DISC distribution functions. This is compatible with ANSI SQL:2008 standards. You can use these functions to get MEDIAN also. See link below for more details:

http://msdn.microsoft.com/en-us/library/hh231473(v=SQL.110).aspx

We will consider adding support for the aggregate version of these distribution functions in a future release.

--
Umachandar, SQL Programmability Team
Posted by Wim SQL Server on 10/3/2008 at 5:51 AM
WITH Cnt AS
(SELECT COUNT(*) AS cnt FROM dbo.groups)
,RN AS
(SELECT val, ROW_NUMBER() OVER (order BY val) AS rn
FROM dbo.groups
)
SELECT (t1.Val+t2.Val)/2 AS median
FROM RN t1,RN t2,Cnt
where t1.rn=(Cnt.cnt+1)/2 and t2.rn=(Cnt.cnt+2)/2 ;

This quer is twice as fast as the firsdt one I provided, but I have no reason why..
Posted by Wim SQL Server on 9/10/2008 at 1:21 AM
Hi,
the fastest way I found is the following:
CREATE TABLE dbo.groups
(idke INT IDENTITY(1,1) NOT NULL ,
val float NOT NULL,
CONSTRAINT [PK_group] PRIMARY KEY NONCLUSTERED(idke),
CONSTRAINT [Cix_groups] UNIQUE CLUSTERED (val,idke));

WITH Cnt AS
(SELECT COUNT(*) AS cnt)
,RN AS
(SELECT val, ROW_NUMBER() OVER (order BY val) AS rn
FROM dbo.groups
)
SELECT AVG(1.*val) AS median
FROM RN JOIN Cnt
on rn IN ((cnt+1)/2,(cnt+2)/2)

When clustered on the value column no sort has to be done.

But indeed a median function into sql would be great.

Greets,Wim.
Posted by Jim [MSFT] on 5/23/2008 at 10:11 AM
Hi John,

Thankyou for this suggestion. Yes, we really should add an efficient Median().

We are aiming to invest lots of effort in improving T-SQL in the next release, and I've added Median() onto the candidate list.

Thanks,

Jim