【oracle】解析oracle21c新增的4个峰度和偏度函数的计算过程并使用excel及sql进行公式计算 -m6米乐安卓版下载

一、前言

在oracle21c版本中，添加了4个有关峰度和偏度的聚合函数，分别是

1. skewness_pop-总体偏度
2. skewness_samp-样本偏度
3. kurtosis_pop-总体峰度
4. kurtosis_samp-样本峰度

这4个函数与统计学有关，至于有什么用，不是本篇重点，请自行检索相关文献。但要注意的是，目前国内某些百科，由于翻译或者语境的问题，导致相关描述存在问题。如果想对此方面有更详细了解的，建议多搜索一些国外的文章。

二、原公式

skewness_samp()=

skewness_pop()=skewness_samp()*(n–2)/sqrt(n(n–1))

kurtosis_samp()=

kurtosis_pop()=(kurtosis_samp()*(n-2)*(n-3)/(n-1)-6)/(n 1)

其中x̄为平均值，n为项数,s为标准差，sqrt为取平方根

三、excel公式计算

在微软office的excel中，自带有skew、skew.p、kurt三个函数，金山wps的表格里只有skew、kurt两个。
下面我们以skewness_samp(excel里对应skew)为例，来用公式进行模拟计算，看是否和excel中自带函数计算一致。（文末附该演示案例的excel文件下载地址）

1.准备数据

假设我们有一列这样的数据
2
3
-1
3
4
5
0
2

2.计算相关统计值

可以得到其项数n=8，平均值x̄=2.25，标准差s=1.9821，
(标准差在各可计算的软件及开发语言中一般都有自带函数，一般名称为stddev，因此不在此做模拟演算，其公式为s=sqrt(((x1-x̄)^2 (x2-x̄)^2 …(xn-x̄)^2)/n ))

3.计算每一项与平均值的差

2-2.25= -0.25
3-2.25= 0.75
-1-2.25= -3.25
3-2.25= 0.75
4-2.25= 1.75
5-2.25= 2.75
0-2.25= -2.25
2-2.25= -0.25

4.计算这列差每一项的3次方

-0.25^3=-0.015625
0.75^3= 0.421875
-3.25^3=-34.328125
0.75^3= 0.421875
1.75^3= 5.359375
2.75^3= 20.796875
-2.25^3=-11.390625
-0.25^3=-0.015625

5.将这3次方后的结果求和

得到 -18.75，即skewness_samp公式中最复杂的分子部分

6.除以分母得到最终结果

-18.75/((8-1)*(8-2)*1.9821^3)=-0.4587

7.和excel函数比较

可以发现结果完全一致

8.计算skewness_pop(即excel中的skew.p）

-0.4587*(8–2)/sqrt(8(8–1))=-0.3677

9.关于kurtosis_samp和kurtosis_pop

请参考文末的excel表格，自行增加下图标黄部分,就当留给读者的课后作业了

d7=(a7-f$9)^4
d15=sum(d7:d14)
e22=kurt(a7:a14)
e23=f12*(f12 1)*d15/((f12-1)*(f12-2)*(f12-3)*f10^4)-3*(f12-1)^2/((f12-2)*(f12-3))

四、sql计算

有了上面excel公式模拟作为铺垫后，我们就能用其他程序语言来进行模拟了，这里以sql为例，只为描述逻辑。如果在生产中考虑到内存占用的问题，建议改写成plsql

--注：以下any_value函数为oracle19c新增，更老的版本请改成max或者min
--21c函数
select skewness_pop(salary), skewness_samp(salary) from hr.employees a;
--sql公式计算
with tt as
 (select salary,
         count(1) over() ct,
         avg(salary) over() av,
         stddev(salary) over() st
    from hr.employees),
r as
 (select any_value(ct) ct,
         sum(power((salary - av), 3)) avp,
         any_value(power(st, 3)) stp
    from tt)
select ct * (avp / ((ct - 1) * (ct - 2) * stp)) skewness_pop_,
ct * (avp / ((ct - 1) * (ct - 2) * stp))*(ct-2)/sqrt(ct*(ct-1)) skewness_samp_ from r;
--21c函数
select kurtosis_samp(salary) ,kurtosis_pop(salary) from hr.employees a;
--sql公式计算
with tt as
 (select salary,
         count(1) over() ct,
         avg(salary) over() av,
         stddev(salary) over() st
    from hr.employees),
r as
 (select any_value(ct) ct,
         sum(power((salary - av), 4)) avp,
         any_value(power(st, 4)) stp
    from tt)
select ct * (ct   1) * avp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
       3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3)) kurtosis_samp_,
       ((ct * (ct   1) * avp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
       3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3)))*(ct-2)*(ct-3)/(ct-1)-6)/(ct 1) kurtosis_pop_
  from r;

sql执行对比截图

五、将函数移植到其他数据库

比如opengauss(base on postgresql)

代码请参考我的gitee项目
https://gitee.com/darkathena/opengauss-oracle/tree/master/oracle-function

参考资料

1. (文中的excel文件可在此下载)

• 本文作者：
• 本文链接：
• 米乐app官网下载的版权声明： 本博客所有文章除特别声明外，均采用 许可协议。转载请注明出处！