MySQL计算lat_n中位数遇1248错误及结果不符合要求问题咨询

1. 报错问题解决 #

你遇到的ERROR 1248 (42000)错误确实是派生表未加别名导致的，你编辑1中给排序后的子查询加上as ordered的写法已经正确解决了这个报错。

2. 计算结果错误的原因 #

你的代码逻辑存在两处问题：

• MySQL部分版本会忽略派生表内部的order by排序规则，导致后续行号计数是基于乱序的表数据，自然得到的中位数结果错误
• 行号范围的边界逻辑有缺陷，遇到整数除法场景会出错，比如总行数为奇数5时，整数除法得到ct/2=2，范围取2到3行的平均值，和正确的第3行结果不符

修正后的MySQL变量版代码 #

set @ct := (select count(1) from station);
set @row_id := 0;

select avg(lat_n) as median
from (
    select lat_n, @row_id := @row_id + 1 as row_id
    from station 
    order by lat_n
) as ordered
where row_id between floor((@ct+1)/2) and ceil((@ct+1)/2);

直接在子查询内完成行号计数，保证行号和排序结果绑定，同时调整边界逻辑适配奇偶两种总行数场景。

3. PostgreSQL/CTE 场景的最优实现 #

你习惯用CTE的话，除了修正边界逻辑的自写行号方案，还可以直接用PostgreSQL内置的中位数计算函数，代码更简洁：

方案1：CTE行号版（兼容MySQL8.0+/PostgreSQL） #

with ordered as(
    select lat_n, 
    row_number() over(order by lat_n) as row_id,
    count(*) over() as ct
    from station
)
select avg(lat_n) as median
from ordered
where row_id between floor((ct+1)/2) and ceil((ct+1)/2);

方案2：PostgreSQL内置函数版 #

-- 连续百分位，偶数行时返回中间两数的平均
select percentile_cont(0.5) within group (order by lat_n) as median from station;

-- 离散百分位，偶数行时返回中间位置靠后的那个数
select percentile_disc(0.5) within group (order by lat_n) as median from station;

可以根据题目要求的中位数计算规则选对应的函数。

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