1 2 3 4 5 6 7 8 9 10 11 12 13 14
| drop procedure if exists sp_correlation;
delimiter ||
CREATE procedure sp_correlation (IN variable1 varchar(6), IN variable2 varchar(6)
begin
set @req=concat('SELECT count(observation) AS N, sum(',variable1,') AS sumX, sum(',variable2,') AS sumY,
sum(',variable1,'*,'variable1',) AS sumXX, sum(',variable2,'*,'variable2,') AS sumYY, sum(',variable1,'*',variable2,') AS sumXY
INTO @n,@sumX,@sumY,@sumXX, @sumYY, @sumXY FROM filtered_data;');
prepare exe from @req;
execute exe;
deallocate prepare exe;
SELECT (@n*@sumXY-@sumX*@sumY)/
sqrt((@n*@sumXX - @sumX*@sumX) *(@n*@sumYY-@sumY*@sumY)) AS correlation;
end||
delimiter ; |
Partager