巧用 GROUP_CONCAT 實現 MySQL 聚合乘法（自定義函數 func_multiple）

巧用 GROUP_CONCAT 實現 MySQL 聚合乘法（自定義函數 func_multiple）

MySQL 內建聚合函數有 SUM、AVG、MAX、MIN，卻缺少乘法聚合（PRODUCT）。本文示範如何結合 GROUP_CONCAT 與自定義函數 func_multiple 實現分組乘積計算。

測試資料準備

CREATE TABLE tb_seq (
  num INT(10) NOT NULL,
  seq_type ENUM('yellow','green','red') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tb_seq (num, seq_type) VALUES
(4,'green'),(1,'red'),(3,'green'),(1,'red'),(8,'red'),
(4,'yellow'),(8,'red'),(7,'yellow'),(10,'red'),
(1,'red'),(1,'red'),(1,'yellow'),
(5,'green'),(9,'red'),(1,'yellow'),(6,'yellow');

自定義聚合乘法函數

DELIMITER $$
CREATE FUNCTION func_multiple(f_nums VARCHAR(1000)) RETURNS DOUBLE(10,2)
BEGIN
  DECLARE result DOUBLE(10,2) DEFAULT 1;
  DECLARE cnt, i INT DEFAULT 0;
  SET cnt = CHAR_LENGTH(f_nums) - CHAR_LENGTH(REPLACE(f_nums, ',', '')) + 1;
  WHILE i < cnt DO
    SET result = result * REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_nums, ',', i+1)), ',', 1));
    SET i = i + 1;
  END WHILE;
  RETURN ROUND(result, 2);
END$$
DELIMITER ;

原理：將 GROUP_CONCAT 產生的逗號分隔字串拆解，迴圈逐一相乘。

查詢各類別乘積

SELECT seq_type,
       func_multiple(GROUP_CONCAT(num ORDER BY num ASC SEPARATOR ',')) AS multiple_num
FROM tb_seq
GROUP BY seq_type;

執行結果

適用場景與注意

• 適用於數值型欄位的分組乘積（如複利計算、機率連乘）。
• GROUP_CONCAT 預設最大長度 1024 字節，大數據量需調大 group_concat_max_len。
• 函數使用字串操作，效能不如原生聚合，百萬行級建議應用層計算或使用 UDF。