求中位数时MySQL与PostgreSQL报错问题求助

一、MySQL中"BIGINT UNSIGNED value is out of range"报错解决 #

问题原因 #

row_number()返回的是无符号大整数（BIGINT UNSIGNED），当row_asc - row_desc得到负数时，无符号整数无法存储负数，直接触发溢出报错。

解决办法 #

把其中一个行号字段转换为有符号整数后再计算，或者换用无负数的差值写法：

写法一：类型转换避免负数溢出

with median as
(select lat_n, 
        row_number() over (order by lat_n asc) as row_asc,
        row_number() over (order by lat_n desc) as row_desc
 from station
)
select round(avg(lat_n),4)
from median
where abs(row_asc - cast(row_desc as signed)) <= 1;

写法二：用最值计算替代直接相减

with median as
(select lat_n, 
        row_number() over (order by lat_n asc) as row_asc,
        row_number() over (order by lat_n desc) as row_desc
 from station
)
select round(avg(lat_n),4)
from median
where greatest(row_asc, row_desc) - least(row_asc, row_desc) <= 1;

二、PostgreSQL中"function round(double precision, integer) does not exist"报错解决 #

问题原因 #

PostgreSQL内置的round函数仅支持round(numeric, integer)签名，但avg(lat_n)返回的是double precision类型，没有对应的双参数round函数，因此需要显式类型转换。

解决办法 #

将avg(lat_n)的结果转换为numeric类型后再调用round函数：

create table station (id serial primary key,
                      city varchar(50),
                      state varchar(50),
                      lat_n float,
                      long_w float);
                       
insert into station values 
(2,'ctc','odisha',31.546,33.213),
(3,'khorda','odisha',30.546,39.213),                  
(4,'puri','odisha',41.546,37.213);  

with median as
(select lat_n,
        row_number() over (order by lat_n) as row_asc,
        row_number() over(order by lat_n desc) as row_desc 
 from station
)
select round(avg(lat_n)::numeric,4)
from median
where abs(row_asc-row_desc) <=1;

也可以用cast(avg(lat_n) as numeric)替代::numeric，效果完全一致。

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