用MySQL计算中位数最简单(希望不会太慢)的方法是什么?我已经使用AVG(x)来寻找平均值，但我很难找到一个简单的方法来计算中位数。现在，我将所有的行返回到PHP，进行排序，然后选择中间的行，但是肯定有一些简单的方法可以在一个MySQL查询中完成它。
示例数据:
id | val
--------
1 4
2 7
3 2
4 2
5 9
6 8
7 3
对val排序得到2 2 3 4 7 8 9，因此中位数应该是4，而SELECT AVG(val) == 5。
当前回答
通常，我们不仅需要为整个表计算Median，还需要为与ID相关的聚合计算Median。换句话说，计算表中每个ID的中位数，其中每个ID有许多记录。(良好的性能和工作在许多SQL +修复偶数和赔率的问题，更多关于不同的中值方法的性能https://sqlperformance.com/2012/08/t-sql-queries/median)
SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val,
COUNT(*) OVER (PARTITION BY our_id) AS cnt,
ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;
希望能有所帮助
其他回答
一个简单的方法来计算中位数在MySQL
set @ct := (select count(1) from station);
set @row := 0;
select avg(a.val) as median from
(select * from table order by val) a
where (select @row := @row + 1)
between @ct/2.0 and @ct/2.0 +1;
我使用了两个查询方法:
第一个得到count, min, Max和avg 第二个语句(预处理语句)使用“LIMIT @count/ 2,1”和“ORDER BY ..”子句来获得中值
它们被包装在函数defn中，因此可以从一次调用中返回所有值。
如果您的范围是静态的，并且数据不经常更改，那么预先计算/存储这些值并使用存储的值，而不是每次都从头查询，可能会更有效。
你也可以选择在存储过程中这样做:
DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
-- Set default parameters
IF where_clause IS NULL OR where_clause = '' THEN
SET where_clause = 1;
END IF;
-- Prepare statement
SET @sql = CONCAT(
"SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.", column_name, " AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.", column_name, "
FROM ", table_name," AS x, (SELECT @row:=0) AS r
WHERE ", where_clause, " ORDER BY x.", column_name, "
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM ", table_name, " x
WHERE ", where_clause, "
) AS t2
-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
WHERE t1.row >= t2.count/2
AND t1.row <= ((t2.count/2)+1)) AS t3
");
-- Execute statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
END//
DELIMITER ;
-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);
set @r = 0;
select
case when mod(c,2)=0 then round(sum(lat_N),4)
else round(sum(lat_N)/2,4)
end as Med
from
(select lat_N, @r := @r+1, @r as id from station order by lat_N) A
cross join
(select (count(1)+1)/2 as c from station) B
where id >= floor(c) and id <=ceil(c)
通常，我们不仅需要为整个表计算Median，还需要为与ID相关的聚合计算Median。换句话说，计算表中每个ID的中位数，其中每个ID有许多记录。(良好的性能和工作在许多SQL +修复偶数和赔率的问题，更多关于不同的中值方法的性能https://sqlperformance.com/2012/08/t-sql-queries/median)
SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val,
COUNT(*) OVER (PARTITION BY our_id) AS cnt,
ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;
希望能有所帮助
