求计算表中薪资列中位数的PL/SQL用户自定义函数（UDF）

以下是适用于MySQL 8.0+的UDF，用于按升序计算指定表中salary列的中位数：

DELIMITER //

CREATE FUNCTION calculate_salary_median() 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total_count INT;
    DECLARE median_val DECIMAL(10,2);
    
    -- 获取非空薪资记录总数
    SELECT COUNT(salary) INTO total_count FROM your_table_name WHERE salary IS NOT NULL;
    
    IF total_count = 0 THEN
        RETURN NULL;
    END IF;
    
    -- 奇数条记录：取中间值
    IF total_count % 2 = 1 THEN
        SELECT salary INTO median_val
        FROM (
            SELECT salary, ROW_NUMBER() OVER (ORDER BY salary ASC) AS row_num
            FROM your_table_name
            WHERE salary IS NOT NULL
        ) AS sorted_salaries
        WHERE row_num = (total_count + 1) / 2;
    -- 偶数条记录：取中间两值的平均值
    ELSE
        SELECT AVG(salary) INTO median_val
        FROM (
            SELECT salary, ROW_NUMBER() OVER (ORDER BY salary ASC) AS row_num
            FROM your_table_name
            WHERE salary IS NOT NULL
        ) AS sorted_salaries
        WHERE row_num IN (total_count / 2, total_count / 2 + 1);
    END IF;
    
    RETURN median_val;
END //

DELIMITER ;

使用说明 #

• 替换代码中的your_table_name为你的实际表名
• 函数自动过滤salary为NULL的记录，确保计算准确性
• 返回结果为保留两位小数的数值类型，可根据需求修改DECIMAL(10,2)的精度参数
• 依赖MySQL 8.0及以上版本的窗口函数支持

内容的提问来源于stack exchange，提问作者jack