SQL: Median function for SQL Server

I have been using a Transact-SQL query (see Method 1 – Update entire table below) to get the Median “Order Value” for an entire list of Customers. However, due to the quantity of data, it was taking for ever.

 

Therefore, this morning I decided to write a SQL Server User Defined Function (see Method 2 – Function below) 1 to update the Median Order Value for a single Customer. I then used a UPDATE query to update all of the customers. This took a lot less time to process 🙂

 

Method 1 – Update entire table

UPDATE C
SET C.MedianOrderValue = X.Median 
FROM dbo.Customer C
INNER JOIN
(	SELECT B.CustomerID, 
	(	(	SELECT MAX(CAST(OrderValue AS NUMERIC(25,6))) 
			FROM
			(	SELECT TOP 50 PERCENT OrderValue 
				FROM dbo.Orders
				WHERE CustomerID = B.CustomerID
				AND OrderValue IS NOT NULL
				ORDER BY OrderValue
			) AS H1
		)
		+
		(	SELECT MIN(CAST(OrderValue AS NUMERIC(25,6))) 
			FROM
			(	SELECT TOP 50 PERCENT OrderValue 
				FROM dbo.Orders
				WHERE CustomerID = B.CustomerID
				AND OrderValue IS NOT NULL
				ORDER BY OrderValue DESC
			) AS H2
		)
	) / 2 AS Median
	FROM dbo.Customers B
	GROUP BY CustomerID
) X
ON C.CustomerID = X.CustomerID

 

Method 2 – Function

CREATE FUNCTION GetMedianOrderValueForCustomer
	(@CustomerID AS Integer)
RETURNS Numeric(25,6)
AS
BEGIN
	DECLARE @MaxOrderValueFromTopHalf NUMERIC(25,6)
	DECLARE @MinOrderValueFromBottomHalf NUMERIC(25,6)
	DECLARE @MedianOrderValue NUMERIC(25,6)

	SELECT @MaxOrderValueFromTopHalf 
		= MAX(CAST(OrderValue AS NUMERIC(25,6)))
	FROM (
		SELECT TOP 50 PERCENT OrderValue
		FROM dbo.Orders
		WHERE CustomerID = @CustomerID 
		ORDER BY OrderValue
	) AS H1

	SELECT @MinOrderValueFromBottomHalf 
		= MIN(CAST(OrderValue AS NUMERIC(25,6)))
	FROM (
		SELECT TOP 50 PERCENT OrderValue 
		FROM dbo.Orders
		WHERE CustomerID = @CustomerID 
		ORDER BY OrderValue DESC
	) AS H2

	SET @MedianOrderValue = (@MaxOrderValueFromTopHalf 
			+ @MinOrderValueFromBottomHalf) / 2

	RETURN @Median
END

 

For more information regarding “Calculating Mean, Median and Mode with SQL Server”, please see the “Less Than Dot” blog, using the link below:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.