629
技術社區[雲棲]
PostgreSQL 聚合函數講解 - 4 總體協方差, 樣本協方差
1. 數學期望值, 平均值, mean表示: E[X], 即X變量的平均值.
也用miu表示 : μ=E[X]
PostgreSQL中舉例 :
postgres=# select avg(c1) from (values(null),(1),(2)) as t(c1);
avg
--------------------
1.5000000000000000
(1 row)
以上為離散概率, 數學期望就是每個值乘以它的概率除以值的個數.
如果是連續概率的話, 要統計一個取值範圍的區間概率, 統計一個區間的麵積.
2. 眾數, mode
出現頻率最高的值.
PostgreSQL中舉例, 如果有多個同樣頻率的, 取第一個 :
postgres=# select mode() WITHIN GROUP (ORDER BY c1) from (values(null),(1),(2),(1),(2),(3)) as t(c1);
mode
------
1
(1 row)
postgres=# select mode() WITHIN GROUP (ORDER BY c1 desc) from (values(null),(1),(2),(1),(2),(3)) as t(c1);
mode
------
2
(1 row)
其實在PostgreSQL統計信息中也用到了眾數類似(延展)的概念, 即mcv, mce. 最頻繁出現的值. 見pg_stats.
postgres=# \d pg_stats
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer | 數學期望
n_distinct | real |
most_common_vals | anyarray | 眾數類似
most_common_freqs | real[] | 眾數概率
histogram_bounds | anyarray | 中位數類似
correlation | real | 相關度
most_common_elems | anyarray | 眾數類似
most_common_elem_freqs | real[] | 眾數概率
elem_count_histogram | real[] | 中位數類似
3. 中位數, Median
取按數字大小順序排好後的中間數字, 如果是偶數個, 則取兩個值的平均值.
例如: 1,3,5,7,9的中位數為5.
例如: 1,3,5,7,9,10的中位數為(5+7)/2=6
中位數在PostgreSQL統計信息中也有類似的應用, 即柱狀分布, 見pg_stats.histogram_bounds. 每個區間(bucket)的概率是相等的, 每個值的兩邊的bucket概率是相等的. 類似中位數的概念.
4. 方差, Variance
方差是各個數據分別與其平均數之差的平方的和的平均數，用字母D表示。在概率論和數理統計中，方差（Variance）用來度量隨機變量和其數學期望（即均值）之間的偏離程度。在許多實際問題中，研究隨機變量和均值之間的偏離程度有著重要意義。
設X為服從分布F的隨機變量， 如果E[X]是隨機變量X的期望值（平均數μ=E[X]）
隨機變量X或者分布F的方差為
(總體方差) :
樣本方差是依據所給樣本對方差做出的一個無偏估計。
樣本方差的計算公式以及表述符號為:
其中：
為樣本均值 N為樣本個數
為樣本均值 N為樣本個數PostgreSQL中舉例 :
從計算方法來看.方差可以評估一組數字的離散度(如下).
總體方差.
postgres=# select var_pop(c1) from (values(1),(2),(3),(4),(5)) as t(c1);
var_pop
--------------------
2.0000000000000000
(1 row)
postgres=# select var_pop(c1) from (values(1),(2),(3),(4),(5),(1000)) as t(c1);
var_pop
---------------------
138058.472222222222
(1 row)
postgres=# select var_pop(c1) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
var_pop
--------------------
3.8055555555555556
(1 row)
樣本方差
postgres=# select var_samp(c1) from (values(1),(2),(3),(4),(5)) as t(c1);
var_samp
--------------------
2.5000000000000000
(1 row)
postgres=# select var_samp(c1) from (values(1),(2),(3),(4),(5),(1000)) as t(c1);
var_samp
---------------------
165670.166666666667
(1 row)
postgres=# select var_samp(c1) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
var_samp
--------------------
4.5666666666666667
(1 row)
5. 標準差, Standard Deviation, 對應方差的平方根. 也可用於評估離散度.
表示符號和方差一樣, 但是符號上頭沒有代表平方的2.
標準差（Standard Deviation） ，中文環境中又常稱均方差，但不同於均方誤差（mean squared error，均方誤差是各數據偏離真實值的距離平方的平均數，也即誤差平方和的平均數，計算公式形式上接近方差，它的開方叫均方根誤差，均方根誤差才和標準差形式上接近），標準差是離均差平方和平均後的方根，用σ表示。標準差是方差的算術平方根。標準差能反映一個數據集的離散程度。平均數相同的，標準差未必相同。
標準差（Standard Deviation），在概率統計中最常使用作為統計分布程度（statistical dispersion）上的測量。標準差定義是總體各單位標準值與其平均數離差平方的算術平均數的平方根。它反映組內個體間的離散程度。
總體標準差
postgres=# select stddev_pop(c1) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
stddev_pop
--------------------
1.9507833184532709
(1 row)
postgres=# select dsqrt(var_pop(c1)) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
dsqrt
------------------
1.95078331845327
(1 row)
樣本標準差
postgres=# select stddev_samp(c1) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
stddev_samp
--------------------
2.1369760566432809
(1 row)
postgres=# select dsqrt(var_samp(c1)) from (values(1000),(1001),(1003),(1004),(1005),(1000)) as t(c1);
dsqrt
------------------
2.13697605664328
(1 row)
6. 協方差, Covariance
在概率論和統計學中，協方差用於衡量兩個變量的總體誤差(如果兩組數據的誤差一樣, 那麼和計算一組數據的方差得到的結果是一樣的)。
而方差是協方差的一種特殊情況，即當兩個變量是相同的情況。
注意這裏提到方差是兩個變量相等時的協方差, 一會我在PostgreSQL裏麵驗證一下.
在PostgreSQL中計算一組數據協方差的方法.
總體協方差
postgres=# select covar_pop(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
covar_pop
------------------
138058.472222222
(1 row)
樣本協方差
postgres=# select covar_samp(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
covar_samp
------------------
165670.166666667
(1 row)
在PostgreSQL中驗證 : 方差是兩個變量相等時的協方差
postgres=# select var_pop(c1) from (values(1),(2),(3),(4),(5),(1000)) as t(c1);
var_pop
---------------------
138058.472222222222
(1 row)
postgres=# select covar_pop(c1,c1) from (values(1),(2),(3),(4),(5),(1000)) as t(c1);
covar_pop
------------------
138058.472222222
(1 row)
驗證 : (如果兩組數據的誤差一樣, 那麼和計算一組數據的方差得到的結果是一樣的)。
如下兩組數據, 1,2,3,4,5,1000; 2,3,4,5,6,1001; 他們各自的方差一樣, 所以協方差也一樣
postgres=# select var_pop(c1) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
var_pop
---------------------
138058.472222222222
(1 row)
postgres=# select var_pop(c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
var_pop
---------------------
138058.472222222222
(1 row)
postgres=# select covar_pop(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
covar_pop
------------------
138058.472222222
(1 row)
7. 相關性, 線性相關性, Correlation
表示兩組數據的相關性, 相關值從0到1取值, 趨向1表示完全相關, 趨向0 表示完全不相關.
相關表和相關圖可反映兩個變量之間的相互關係及其相關方向，但無法確切地表明兩個變量之間相關的程度。於是，著名統計學家卡爾·皮爾遜設計了統計指標——相關係數(Correlation coefficient)。相關係數是用以反映變量之間相關關係密切程度的統計指標。相關係數是按積差方法計算，同樣以兩變量與各自平均值的離差為基礎，通過兩個離差相乘來反映兩變量之間相關程度；著重研究線性的單相關係數。
在PostgreSQL中的統計信息中也有相關性的應用, 例如用於評估數據的物理存儲和值的相關性, 方便優化器計算索引掃描的成本, 相關性越高, 越趨向索引, 因為離散塊掃描會更少. 可以參考如下.
在PostgreSQL中計算兩組數據的相關性.
postgres=# select corr(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)) as t(c1,c2);
corr
------
1
(1 row)
postgres=# select corr(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)) as t(c1,c2);
corr
--------------------
-0.652023240836194
(1 row)
另外可看百度提到的例子 :
表1 廣告費與月平均銷售額相關表 單位：萬元
|
年廣告費投入
|
月均銷售額
|
|---|---|
|
12.5 15.3 23.2 26.4 33.5 34.4 39.4 45.2 55.4 60.9
|
21.2 23.9 32.9 34.1 42.5 43.2 49.0 52.8 59.4 63.5
|
參照表1，可計算相關係數如表2：
|
序號
|
廣告投入(萬元)
x |
月均銷售額(萬元)
y |
|
|
|
|---|---|---|---|---|---|
|
1 2 3 4 5 6 7 8 9 10
|
12.5 15.3 23.2 26.4 33.5 34.4 39.4 45.2 55.4 60.9
|
21.2 23.9 32.9 34.1 42.5 43.2 49.0 52.8 59.4 63.5
|
156.25 234.09 538.24 696.96 1122.25 1183.36 1552.36 2043.04 3069.16 3708.81
|
449.44 571.21 1082.41 1162.81 1806.25 1866.24 2401.00 2787.84 3528.36 4032.25
|
265.00 365.67 763.28 900.24 1423.75 1486.08 1930.60 2386.56 3290.76 3867.15
|
|
合計
|
346.2
|
422.5
|
14304.52
|
19687.81
|
16679.09
|
-
=0.9942
相關係數為0.9942，說明廣告投入費與月平均銷售額之間有高度的線性正相關關係。
相關性越高, 說明廣告投入和銷售額的關係越明顯.
相關性是如何計算的呢? 實際上是 "協方差(x,y)除以(平方根(方差(x)*方差(y)))" .
postgres=# select corr(c1,c2) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)) as t(c1,c2);
corr
--------------------
-0.652023240836194
(1 row)
使用以上公式計算結果一致 :
postgres=# select covar_pop(c1,c2)/dsqrt(var_pop(c1)*var_pop(c2)) from (values(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)) as t(c1,c2);
?column?
--------------------
-0.652023240836194
(1 row)
[參考]
1. 百度百科
2. 百度詞條
3. 維基百科
5. 用到的PostgreSQL聚合函數源碼在此
src/backend/utils/adt/float.c
6. 更多玩法, 如果要在PostgreSQL中使用R語言或MADlib庫, 請參考如下.
最後更新：2017-04-01 13:37:06