如何在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
相关产品推荐
相关产品推荐

