คำนวณสหสัมพันธ์ (Correlation) ใน MySQL Query

MySQL ไม่มีฟังก์ชัน CORR() ตรงๆ (มีใน MySQL 8.0+) แต่สามารถคำนวณ Pearson Correlation Coefficient ได้ด้วยสูตรทางสถิติ

สูตร Pearson Correlation

r = Σ((x - x̄)(y - ȳ)) / √(Σ(x - x̄)² Σ(y - ȳ)²)

ตัวอย่างคำนวณแบบขั้นตอน

1. สร้างตารางและข้อมูล

mysql> create table correlationDemo (value float not null, value2 float not null);
mysql> insert into correlationDemo values(1,10),(2,4),(3,5),(6,17);

2. คำนวณค่ากลางและค่าส่วนเบี่ยงเบนมาตรฐาน

mysql> select 
    @avg_x := avg(value),
    @avg_y := avg(value2),
    @std_x := stddev_samp(value),
    @std_y := stddev_samp(value2)
    from correlationDemo;

3. คำนวณ Correlation

mysql> select sum((value - @avg_x) * (value2 - @avg_y)) / ((count(*) - 1) * @std_x * @std_y) as correlation
    -> from correlationDemo;

+------------------+
| correlation      |
+------------------+
| 0.7008850777290727 |
+------------------+

MySQL 8.0+ ใช้ฟังก์ชัน built-in

SELECT CORR(value, value2) FROM correlationDemo;

การตีความผลลัพธ์

โค้ดสำหรับใช้ซ้ำ (Stored Function)

CREATE FUNCTION calc_correlation(table_name VARCHAR(64), col_x VARCHAR(64), col_y VARCHAR(64))
RETURNS FLOAT
READS SQL DATA
BEGIN
    DECLARE result FLOAT;
    SET @q = CONCAT('SELECT CORR(', col_x, ',', col_y, ') INTO @r FROM ', table_name);
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    RETURN @r;
END;