STDDEV, STDDEV_POP and STDDEV_SAMP Analytic Functions in Oracle SQL

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
Advertisements