MySQL 存储过程平分1

需求：指标平分权重 1。
子节点权重平分1，差值在最后一个节点找补。

存储过程

因为指标个数在 200 以内，所以 5 位小数就满足了。

DELIMITER $$
DROP PROCEDURE IF EXISTS averageWeight$$
CREATE PROCEDURE `averageWeight` (
	IN `total` int,
	OUT `average` DECIMAL (15, 5),
	OUT `last` DECIMAL (15, 5)
)
	BEGIN
		IF total = 0 THEN
			SET average = 0;
			SET last = 0;
		END IF;

		SELECT 1/total INTO average;
		SET @avgTotal = average * total;
		IF @avgTotal = 1 THEN
			SET last = average;
		ELSE
			-- @avgTotal > 1 则相当于 average - 差值
			SET last = average + (1 - @avgTotal);
		END IF;
	END $$
DELIMITER;

测试

SET @total = 109;

CALL averageWeight(@total, @average, @last);
SELECT @average, @last;
SELECT @average * (@total - 1) + @last;