Calculating Average Excluding Outliers

Question

I'm looking for a SQL Server function that will let me take an average, excluding outliers. Basically something where:

1, 2, 1, 2, 7

Would average out to 1.5.

I'm going to implement this in a financial analysis Microsoft SQL script.

My query is way too long and complicated to fit here, and it's not something I want to post publicly. AVG doesn't look like it has an option to exclude outliers per se, though it does include a PARTITION BY.

Answer 1

A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average.

You then take the average of the new range.

This is quite a basic bit of code (don't forget the CAST to a DECIMAL) which you can expand upon to make it more suitable to your needs.

CREATE TABLE #nums (num INT);

INSERT INTO #nums VALUES (1), (2), (1), (2), (7);

WITH AvgStd AS (
  SELECT
    AVG(num) AS avgnum,
    STDEVP(num) AS stdnum
  FROM #nums AS n  
)
SELECT 
  AVG(CAST(num AS DECIMAL(5,2)))
FROM #nums AS n
CROSS JOIN AvgStd
WHERE num < (avgnum + stdnum)
  AND num > (avgnum - stdnum);

DROP TABLE #nums;