文章目录
前言
从 STATION 查询北纬度 (LAT_N) 的中位数,并将您的答案四舍五入到小数点后4位.
中位数的定义是:如果数据量是奇数,则中位数是排序后位于中间的数;如果数据量是偶数,则中位数是排序后中间两个数的平均值。
STATION表说明如下:
一、题析
以MySQL为例:
给每一行加一列行号
@rownum:=@rownum+1 as `row_number`
判断奇偶数的情况
1、获取总数:
SELECT count(*) as total_rows
FROM station d
如果是奇数:(总行数+1)/2;偶数:总行数/2 和 总行数/2 +1
进行合并:
floor((total_rows+1)/2), floor((total_rows+2)/2) )
四舍五入到四位小数:
truncate(AVG(LAT_N),4)
二、题解
1.mysql
代码如下:
select truncate((SELECT avg(t1.LAT_N) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.LAT_N
FROM station d, (SELECT @rownum:=0) r
ORDER BY d.LAT_N
) as t1,
(
SELECT count(*) as total_rows
FROM station d
) as t2
WHERE t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )),4);
2.sqlserver
SELECT CAST( AVG(LAT_N) AS NUMERIC(20,4))
FROM
(
SELECT LAT_N,
ra = ROW_NUMBER() OVER (ORDER BY LAT_N, id),
rd = ROW_NUMBER() OVER (ORDER BY LAT_N DESC, id DESC)
FROM STATION
) AS x
WHERE ra BETWEEN rd - 1 AND rd + 1