You need to enable JavaScript to run this app. 如何在MySQL中对内部计算生成的差值表求取中位数
优惠活动
大模型
产品
解决方案
定价
更多

如何在MySQL中对内部计算生成的差值表求取中位数

MySQL计算日期差值中位数报错的解决方案

错误原因

你的SQL存在3处语法/逻辑问题:

  • 最内层计算日期差值的DATEDIFF未设置别名,后续排序、取值时无法定位到该字段
  • 中间层子查询仅返回了行索引rowindex,没有携带差值字段,外层计算平均值时没有可计算的列
  • AVG(g)写法错误:g是子查询的表别名,不能直接传入聚合函数,需要指定具体的列名

修正后的SQL代码

SET @rowindex := -1;
 
SELECT
   AVG(g.diff_val) AS median_diff
FROM
   (
    SELECT 
        @rowindex:=@rowindex + 1 AS rowindex,
        difference.diff_val
    FROM(
        SELECT
            -- 给计算出的差值设置别名
            DATEDIFF(`orders`.`created_at`, `o_creation`.`created_at`) AS diff_val
        from `orders`
        left join `orders` AS `o_creation`
            on `o_creation`.`promise_honored_by_id` = `orders`.`order_id`
        WHERE (
            `orders`.`mean` = 'Chèque' AND 
            `orders`.`promise_honored_by_id` is NULL AND
            `orders`.`status` = 2
        )
    )  AS `difference`
    -- 按差值排序
    ORDER BY difference.diff_val
) AS g
WHERE
    g.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));

拓展:MySQL 8.0+ 更简洁的窗口函数写法

如果你的MySQL版本在8.0及以上,可以用PERCENTILE_CONT函数直接计算中位数,代码更易维护:

SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(`orders`.`created_at`, `o_creation`.`created_at`)) AS median_diff
from `orders`
left join `orders` AS `o_creation`
    on `o_creation`.`promise_honored_by_id` = `orders`.`order_id`
WHERE (
    `orders`.`mean` = 'Chèque' AND 
    `orders`.`promise_honored_by_id` is NULL AND
    `orders`.`status` = 2
);

内容的提问来源于stack exchange,提问作者Revolucion for Monica

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.17 04:15:04