Простой способ рассчитать медиану с MySQL
Какой самый простой (и, надеюсь, не слишком медленный) способ расчета медианы с 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,
50 ответов
В MariaDB / MySQL:
SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM data d, (SELECT @rownum:=0) r
WHERE d.val is NOT NULL
-- put some where clause here
ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );
Стив Коэн отмечает, что после первого прохода @rownum будет содержать общее количество строк. Это можно использовать для определения медианы, поэтому второй проход или соединение не требуется.
Также AVG(dd.val) а также dd.row_number IN(...) используется для правильного получения медианы при четном количестве записей. Обоснование:
SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3
Я просто нашел другой ответ онлайн в комментариях:
Для медиан практически в любом SQL:
SELECT x.val from data x, data y GROUP BY x.val HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
Убедитесь, что ваши столбцы хорошо проиндексированы, а индекс используется для фильтрации и сортировки. Проверьте с планами объяснения.
select count(*) from table --find the number of rows
Рассчитайте "средний" номер строки. Может быть, использовать: median_row = floor(count / 2),
Затем выберите его из списка:
select val from table order by val asc limit median_row,1
Это должно вернуть вам одну строку только с тем значением, которое вы хотите.
Иаков
Я обнаружил, что принятое решение не работает на моей установке MySQL, возвращая пустой набор, но этот запрос работал для меня во всех ситуациях, на которых я его тестировал:
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
К сожалению, ни ответы TheJacobTaylor, ни Velcro не дают точных результатов для текущих версий MySQL.
Ответ липучки сверху близок, но он не рассчитывается правильно для наборов результатов с четным числом строк. Медианы определяются как 1) среднее число на наборах с нечетными номерами, или 2) среднее двух средних чисел на наборах с нечетными числами.
Итак, вот решение Velcro, исправленное для обработки как нечетных, так и четных наборов чисел:
SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.median_column AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.median_column
FROM median_table AS x, (SELECT @row:=0) AS r
WHERE 1
-- put some where clause here
ORDER BY x.median_column
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM median_table x
WHERE 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;
Чтобы использовать это, выполните следующие 3 простых шага:
- Замените "median_table" (2 вхождения) в приведенном выше коде именем вашей таблицы
- Замените "median_column" (3 вхождения) на имя столбца, для которого вы хотите найти медиану
- Если у вас есть условие WHERE, замените "WHERE 1" (2 вхождения) на условие where.
Я предлагаю более быстрый способ.
Получить количество строк:
SELECT CEIL(COUNT(*)/2) FROM data;
Затем возьмите среднее значение в отсортированном подзапросе:
SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;
Я проверил это с набором случайных чисел 5x10e6, и он найдет медиану менее чем за 10 секунд.
Установите и используйте эти статистические функции mysql: http://www.xarg.org/2012/07/statistical-functions-in-mysql/
После этого вычислить медиану легко:
ВЫБЕРИТЕ медиану ( x) ОТ t1
Если MySQL имеет ROW_NUMBER, то MEDIAN (вдохновлен этим запросом SQL Server):
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)
;
IN используется, если у вас есть четное количество записей.
Если вы хотите найти медиану для каждой группы, просто укажите PARTITION BY group в ваших предложениях OVER.
обкрадывать
Комментарий к этой странице в документации MySQL содержит следующее предложение:
-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in http://en.wikipedia.org/wiki/Median
--
-- 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
Большинство приведенных выше решений работают только для одного поля таблицы, вам может потребоваться получить медиану (50-й процентиль) для многих полей в запросе.
Я использую это:
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;
Вы можете заменить "50" в приведенном выше примере на любой процентиль, это очень эффективно.
Просто убедитесь, что у вас достаточно памяти для GROUP_CONCAT, вы можете изменить это с помощью:
SET group_concat_max_len = 10485760; #10MB max length
Более подробная информация: http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/
У меня есть этот код ниже, который я нашел на HackerRank, и он довольно прост и работает в каждом конкретном случае.
SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) =
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );
Вы можете использовать пользовательскую функцию, найденную здесь.
Построение ответа на липучке, для тех из вас, кто должен сделать медиану из чего-то, что сгруппировано по другому параметру:
ВЫБЕРИТЕ grp_field, t1.val FROM (ВЫБЕРИТЕ grp_field, @rownum: = IF (@s = grp_field, @rownum + 1, 0) AS row_number,
@s: = IF (@s = grp_field, @s, grp_field) AS sec, d.val
ОТ данных d, (ВЫБЕРИТЕ @rownum:=0, @s:=0) r
ЗАКАЗАТЬ по grp_field, d.val) как t1 JOIN (ВЫБЕРИТЕ grp_field, count(*) как total_rows
ОТ данных d
GROUP BY grp_field) как t2
ON t1.grp_field = t2.grp_field
ГДЕ t1.row_number=floor(total_rows/2)+1;
Мой код, работающий без таблиц или дополнительных переменных:
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;
Один запрос для архивации идеальной медианы:
SELECT
COUNT(*) as total_rows,
IF(count(*)%2 = 1, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL), ROUND((CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) + CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL)) / 2)) as median,
AVG(val) as average
FROM
data
Заботится о количестве нечетных значений - в этом случае дает среднее из двух значений в середине.
SELECT AVG(val) FROM
( SELECT x.id, x.val from data x, data y
GROUP BY x.id, x.val
HAVING 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
При желании вы также можете сделать это в хранимой процедуре:
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);
Зная точное количество строк, вы можете использовать этот запрос:
SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>
куда <half> = ceiling(<size> / 2.0) - 1
Мое решение, представленное ниже, работает в одном запросе без создания таблицы, переменной или даже подзапроса. Кроме того, он позволяет вам получить медиану для каждой группы в групповых запросах (это то, что мне нужно!):
SELECT `columnA`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB
FROM `tableC`
-- some where clause if you want
GROUP BY `columnA`;
Это работает из-за умного использования group_concat и substring_index.
Но, чтобы разрешить большой group_concat, вы должны установить для group_concat_max_len более высокое значение (по умолчанию 1024 символа). Вы можете установить его так (для текущей сессии sql):
SET SESSION group_concat_max_len = 10000;
-- up to 4294967295 in 32-bits platform.
Дополнительная информация для group_concat_max_len: https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Простое решение для ORACLE:
SELECT ROUND(MEDIAN(Lat_N), 4) FROM Station;
Простое решение для MySQL:
select case MOD(count(lat_n),2)
when 1 then (select round(S.LAT_N,4) from station S where (select count(Lat_N) from station where Lat_N < S.LAT_N ) = (select count(Lat_N) from station where Lat_N > S.LAT_N))
else (select round(AVG(S.LAT_N),4) from station S where 1 = (select count(Lat_N) from station where Lat_N < S.LAT_N ) - (select count(Lat_N) from station where Lat_N > S.LAT_N))
end from station;
Объяснение
STATION - имя таблицы. LAT_N - это имя столбца, имеющее числовое значение
Предположим, что в таблице станций 101 запись (нечетное число) . Это означает, что медиана - это 51-я запись, если таблица отсортирована по возрастанию или убыванию.
В приведенном выше запросе для каждой таблицы S.LAT_N из S я создаю две таблицы. Один для количества значений LAT_N меньше S.LAT_N, а другой - для количества значений LAT_N больше, чем S.LAT_N. Позже я сравниваю эти две таблицы, и если они совпадают, я выбираю это значение S.LAT_N. Когда я проверяю 51-ю запись, 50 значений меньше 51-й записи и 50 записей больше 51-й записи. Как видите, в обеих таблицах по 50 записей. Итак, это наш ответ. Для каждой другой записи существует разное количество записей в двух таблицах, созданных для сравнения. Таким образом, условию удовлетворяет только 51-я запись.
Теперь предположим, что в таблице станций 100 записей (четное число) . Это означает, что медиана составляет среднее значение 50-й и 51-й записей, если таблица отсортирована по возрастанию или убыванию.
Как и странная логика, я создаю две таблицы. Один для количества значений LAT_N меньше S.LAT_N, а другой - для количества значений LAT_N больше, чем S.LAT_N. Позже я сравниваю эти две таблицы, и если их разница равна 1, я выбираю это значение S.LAT_N и нахожу среднее. Когда я проверяю 50-ю запись, есть 49 значений меньше 50-й записи и 51 запись больше 50-й записи. Как видите, разница в 1 запись в обеих таблицах. Итак, это (50-я запись) наша первая запись в среднем. Точно так же, когда я проверяю 51-ю запись, есть 50 значений меньше 51-й записи и 49 записей больше 51-й записи. Как видите, разница в 1 запись в обеих таблицах. Так что это (51-я запись) - наш второй результат в среднем.Для каждой другой записи существует разное количество записей в двух таблицах, созданных для сравнения. Таким образом, условию удовлетворяют только 50-я и 51-я записи.
Еще один рифф на ответ Велкроу, но он использует одну промежуточную таблицу и использует переменную, используемую для нумерации строк, чтобы получить счет, а не выполнять дополнительный запрос для его вычисления. Также начинается отсчет, чтобы первая строка была строкой 0, чтобы просто использовать Floor и Ceil для выбора медианной строки (строк).
SELECT Avg(tmp.val) as median_val
FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum
FROM data as inTab, (SELECT @rows := -1) as init
-- Replace with better where clause or delete
WHERE 2 > 1
ORDER BY inTab.val) as tmp
WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));
Я использую приведенную ниже таблицу для решения в MySQL:
CREATE TABLE transactions (
transaction_id int , user_id int , merchant_name varchar(255), transaction_date date , amount int
);
INSERT INTO transactions (transaction_id, user_id, merchant_name, transaction_date, amount)
VALUES (1, 1 ,'abc', '2015-08-17', 100),(2, 2, 'ced', '2015-2-17', 100),(3, 1, 'def', '2015-2-16', 121),
(4, 1 ,'ced', '2015-3-17', 110),(5, 1, 'ced', '2015-3-17', 150),(6, 2 ,'abc', '2015-4-17', 130),
(7, 3 ,'ced', '2015-12-17', 10),(8, 3 ,'abc', '2015-8-17', 100),(9, 2 ,'abc', '2015-12-17', 140),(10, 1,'abc', '2015-9-17', 100),
(11, 1 ,'abc', '2015-08-17', 121),(12, 2 ,'ced', '2015-12-23', 130),(13, 1 ,'def', '2015-12-23', 13),(3, 4, 'abc', '2015-2-16', 120),(3, 4, 'def', '2015-2-16', 121),(3, 4, 'ced', '2015-2-16', 121);
Вычисление медианы для столбца «сумма»:
WITH Numbered AS
(
SELECT *, COUNT(*) OVER () AS TotatRecords,
ROW_NUMBER() OVER (ORDER BY amount) AS RowNum
FROM transactions
)
SELECT Avg(amount)
FROM Numbered
WHERE RowNum IN ( FLOOR((TotatRecords+1)/2), FLOOR((TotatRecords+2)/2) )
;
TotalRecords = 16 и медиана = 120,5000
Этот запрос будет работать для обоих условий, т.е. четных и нечетных записей.
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
GROUP_CONCAT(field ORDER BY field),
',',
((
ROUND(
LENGTH(GROUP_CONCAT(field)) -
LENGTH(
REPLACE(
GROUP_CONCAT(field),
',',
''
)
)
) / 2) + 1
)),
',',
-1
)
FROM
table
Выше, кажется, работает для меня.
Если вам нужна медиана для каждой группы, используйте «PARTITION BY» в ROW_NUMBER() OVER (...)
WITH Numbered AS
(
SELECT groupingColumn,
val,
COUNT(*) OVER (partition by groupingColumn) AS Cnt,
ROW_NUMBER() OVER (partition by groupingColumn ORDER BY val) AS RowNum
FROM yourtable
)
SELECT groupingColumn, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
ORDER BY groupingColumn
;
Часто нам может потребоваться рассчитать медиану не только для всей таблицы, но и для агрегатов относительно нашего идентификатора. Другими словами, рассчитайте медиану для каждого идентификатора в нашей таблице, где каждый идентификатор имеет много записей. (хорошая производительность и работает во многих SQL + исправляет проблему четных и шансов, больше о производительности различных методов Median 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;
Надеюсь, поможет
create table med(id integer);
insert into med(id) values(1);
insert into med(id) values(2);
insert into med(id) values(3);
insert into med(id) values(4);
insert into med(id) values(5);
insert into med(id) values(6);
select (MIN(count)+MAX(count))/2 from
(select case when (select count(*) from
med A where A.id<B.id)=(select count(*)/2 from med) OR
(select count(*) from med A where A.id>B.id)=(select count(*)/2
from med) then cast(B.id as float)end as count from med B) C;
?column?
----------
3.5
(1 row)
ИЛИ ЖЕ
select cast(avg(id) as float) from
(select t1.id from med t1 JOIN med t2 on t1.id!= t2.id
group by t1.id having ABS(SUM(SIGN(t1.id-t2.id)))=1) A;
Основываясь на ответе @bob, это обобщает запрос, чтобы иметь возможность возвращать несколько медиан, сгруппированных по некоторым критериям.
Подумайте, например, о средней цене продажи подержанных автомобилей на автомобильном участке, сгруппированной по годам и месяцам.
SELECT
period,
AVG(middle_values) AS 'median'
FROM (
SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
FROM (
SELECT
@last_period:=@period AS 'last_period',
@period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,
x.sale_price
FROM listings AS x, (SELECT @row:=0) AS r
WHERE 1
-- where criteria goes here
ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
) AS t1
LEFT JOIN (
SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
FROM listings x
WHERE 1
-- same where criteria goes here
GROUP BY DATE_FORMAT(sale_date, '%Y%m')
) AS t2
ON t1.period = t2.period
) AS t3
WHERE
row_num >= (count/2)
AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;
Вы можете использовать оконную функцию row_number(), чтобы ответить на запрос, чтобы найти среду
select val
from (select val, row_number() over (order by val) as rownumber, x.cnt
from data, (select count(*) as cnt from data) x) abc
where rownumber=ceil(cnt/2);
Прочитав все предыдущие, они не соответствовали моему фактическому требованию, поэтому я реализовал свое собственное, которое не нуждается ни в какой процедуре или усложняет утверждения, просто я GROUP_CONCAT все значения из столбца, который я хотел получить в MEDIAN и применяя COUNT DIV BY 2 Я извлекаю значение из середины списка, как это делает следующий запрос:
(POS - это название столбца, который я хочу получить в медиане)
(query) SELECT
SUBSTRING_INDEX (
SUBSTRING_INDEX (
GROUP_CONCAT(pos ORDER BY CAST(pos AS SIGNED INTEGER) desc SEPARATOR ';')
, ';', COUNT(*)/2 )
, ';', -1 ) AS `pos_med`
FROM table_name
GROUP BY any_criterial
Я надеюсь, что это может быть полезно для кого-то, как многие другие комментарии были для меня с этого сайта.
Простой способ вычисления медианы в 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;
MySQL поддерживает оконные функции начиная с версии 8.0, вы можете использовать ROW_NUMBER или DENSE_RANK(НЕ используйтеRANK поскольку он присваивает один и тот же ранг тем же значениям, что и в спортивном рейтинге):
SELECT AVG(t1.val) AS median_val
FROM (SELECT val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM data) t1,
(SELECT COUNT(*) AS num_records FROM data) t2
WHERE t1.row_num IN
(FLOOR((t2.num_records + 1) / 2),
FLOOR((t2.num_records + 2) / 2));