SELECT avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.valFROM data d, (SELECT @rownum:=0) rWHERE 1-- put some where clause hereORDER BY d.val
) as t1,
(SELECT count(*) as total_rowsFROM data dWHERE 1-- put same where clause here
) as t2
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) );
SELECT floor((3+1)/2),floor((3+2)/2);#total_rows is 3, so avg row_numbers 2 and 2
SELECT floor((4+1)/2),floor((4+2)/2);#total_rows is 4, so avg row_numbers 2 and 3
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1
4.?一此頁面上的MySQL有以下建議：
-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in CodeGo.net
--
-- by Peter Hlavac
-- 06.11.2008
--
-- Example Table:
DROP table if exists table_median;
CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));
COMMIT;INSERT INTO table_median (id, val) VALUES
(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),
(2, 4),
(3, 5), (3, 2),
(4, 5), (4, 12), (4, 1), (4, 7);
-- Calculating the MEDIAN
SELECT @a := 0;
SELECT
id,
AVG(val) AS MEDIAN
FROM (
SELECT
id,
val
FROM (
SELECT
-- Create an index n for every id
@a := (@a + 1) mod o.c AS shifted_n,
IF(@a mod o.c=0, o.c, @a) AS n,
o.id,
o.val,
-- the number of elements for every id
o.c
FROM (
SELECT
t_o.id,
val,
c
FROM
table_median t_o INNER JOIN
(SELECT
id,
COUNT(1) AS c
FROM
table_median
GROUP BY
id
) t2
ON (t2.id = t_o.id)
ORDER BY
t_o.id,val
) o
) a
WHERE
IF(
-- if there is an even number of elements
-- take the lower and the upper median
-- and use AVG(lower,upper)
c MOD 2 = 0,
n = c DIV 2 OR n = (c DIV 2)+1,
-- if its an odd number of elements
-- take the first if its only one element
-- or take the one in the middle
IF(
c = 1,
n = 1,
n = c DIV 2 + 1
)
)
) a
GROUP BY
id;
-- Explanation:
-- The Statement creates a helper table like
--
-- n id val count
-- ----------------
-- 1, 1, 1, 7
-- 2, 1, 3, 7
-- 3, 1, 4, 7
-- 4, 1, 5, 7
-- 5, 1, 6, 7
-- 6, 1, 7, 7
-- 7, 1, 8, 7
--
-- 1, 2, 4, 1
-- 1, 3, 2, 2
-- 2, 3, 5, 2
--
-- 1, 4, 1, 4
-- 2, 4, 5, 4
-- 3, 4, 7, 4
-- 4, 4, 12, 4-- from there we can select the n-th element on the position: count div 2 + 1
5.?你函數 CodeGo.net，在這里找到。? 6.?我提出了一個更快的方法。 獲取的行數：SELECT CEIL(COUNT(*)/2) FROM data;然后取中間值在排序子查詢：SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;我測試了這個隨機數的5×10e6個數據集，它會發現在10秒以內。? 7.?建立銷magic貼的回答，對于那些你不必做了，是通過另一個分組 選擇grp_field，t1.val FROM（ 選擇grp_field，@ROWNUM：=IF（@S=grp_field，@ROWNUM +1，0）ASrow_number， @S：=IF（@S=grp_field，@S，grp_field）為二段，d.val 從數據D，（SELECT ROWNUM@：=0，@S：=0）R ORDER BY grp_field，d.val ）為T1 JOIN（ 選擇grp_field，COUNT（*）作為total_rows 從數據D GROUP BY grp_field ）為T2 開t1.grp_field=t2.grp_field WHERE t1.row_number=地板（total_rows / 2）+1;? 8.?不幸的是，TheJacobTaylor的也不是magic貼的答案返回準確的結果為MySQL的最新版本。 從上面magic貼的答案是接近，但它不能正確計算結果集與偶數行。中值的定義為要么1）在偶數套的中間數的奇數編號的集合，或中間的兩個數的2）的平均值。 所以，這里的補丁來處理奇數和偶數設置magic貼的解決方案：
SELECT AVG(middle_values) AS 'median' FROM (SELECT t1.median_column AS 'middle_values' FROM(SELECT @row:=@row+1 as `row`, x.median_columnFROM median_table AS x, (SELECT @row:=0) AS rWHERE 1-- put some where clause hereORDER BY x.median_column) AS t1,(SELECT COUNT(*) as 'count'FROM median_table xWHERE 1-- put same where clause here) 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;
SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM table_name;
SET group_concat_max_len = 10485760; #10MB max length
更多詳細信息：? 10.?需要關心的奇數值計數-給出了在這種情況下，兩個中間值的平均值。
SELECT AVG(val) FROM( SELECT x.id, x.val from data x, data yGROUP BY x.id, x.valHAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))) sq
WITH Numbered AS
(
SELECT *, COUNT(*) OVER () AS Cnt,ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;
SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2
as median
FROM table;
15.?或者，你也可以在存儲這樣做
DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN-- Set default parametersIF where_clause IS NULL OR where_clause = '' THENSET where_clause = 1;END IF;-- Prepare statementSET @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 rWHERE ", where_clause, " ORDER BY x.", column_name, ") AS t1,(SELECT COUNT(*) as 'count'FROM ", table_name, " xWHERE ", 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/2AND t1.row <= ((t2.count/2)+1)) AS t3");-- Execute statementPREPARE stmt FROM @sql;EXECUTE stmt;
END//
DELIMITER ;-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);
