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)的结果，这个比较有代表性。 代表被评测列的线性相关性。

应用场景大家猜一猜。