如何在MySQL中创建中位数计算函数以替换现有查询中的avg函数

方案1：MySQL 8.0+ 直接改写查询（无需自定义函数，推荐优先用） #

利用PERCENTILE_CONT窗口函数可以直接实现中位数计算，逻辑简单易读：

SELECT 
    brain_sku,
    -- 计算4周周销中位数
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY weeklysales_4wks) OVER (PARTITION BY brain_sku) AS Median_4Weeks,
    -- 计算12周周销中位数
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY weeklysales_12wks) OVER (PARTITION BY brain_sku) AS Median_12Weeks
FROM (
    SELECT 
        brain_sku,
        week(order_date) as week_date,
        SUM(quantity_ordered) as weeklysales_total,
        -- 单独拆出4周的销量
        CASE WHEN date(order_date) > date(DATE_SUB(NOW(), INTERVAL 4 WEEK)) THEN SUM(quantity_ordered) END AS weeklysales_4wks,
        -- 12周的销量
        SUM(quantity_ordered) as weeklysales_12wks
    FROM sales
    WHERE 
        date(order_date) > date(DATE_SUB(NOW(), INTERVAL 12 WEEK))
        AND IF(DAYNAME(NOW()) != 'Sunday', week(order_date) != week(now()), week(order_date) <= week(now()))
        AND brain_sku in ('1400280','1177260')
    GROUP BY brain_sku, week(order_date)
) AS t
GROUP BY brain_sku;

逻辑说明：PERCENTILE_CONT(0.5)就是取百分位为50%的值，也就是中位数，偶数个值时会自动返回中间两个值的平均，符合通用的中位数计算规则。

方案2：创建自定义中位数函数（适合MySQL 5.7及更低版本，可复用） #

如果你的数据库版本较低没有窗口函数，可以直接创建一个通用的中位数聚合函数，后续可以像调用AVG()一样直接用：

• 第一步：创建中位数函数

DELIMITER //
CREATE FUNCTION median_func(expr FLOAT) RETURNS FLOAT
NO SQL
BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE mid INT DEFAULT 0;
    DECLARE res FLOAT DEFAULT 0;
    -- 先统计总数
    SELECT COUNT(*) INTO cnt FROM temp_median_data;
    SET mid = FLOOR(cnt / 2);
    -- 取中间位置的值
    IF cnt % 2 = 1 THEN
        SELECT expr INTO res FROM temp_median_data ORDER BY expr LIMIT mid, 1;
    ELSE
        SELECT AVG(expr) INTO res FROM (
            SELECT expr FROM temp_median_data ORDER BY expr LIMIT mid-1, 2
        ) AS t;
    END IF;
    RETURN res;
END //
DELIMITER ;

• 第二步：调用函数实现你的需求

-- 先处理12周数据存入临时表
CREATE TEMPORARY TABLE temp_median_data ENGINE=MEMORY
SELECT 
    brain_sku,
    week(order_date) as week_date,
    SUM(quantity_ordered) as weeklysales,
    CASE WHEN date(order_date) > date(DATE_SUB(NOW(), INTERVAL 4 WEEK)) THEN 1 ELSE 0 END AS is_4w
FROM sales
WHERE 
    date(order_date) > date(DATE_SUB(NOW(), INTERVAL 12 WEEK))
    AND IF(DAYNAME(NOW()) != 'Sunday', week(order_date) != week(now()), week(order_date) <= week(now()))
    AND brain_sku in ('1400280','1177260')
GROUP BY brain_sku, week(order_date);

-- 分别计算两个维度的中位数
SELECT 
    t.brain_sku,
    (SELECT median_func(weeklysales) FROM temp_median_data WHERE brain_sku = t.brain_sku AND is_4w = 1) AS Median_4Weeks,
    (SELECT median_func(weeklysales) FROM temp_median_data WHERE brain_sku = t.brain_sku) AS Median_12Weeks
FROM temp_median_data t
GROUP BY t.brain_sku;

-- 用完删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_median_data;

提示：如果没有创建自定义函数的权限，5.7版本也可以直接在分组子查询中对周销排序，通过LIMIT取中间值计算，逻辑和函数内的判断规则一致。

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