我需要找到一列的中位数,并将答案四舍五入至小数点后四位。由于 SQL Server 没有 "MEDIAN()" 函数,因此我需要从列表的前50%中获取最小数字以及从底部50%中获取最大数字,然后除以2。
我尝试这样做:
SELECT(
(SELECT CAST(ROUND(MAX(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT LAT_N FROM STATION ORDER BY LAT_N ASC) AS Bottom1)
+ (SELECT CAST(ROUND(MIN(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT LAT_N FROM STATION ORDER BY LAT_N DESC) AS Top1)) / 2;
但结果为5.323200而不是5.3232。
我还尝试了这个:
SELECT(
(SELECT CAST(ROUND(MAX(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT (LAT_N / 2) AS LAT_N FROM STATION ORDER BY LAT_N ASC) AS Bottom1)
+ (SELECT CAST(ROUND(MIN(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT (LAT_N / 2) AS LAT_N FROM STATION ORDER BY LAT_N DESC) AS Top1));
这个答案似乎很接近,但通过将最小值和最大值都除以2,会导致丢失0.0001,从而使答案错误。
我该如何修正我正在尝试的方法中的任一种,以便从表格中获得中位数。还是有其他方法可以做到这一点?
MEDIAN
函数,但是它有PERCENTILE_CONT
函数。中位数可以用PERCENTILE_CONT(0.5)
来计算。 - Jeroen Mostert