Hi,
I'm looking at creating some aggregrate table based on ~2hr pull from
the main DB and looking to get standard stuffs like min/max/ave etc.
I'm having some issues with getting stdev which would be representative
of the stdev of say 10 hours of data.
>From this website : (it references using SQL Server Analysis services
but I think the concept is the same for MySQL)
http://www.phptr.com/articles/printerfriendly.asp?p=337135&rl=1
1. Calculate sum of square of each sale
2. multiple the result of step 1 by the sales count
3. sum all sales
4. Square the result of step 3
5. Substract the result of step 4 from the result of step 2
6. Multiply the sales count by one less than sales count ("sales_count"
* ("sales_count" - 1))
7. Divide the result of step 5 by the result of step 6
8. Stdev will be the square root of step 7
The results are valid (verified with actual data) but I don't understand
the logic. All the Statistical books I've read marked stdev as sqrt
(sum(x - ave(x))^2 / (n - 1). The formula is very different, hence the
confusion.
All I know is that, it works. Only question is, why?
Can anyone explain this?