分析函数-STDDEV，STDDEV_SAMP ，STDDEV_POP，VAR_POP，VAR_SAMP，VARIANCE

/*STDDEV：计算当前行关于组的标准偏离

STDDEV_SAMP ： 该函数计算累积样本标准偏离，并返回总体变量的平方根

STDDEV与 stddev_samp 的不同之处在于，当计算的输入数据只有一行时，stddev 返回 0，而
stddev_samp 返回 null*/

计算公式：

select stddev(salary) FROM hr.employees
WHERE department_id =20;--结果为4949.74746830583

--STDDEV,STDDEV_SAMP
SELECT SQRT(SUM( POWER((SALARY - (SELECT AVG(SALARY) FROM hr.employees
WHERE department_id =20)), 2)
/((SELECT COUNT(1) FROM hr.employees
WHERE department_id =20)-1 )))
FROM hr.employees
WHERE department_id =20;--结果为4949.74746830583




---STDDEV_POP标准方差

--计算公式

select STDDEV_POP(salary) FROM hr.employees
WHERE department_id =20;--结果为3500

SELECT SQRT(SUM( POWER((SALARY - (SELECT AVG(SALARY) FROM hr.employees
WHERE department_id =20)), 2)
/((SELECT COUNT(1) FROM hr.employees
WHERE department_id =20))))
FROM hr.employees
WHERE department_id =20;--结果为3500


/*VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略 null），VAR_POP
进行如下计算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)*/
select var_pop(salary) FROM hr.employees
WHERE department_id =20----结果为12250000

select count(1)/*COUNT(expr)*/ FROM hr.employees WHERE department_id =20;

select (sum(power(salary,2)) - power(sum(salary),2)/2)/2
FROM hr.employees WHERE department_id =20;----结果为12250000


/*VAR_SAMP
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略 null），VAR_SAMP 进
行如下计算：
(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)*/

select var_samp(salary) FROM hr.employees
WHERE department_id =20--结果为24500000

select count(1)/*COUNT(expr)*/ FROM hr.employees WHERE department_id =20;

select (sum(power(salary,2)) - power(sum(salary),2)/2)/(2-1)
FROM hr.employees WHERE department_id =20--结果为24500000



/*VARIANCE
功能描述：该函数返回表达式的变量，Oracle 计算该变量如下：
如果表达式中行数为 1，则返回 0
如果表达式中行数大于 1，则返回 VAR_SAMP*/

select VARIANCE(salary) FROM hr.employees
WHERE department_id =10--表达式中行数为 1 结果为0

select VARIANCE(salary) FROM hr.employees
WHERE department_id =20--表达式中行数大于1 结果为24500000