一、前言
在oracle21c版本中，添加了4個(gè)有關(guān)峰度和偏度的聚合函數(shù)，分別是
- SKEWNESS_POP-總體偏度
- SKEWNESS_SAMP-樣本偏度
- KURTOSIS_POP-總體峰度
- KURTOSIS_SAMP-樣本峰度
這4個(gè)函數(shù)與統(tǒng)計(jì)學(xué)有關(guān)，至于有什么用，不是本篇重點(diǎn)，請(qǐng)自行檢索相關(guān)文獻(xiàn)。但要注意的是，目前國(guó)內(nèi)某些百科，由于翻譯或者語(yǔ)境的問題，導(dǎo)致相關(guān)描述存在問題。如果想對(duì)此方面有更詳細(xì)了解的，建議多搜索一些國(guó)外的文章。
二、原公式
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為項(xiàng)數(shù),s為標(biāo)準(zhǔn)差，SQRT為取平方根
三、Excel公式計(jì)算
在微軟office的excel中，自帶有skew、skew.p、kurt三個(gè)函數(shù)，金山wps的表格里只有skew、kurt兩個(gè)。
下面我們以SKEWNESS_SAMP(excel里對(duì)應(yīng)skew)為例，來用公式進(jìn)行模擬計(jì)算，看是否和excel中自帶函數(shù)計(jì)算一致。（文末附該演示案例的excel文件下載地址）
1.準(zhǔn)備數(shù)據(jù)
假設(shè)我們有一列這樣的數(shù)據(jù)
2
3
-1
3
4
5
0
2
2.計(jì)算相關(guān)統(tǒng)計(jì)值
可以得到其項(xiàng)數(shù)n=8，平均值x?=2.25，標(biāo)準(zhǔn)差s=1.9821，
(標(biāo)準(zhǔn)差在各可計(jì)算的軟件及開發(fā)語(yǔ)言中一般都有自帶函數(shù)，一般名稱為stddev，因此不在此做模擬演算，其公式為s=sqrt(((x1-x?)^2 +(x2-x?)^2 +…(xn-x?)^2)/n ))
3.計(jì)算每一項(xiàng)與平均值的差
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.計(jì)算這列差每一項(xiàng)的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次方后的結(jié)果求和
得到 -18.75，即SKEWNESS_SAMP公式中最復(fù)雜的分子部分
6.除以分母得到最終結(jié)果
-18.75/((8-1)*(8-2)*1.9821^3)=-0.4587
7.和excel函數(shù)比較
可以發(fā)現(xiàn)結(jié)果完全一致
8.計(jì)算SKEWNESS_POP(即excel中的skew.p）
-0.4587*(8–2)/SQRT(8(8–1))=-0.3677
9.關(guān)于KURTOSIS_SAMP和KURTOSIS_POP
請(qǐng)參考文末的excel表格，自行增加下圖標(biāo)黃部分,就當(dāng)留給讀者的課后作業(yè)了
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計(jì)算
有了上面excel公式模擬作為鋪墊后，我們就能用其他程序語(yǔ)言來進(jìn)行模擬了，這里以sql為例，只為描述邏輯。如果在生產(chǎn)中考慮到內(nèi)存占用的問題，建議改寫成plsql
--注：以下any_value函數(shù)為oracle19c新增，更老的版本請(qǐng)改成max或者min
--21c函數(shù)
select SKEWNESS_POP(salary), SKEWNESS_SAMP(salary) from hr.employees a;
--sql公式計(jì)算
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函數(shù)
select KURTOSIS_SAMP(salary) ,KURTOSIS_POP(salary) from hr.employees a;
--sql公式計(jì)算
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執(zhí)行對(duì)比截圖
五、將函數(shù)移植到其他數(shù)據(jù)庫(kù)
比如openGauss(base on postgresql)
代碼請(qǐng)參考我的gitee項(xiàng)目
https://gitee.com/darkathena/opengauss-oracle/tree/master/oracle-function
參考資料
- https://excelmaster.co/skewness-of-data/ (文中的excel文件可在此下載)
- http://www.real-statistics.com/descriptive-statistics/symmetry-skewness-kurtosis/
- https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/KURTOSIS_POP.html
- 本文作者： DarkAthena
- 本文鏈接： https://www.darkathena.top/archives/skew-kurt-sql-excel
- 版權(quán)聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協(xié)議。轉(zhuǎn)載請(qǐng)注明出處！
