【oracle】解析ORACLE21C新增的4个峰度和偏度函数的计算过程并使用excel及sql进行公式计算

一、前言

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

  1. SKEWNESS_POP-总体偏度
  2. SKEWNESS_SAMP-样本偏度
  3. KURTOSIS_POP-总体峰度
  4. KURTOSIS_SAMP-样本峰度

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

二、原公式

SKEWNESS_SAMP()=image.png

SKEWNESS_POP()=SKEWNESS_SAMP()*(n–2)/SQRT(n(n–1))

KURTOSIS_SAMP()=image.png

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函数比较

image.png
可以发现结果完全一致

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执行对比截图
image.png

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

比如openGauss(base on postgresql)
image.png
代码请参考我的gitee项目
https://gitee.com/darkathena/opengauss-oracle/tree/master/oracle-function

参考资料

  1. https://excelmaster.co/skewness-of-data/ (文中的excel文件可在此下载)
  2. http://www.real-statistics.com/descriptive-statistics/symmetry-skewness-kurtosis/
  3. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/KURTOSIS_POP.html

posted on 2022-01-03 17:20  DarkAthena  阅读(305)  评论(0)  编辑  收藏  举报

导航