mysql 中位数

在 MySQL 中计算中位数可以使用以下两种方法：

方法一：使用内置函数

MySQL中的内置函数 `SUBSTRING_INDEX()` 可以用于截取字符串，通过将结果按照升序或降序排序，再选择中间的值就可以得到中位数。

示例：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column_name,',', ((LENGTH(column_name)-LENGTH(REPLACE(column_name,',',''))+1) DIV 2)),',',-1) AS median_value 
FROM table_name;

其中 `column_name` 是需要计算中位数的列名， `table_name` 是表名。

方法二：使用自定义函数

如果需要经常计算中位数，可以创建一个自定义函数来实现。以下是一个计算中位数的自定义函数的示例代码：

DELIMITER $$

CREATE FUNCTION `get_median`(`values` TEXT) RETURNS DECIMAL(10,2)
BEGIN
  DECLARE median DECIMAL(10,2);
  DECLARE position INT;

  SET @row_num = (SELECT COUNT(*) FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(values, ',', n.digit+1), ',', -1) AS DECIMAL(10,2)) AS value FROM
                  (SELECT @row := @row + 1 AS digit FROM
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                  (SELECT @row:=0) r) n
                WHERE n.digit < LENGTH(values)-LENGTH(REPLACE(values, ',', ''))+1
                ORDER BY value) AS x);

  SET position = @row_num/2 + 1;

  IF @row_num % 2 = 0 THEN
    SET median = (SELECT AVG(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(values, ',', n.digit+1), ',', -1) AS DECIMAL(10,2))) FROM
                  (SELECT @row_num:=@row_num-1) c,
                  (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(values, ',', n.digit+1), ',', -1) AS DECIMAL(10,2)) AS value FROM
                  (SELECT @row := @row + 1 AS digit FROM
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                  (SELECT @row:=0) r) n
                WHERE n.digit < LENGTH(values)-LENGTH(REPLACE(values, ',', ''))+1
                ORDER BY value
                LIMIT position-1, 2) ;
  ELSE
    SET median = (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(values, ',', n.digit+1), ',', -1) AS DECIMAL(10,2)) FROM
                  (SELECT @row_num:=@row_num-1) c,
                  (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(values, ',', n.digit+1), ',', -1) AS DECIMAL(10,2)) AS value FROM
                  (SELECT @row := @row + 1 AS digit FROM
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                  (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                  (SELECT @row:=0) r) n
                WHERE n.digit < LENGTH(values)-LENGTH(REPLACE(values, ',', ''))+1
                ORDER BY value
                LIMIT position-1, 1) ;
  END IF;

  RETURN median;
END$$

DELIMITER ;

使用方法：

SELECT get_median(column_name) AS median_value FROM table_name;

其中 `column_name` 是需要计算中位数的列名， `table_name` 是表名。