MySQLとPostgreSQLにおける相関係数

数値の間に相関性があるかどうか説明する場合に、数値化して求められる事があるケースがあると思う。参考値として相関係数を算出して説明する為のMySQLとPostgreSQLの関数概要。
PostgreSQLにはCORRという関数がある。MySQLには相関係数を算出する関数自体は無いが算出する事が可能。比較軸にもよるけれど、説明する上で数値的な参考値としては使える。

−1以上1以下の実数に値をとる。相関係数が正のとき確率変数には正の相関が、負のとき確率変数には負の相関があるという。また相関係数が0のとき確率変数は無相関であるという
参照:WIKI
参考:日経

例)広告宣伝費と売り上げの相関性のシュミレーション

EXCELの場合

CORREL関数を利用
CORREL

下の表は、相関性が高いので相関係数が1になっている。
CORREL関数

PostgreSQLの場合


rails_app=# select corr(ad,sales) from t1;
       corr        
-------------------
 0.848628572784027
(1 row)

rails_app=# select * from t1;
 ad | sales 
----+-------
 10 |    11
 20 |    30
 30 |    20
 40 |    30
 50 |    50
(5 rows)

MySQLの場合


[confirm]> select (( SUM( ad * sales ) - SUM( ad ) * SUM( sales ) / COUNT( ad ) ) / COUNT( sales )) / (STDDEV_POP(ad) * STDDEV_POP(sales)) as corr from t1;
+--------------------+
| corr               |
+--------------------+
| 0.8486285727840269 |
+--------------------+
1 row in set (0.00 sec)

 [confirm]> select * from t1;
+------+-------+
| ad   | sales |
+------+-------+
|   10 |    11 |
|   20 |    30 |
|   30 |    20 |
|   40 |    30 |
|   50 |    50 |
+------+-------+
5 rows in set (0.01 sec)

補足:負の相関関係


root@localhost [confirm]> select (( SUM( ad * price ) - SUM( ad ) * SUM( price ) / COUNT( ad ) ) / COUNT( price )) / (STDDEV_POP(ad) * STDDEV_POP(price)) as corr from t1;
+---------------------+
| corr                |
+---------------------+
| -0.9999999999999999 |
+---------------------+
1 row in set (0.00 sec)

root@localhost [confirm]> select * from t1;
+------+-------+
| ad   | price |
+------+-------+
|   10 |  1000 |
|   20 |   800 |
|   30 |   600 |
|   40 |   400 |
|   50 |   200 |
+------+-------+
5 rows in set (0.00 sec)

root@localhost [confirm]> 


MYSQLでの日付比較演算子の確認

## 月跨ぎでページがエラーになってしまっていた。


$query = "select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmation
where GET_D > CURDATE() - 8 group by GET_D";

## DB側にてデータの確認

mysql> select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmation
where GET_D > CURDATE() - 8 group by GET_D;
Empty set, 1 warning (0.02 sec)

## データはあるので、where句の部分を確認。
## 実際にQueryを実行してみると以下のような日付が返ってきた。


mysql>select CURDATE() - 8;
+---------------+
| CURDATE() - 8 |
+---------------+
| 20100893 |
+---------------+
1 row in set (0.00 sec)


mysql> select CURRENT_DATE() - 8;
+--------------------+
| CURRENT_DATE() - 8 |
+--------------------+
| 20100893 |
+--------------------+
1 row in set (0.00 sec)

## 日付の表示を正しいものに修正する為に、DATA_ADDを利用する事にした。

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL 8 DAY);
+—————————————+
| DATE_ADD(CURRENT_DATE,INTERVAL 8 DAY) |
+—————————————+
| 2010-09-09 |
+—————————————+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY);
+—————————————-+
| DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY) |
+—————————————-+
| 2010-08-24 |
+—————————————-+
1 row in set (0.00 sec)

mysql>

## 正しくデータが表示される事を確認

mysql> select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmationwhere GET_D > DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY) group by GET_D;
+————+——+
| GET_D | AVGT |
+————+——+
| 2010-08-25 | 0.77 |
| 2010-08-26 | 0.48 |
| 2010-08-27 | 0.32 |
| 2010-08-28 | 0.30 |
| 2010-08-29 | 0.28 |
| 2010-08-30 | 0.35 |
| 2010-08-31 | 0.34 |
+————+——+
7 rows in set (0.02 sec)

mysql>


select substring(GET_T,1,2) as GET_T,COUNT(*) as TOTAL from
T_Confirmation where MONTH(GET_D)= MONTH(DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY))
AND TAKEN >= 10 Group by substring(GET_T,1,2);

MONTH(DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY))

## 全てのPHPファイルを修正する為に、ワンライナーで一気に書き換えで終了。
## ワンライナーでフォルダー配下のPHPを全て修正して解決!!

perl -pi -e 's/CURDATE\(\) - 8/DATE_ADD\(CURRENT_DATE,INTERVAL -8 DAY\)/' *.php


大文字/小文字を混ぜ合わせてCHARのテーブルとBINARYのテーブルに
データをINSERTしてCOUNTなどで結果を比較。

TABLE CREATE WITH CHAR


mysql> desc ENGLISH;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from ENGLISH;
+---------+
| name |
+---------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+---------+
4 rows in set (0.00 sec)

mysql> select distinct name from ENGLISH;
+---------+
| name |
+---------+
| English |
+---------+
1 row in set (0.00 sec)

mysql> select name,count(*) from ENGLISH group by name;
+---------+----------+
| name | count(*) |
+---------+----------+
| English | 4 |
+---------+----------+
1 row in set (0.00 sec)

char_table

TABLE CREATE WITH BINARY


mysql> desc ENGLISH_BIN;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | binary(10) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from ENGLISH_BIN;
+------------+
| name |
+------------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+------------+
4 rows in set (0.00 sec)

mysql> select distinct name from ENGLISH_BIN;
+------------+
| name |
+------------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+------------+
4 rows in set (0.00 sec)

mysql> select name,count(*) from ENGLISH_BIN group by name;
+------------+----------+
| name | count(*) |
+------------+----------+
| ENGLISH | 1 |
| English | 1 |
| eNgLiSh | 1 |
| english | 1 |
+------------+----------+
4 rows in set (0.00 sec)

binary_table