STDDEV, STDDEV_POP and STDDEV_SAMP Analytic Functions in Oracle SQL
Standard deviation is a number used to tell how measurements for a group are spread out from the average (mean), or expected value.
A low standard deviation means that most of the numbers are very close to the average.
A high standard deviation means that the numbers are spread out.
STDDEV : aggregate functions are used to calculate the standard deviation.
Syntax:
STDDEV( expr) [ OVER ]
STDDEV_POP: aggregate function are used to calculate the population standard deviation.
Syntax:
STDDEV_POP(expr) [ PARTITION | OVER ]
STDDEV_SAMP: aggregate function are used to calculate the cumulative sample standard deviation of a set of data respectively
Syntax:
STDDEV_SAMP(expr) [ PARTITION | OVER ]
Example of Standard deviation calculation
Calculate the population standard deviation & standard deviation:
1. Consider 9 number
1 2 3 4 5 6 7 8 9 = 45
2. Find the average of 9 number:
Average = 45/9 = 5
3. Do the difference of each number in the list from the mean.
4. Square the result of each difference
1-5 = -4 = (-4*-4) = 16
2-5 = -3 = (-3*-3) = 9
3-5 = -2 = (-2*-2) = 4
4-5 = -1 = (-1*-1) = 1
5-5 = 0 = (0*0) = 0
6-5 = 1 = (1*1) = 1
7-5 = 2 = (2*2) = 4
8-5 = 3 = (3*3) = 9
9-5 = 4 = (4*4) = 16
5. Find average of values:
16+9+4+1+0+1+4+9+16 = 60/9 = 6.66 = population standard deviation.
60/8 = 7.5 = sample standard deviation
6. Take square root.
6.66 square root = 2.58 = population standard deviation.
7.5 square root = 2.73 = sample standard deviation
Note:
If n is used at step 5 then it is population standard deviation.
If n-1 at 5 step is used then it is sample standard deviation.
Example with SQL function to calculate standard deviation
SQL> create table test ( id number);
SQL> insert into test values(1);
SQL> insert into test values(2);
SQL> insert into test values(3);
SQL> insert into test values(4);
SQL> insert into test values(5);
SQL> insert into test values(6);
SQL> insert into test values(7);
SQL> insert into test values(8);
SQL> insert into test values(9);
SELECT STDDEV(id) AS stddev, STDDEV_POP(id) AS stddev_pop,
STDDEV_SAMP(id) AS stddev_samp
FROM test;
STDDEV STDDEV_POP STDDEV_SAMP
---------- ---------- -----------
2.73861279 2.5819889 2.73861279
Example using over clause
SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) “StdDev”,
STDDEV_SAMP(salary) OVER ( ORDER BY hire_date ) “STDDEVSAMP”,
STDDEV_POP(salary) OVER (ORDER BY hire_date ) “STDDEVPOP”
FROM employees
WHERE department_id = 30
ORDER BY last_name, salary, “StdDev”;
LAST_NAME SALARY StdDev STDDEVSAMP STDDEVPOP ---------- ---------- ---------- ---------- ---------- Baida 2900 4705.67034 4705.67034 3842.16374 Colmenares 2500 3715.23889 3715.23889 3323.01068 Himuro 2600 4118.55557 4118.55557 3566.77375 Khoo 0 0 Raphaely 11000 0 0 Tobias 2800 5798.27561 5798.27561 4100
Smart way of Technology 