mysql求中位数

最新推荐文章于 2024-07-30 13:23:38 发布

转载最新推荐文章于 2024-07-30 13:23:38 发布 · 877 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/maohuidong/p/11008644.html

文章标签：

#数据库

本文详细介绍了如何在SQL中使用变量和子查询来计算数值的中位数，通过两个具体实例展示了当数据集大小为偶数和奇数时，如何正确地选取中间值进行平均，从而得到中位数。

实例1：

SET @ID = 0;
SELECT
AVG(loan_amount)
from
(
SELECT @ID:=@ID+1 as ID, loan_amount
FROM table_xxx ORDER BY loan_amount
) a where IF( ROUND(@ID/2,0)%2=0,ID in (ROUND(@ID/2,0),ROUND(@ID/2,0)/2 + 1), ID=ROUND(@ID/2,0) )

实例2：

SET @ID = 0;
SELECT
AVG(totallimit)
from
(
SELECT @ID:=@ID+1 as ID,t.*
FROM
(
SELECT pp.capitalid, pp.totallimit
FROM table1 pp
INNER JOIN table2 ppc ON pp.capitalid = ppc.capitalid
WHERE
pp.isduifu = 0
and pp.inserttimestamp <='2019-05-31 23:59:59'
and ppc.faxingjigou like '%R360%'
ORDER BY pp.totallimit
) t
) a where IF( ROUND(@ID/2,0)%2=0,ID in (ROUND(@ID/2,0),ROUND(@ID/2,0) + 1), ID=ROUND(@ID/2,0) );