最近遇到了对一组数据进行统计计算的需求,包括平均值、总和、最大值、最小值、标准差等,代码如下:
SELECT T.BUSIDATE "dimen",
SUM(T.BUSI_OCCURRENCE) "sum",
TRUNC(AVG(T.BUSI_OCCURRENCE), 1) "avg", /*平均值*/
TRUNC(STDDEV(T.BUSI_OCCURRENCE), 1) "stdev", /*样本标准差*/
TRUNC(STDDEV_POP(T.BUSI_OCCURRENCE), 1) "stdevp", /*总体标准差*/
TRUNC(STDDEV_SAMP(T.BUSI_OCCURRENCE), 1) "stdevs",
MAX(T.BUSI_OCCURRENCE) "max",
MIN(T.BUSI_OCCURRENCE) "min"
FROM SRF_DATAMODEL_RESULT T
WHERE
T.BUSI_OCCURRENCE IS NOT NULL
AND T.MODELID = '10000021'
GROUP BY T.BUSIDATE
ORACLE中有三种常用的标准差方法:STDDEV、STDDEV_POP和STDDEV_SAMP
STDDEV_POP STDDEV 、STDDEV_SAPM
STDDEV返回样本标准差表达式 ,你可以用它作为聚合或分析函数。它不同于STDDEV_SAMP函数,当只有一行数据时,STDDEV返回0, 而STDDEV_SAMP返回null值。而STDDEV_POP用来计算总体标准差,用的比较多的是STDDEV