## PostgreSQL 计算 任意类型 字段之间的线性相关性  
                                                                                                                      
### 作者                                                                                                                          
digoal                                                                                                                          
                                                                                                                      
### 日期                                                                                                                          
2016-04-03                                                                                                                     
                                                                                                                      
### 标签                                                                                                                          
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)的结果，这个比较有代表性。 代表被评测列的线性相关性。     
  
应用场景大家猜一猜。    
                                                      
                                                                        
                                                                    
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
#### [PostgreSQL 许愿链接](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216")
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。[开不开森](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216").  
  
  
#### [9.9元购买3个月阿里云RDS PostgreSQL实例](https://www.aliyun.com/database/postgresqlactivity "57258f76c37864c6e6d23383d05714ea")
  
  
#### [PostgreSQL 解决方案集合](https://yq.aliyun.com/topic/118 "40cff096e9ed7122c512b35d8561d9c8")
  
  
#### [德哥 / digoal's github - 公益是一辈子的事.](https://github.com/digoal/blog/blob/master/README.md "22709685feb7cab07d30f30387f0a9ae")
  
  
![digoal's wechat](../pic/digoal_weixin.jpg "f7ad92eeba24523fd47a6e1a0e691b59")
  
