PostgreSQL 计算 任意类型 字段之间的线性相关性
背景
PostgreSQL自带了计算numeric和numeric字段的线性相关性的聚合函数corr(numeric, numeric)。
例如：
postgres=# select corr(c1,c2) from (values (1,2),(2,1),(100,90),(13,13),(25,27) ) t(c1,c2);
corr
-------------------
0.998528203831946
(1 row)
postgres=# \df+ corr
List of functions
Schema | Name | Result data type | Argument data types | Type | Security | Volatility | Owner | Language | Source code | Description
------------+------+------------------+------------------------------------+------+----------+------------+----------+----------+-----------------+-------------------------
pg_catalog | corr | double precision | double precision, double precision | agg | invoker | immutable | postgres | internal | aggregate_dummy | correlation coefficient
(1 row)
如果要计算多元的线性相关性，可以使用madlib提供的linregr_train函数来统计。
http://doc.madlib.net/latest/group__grp__linreg.html
注意不管是一元回归还是多元回归，都需要提供数字类型，如果是文本是不支持的，如下：
postgres=# select corr(c1,c3) from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou') ) t(c1,c2,c3);
ERROR: function corr(integer, text) does not exist
LINE 1: select corr(c1,c3) from (values (1,2,'test'),(2,1,'digoal'),...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
那么怎么处理呢？
PostgreSQL提供了强大的窗口功能，因为任意字段都可以排序，所以只要使用窗口输出字段排序后的rank()就可以代表它的位置从而计算相关性。
例如
postgres=# select
c1, rank() over(order by c1) rc1,
c2, rank() over(order by c2) rc2,
c3, rank() over(order by c3) rc3
from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou') )
t(c1,c2,c3) order by c1;
c1 | rc1 | c2 | rc2 | c3 | rc3
-----+-----+----+-----+----------+-----
1 | 1 | 2 | 2 | test | 5
2 | 2 | 1 | 1 | digoal | 2
13 | 3 | 13 | 3 | china | 1
25 | 4 | 27 | 4 | hangzhou | 3
100 | 5 | 90 | 5 | hello | 4
(5 rows)
这个例子要计算c1,c3的相关性，c1是数字字段，但是c3是text。 corr函数不支持这么操作。
因此我使用上面这条带窗口的SQL，把text字段根据rank抽象为数值，正好和其他字段可以匹配相关性。
来看计算结果：
postgres=# select corr(c1,rc3), corr(rc1,rc3) from (
select
c1, rank() over(order by c1) rc1,
c2, rank() over(order by c2) rc2,
c3, rank() over(order by c3) rc3
from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou')
) t(c1,c2,c3)
) t;
corr | corr
-------------------+------
0.283302495025433 | -0.1
(1 row)
建议采用corr(rc1,rc3)的结果，这个比较有代表性。 代表被评测列的线性相关性。
应用场景大家猜一猜。