how to compute standard deviation in sql without built-in function

Question

I have a column of numbers in my database. How can I computer the standard deviation? I do not want use the stddev function.

Then use other functions that are needed to calculate std deviation. If you need the mean in the formula then just use the AVG and count(), whatever is needed, basically use other aggregate functions to make the std dev. — Omid CompSCI
– Omid CompSCI, Commented Oct 9, 2016 at 21:02
SELECT SQRT(SUM( (number-AVG(number))*(number-AVG(number)) /COUNT(value)) FROM mytable — tonny
– tonny, Commented Oct 9, 2016 at 21:03
Why don't you want to use the stddev function? And which DBMS are you using? Postgres? Oracle? — user330315
– user330315, Commented Oct 9, 2016 at 21:05
I don't think your query will work without GROUP BY unless your group is the entire SELECTed set. You've got aggregate functions, e.g., SUM and COUNT — Seth Difley
– Seth Difley, Commented Oct 9, 2016 at 21:06

John Cappelletti · Accepted Answer · 2016-10-10 03:36:15Z

3

Just because I was curious, I decided to test the actual STDEV(). Now, I could not nail the built in function.

I was close... 0.000141009220002264 or 0.00748% off

Also, The Total Average and Count has to be converted to float (variance was greater with decimal)

The example below is going after my Treasury Rates Table for the 10 Year Yield (not that it matters)

Select SQLFunction = Stdev([TR_Y10]) 
      ,ManualCalc  = Sqrt(Sum(Power(((cast([TR_Y10] as float)-B.TotalAvg)),2) / B.TotalCnt))
      ,Variance    = Stdev([TR_Y10]) - Sqrt(Sum(Power(((cast([TR_Y10] as float)-B.TotalAvg)),2) / B.TotalCnt))
 From [Chinrus-Shared].[dbo].[DS_Treasury_Rates]
 Join (Select TotalAvg=Avg(cast([TR_Y10] as float)),TotalCnt=count(*) From [Chinrus-Shared].[dbo].[DS_Treasury_Rates]) B on 1=1

Returns

SQLFunction         ManualCalc          Variance
1.88409468982299    1.88395368060299    0.000141009220002264

edited Oct 10, 2016 at 3:36

answered Oct 9, 2016 at 21:45

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-10-09 23:07:15Z

0

The standard deviation is the square root of the variance divided by n.

The variance is the sum of the squares of the differences between the average and the observed value.

So, in most databases, you can use window functions:

select sqrt(avg(var))
from (select square(t.x - avg(t.x) over ()) as var
      from t
     ) t;

Notes:

The square() function might have some other name (such as power()).
The sqrt() function might have some other name.
This is not a good way to calculate the standard deviation in general. In particular, this is a numerically unstable algorithm (it will work just fine for finite numbers of normal numbers).
The subquery is needed because window functions cannot be the arguments to aggregation functions.

answered Oct 9, 2016 at 23:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

John Cappelletti Over a year ago

Fascinating. Three calculations, three different results. Makes one question gravity. Results: MS> 1.88409468982299 JC> 1.88395368060299 GL> 1.88395368262047.

Gordon Linoff Over a year ago

@JohnCappelletti . . . John, it is not surprising that we cannot reproduce the results of the built-in function. Hopefully, that implementation uses a method that is more numerically stable, which can produce the small variations in unimportant decimal places.

Collectives™ on Stack Overflow

how to compute standard deviation in sql without built-in function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related