侧边栏壁纸
  • 累计撰写 125 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

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

DarkAthena
2022-01-03 / 0 评论 / 0 点赞 / 1331 阅读 / 5259 字

一、前言

在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.253=-0.015625
0.75
3= 0.421875
-3.253=-34.328125
0.75
3= 0.421875
1.753= 5.359375
2.75
3= 20.796875
-2.253=-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表格,自行增加下图标黄部分,就当留给读者的课后作业了
image.png

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
0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论