MySQL存储过程：计算数组字符串的直线斜率

DELIMITER $$ CREATE PROCEDURE calculate_slope(IN array_str VARCHAR(100), OUT slope DECIMAL(10,2)) BEGIN DECLARE array_length INT; DECLARE i INT DEFAULT 1; DECLARE sum_x DECIMAL(10,2) DEFAULT 0; DECLARE sum_y DECIMAL(10,2) DEFAULT 0; DECLARE sum_xy DECIMAL(10,2) DEFAULT 0; DECLARE sum_x_squared DECIMAL(10,2) DEFAULT 0; DECLARE x DECIMAL(10,2); DECLARE y DECIMAL(10,2); DECLARE array_values CURSOR FOR SELECT * FROM split_string(array_str, ',');

SET array_length = (SELECT COUNT(*) FROM split_string(array_str, ','));

OPEN array_values;
FETCH array_values INTO x;
FETCH array_values INTO y;

WHILE i <= array_length DO
    SET sum_x = sum_x + x;
    SET sum_y = sum_y + y;
    SET sum_xy = sum_xy + (x * y);
    SET sum_x_squared = sum_x_squared + (x * x);
    
    FETCH array_values INTO x;
    FETCH array_values INTO y;
    SET i = i + 2;
END WHILE;

CLOSE array_values;

SET slope = ((array_length * sum_xy) - (sum_x * sum_y)) / ((array_length * sum_x_squared) - (sum_x * sum_x));

END$$

DELIMITER ;

-- The following function splits a string into an array of values CREATE FUNCTION split_string(str VARCHAR(255), delimiter VARCHAR(12)) RETURNS @returnList TABLE (value VARCHAR(255)) BEGIN DECLARE idx INT; DECLARE value VARCHAR(255); WHILE LENGTH(str) > 0 DO SET idx = INSTR(str, delimiter); IF idx = 0 THEN SET idx = LENGTH(str) + 1; END IF; SET value = LEFT(str, idx - 1); SET str = SUBSTRING(str, idx + LENGTH(delimiter)); INSERT INTO @returnList(value) VALUES (value); END WHILE; RETURN; END$$

DELIMITER ;

-- Call the stored procedure with the array string as input parameter CALL calculate_slope('11,12,19,14,15,16,12', @slope); SELECT @slope AS 'Slope';