Простой способ вычисления медианы с помощью MySQL

Я нашел другой ответ в интернете в комментариях:

для медиан практически в любом 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.

ответ Velcro сверху близок, но он не вычисляется правильно для результирующих наборов с четным числом строк. Медианы определяются как 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 простых шага:

1. замените "median_table" (2 вхождения) в приведенном выше коде именем вашей таблицы
2. заменить "median_column" (3 вхождения) с именем столбца, который вы хотели бы найти медиану для
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 есть следующее предложение:

-- (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 

построение ответа velcro, для тех из вас, кто должен сделать медиану от чего-то, что сгруппировано по другому параметру:

SELECT grp_field, t1.val FROM (
   SELECT 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
  FROM data d,  (SELECT @rownum:=0, @s:=0) r
  ORDER BY grp_field, d.val
) as t1 JOIN (
  SELECT grp_field, count(*) as total_rows
  FROM data d
  GROUP BY grp_field
) as t2
ON t1.grp_field = t2.grp_field
WHERE t1.row_number=floor(total_rows/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 );

вы можете использовать пользовательскую функцию, что нашел здесь.

заботится о нечетном количестве значений-дает среднее значение двух значений в середине в этом случае.

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

мой код, эффективный без таблиц или дополнительных переменных:

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;

необязательно, вы также можете сделать это в хранимой процедуре:

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 
    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

выше, кажется, работает для меня.

Я использовал два подхода запрос:

• первый, чтобы получить count, min, max и avg
• второй (подготовленный оператор) с "LIMIT @count/2, 1" и "ORDER BY .."предложения, чтобы получить медианное значение

они завернуты в функцию defn, поэтому все значения могут быть возвращены из одного вызова.

Если ваши диапазоны статичны и ваши данные не меняются часто, было бы более эффективно предварительно вычислить / сохранить эти значения и использовать сохраненные значения вместо запроса с нуля каждый раз.

function mysql_percentile($table, $column, $where, $percentile = 0.5) {

    $sql = "
            SELECT `t1`.`".$column."` as `percentile` FROM (
            SELECT @rownum:=@rownum+1 as `row_number`, `d`.`".$column."`
              FROM `".$table."` `d`,  (SELECT @rownum:=0) `r`
              ".$where."
              ORDER BY `d`.`".$column."`
            ) as `t1`, 
            (
              SELECT count(*) as `total_rows`
              FROM `".$table."` `d`
              ".$where."
            ) as `t2`
            WHERE 1
            AND `t1`.`row_number`=floor(`total_rows` * ".$percentile.")+1;
        ";

    $result = sql($sql, 1);

    if (!empty($result)) {
        return $result['percentile'];       
    } else {
        return 0;
    }

}

использование очень просто, пример из моего текущего проекта:

...
$table = DBPRE."zip_".$slug;
$column = 'seconds';
$where = "WHERE `reached` = '1' AND `time` >= '".$start_time."'";

    $reaching['median'] = mysql_percentile($table, $column, $where, 0.5);
    $reaching['percentile25'] = mysql_percentile($table, $column, $where, 0.25);
    $reaching['percentile75'] = mysql_percentile($table, $column, $where, 0.75);
...

вот мой путь . Конечно, вы могли бы поместить его в процедуру : -)

SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);

SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

вы могли бы избежать переменная @median_counter, Если вы его подставляете:

SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',
                      (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),
                      ', 1'
                    );

PREPARE median FROM @median;

EXECUTE median;

мое решение, представленное ниже, работает только в одном запросе без создания таблицы, переменной или даже подзапроса. Кроме того, он позволяет получить медиану для каждой группы в групповых запросах (это то, что мне нужно !):

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#sysvar_group_concat_max_len

еще один рифф на ответ Velcrow, но использует одну промежуточную таблицу и использует переменную, используемую для нумерации строк, чтобы получить счетчик, а не выполнять дополнительный запрос для его вычисления. Также начинается подсчет так, чтобы первая строка была строкой 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:http://www.xarg.org/2012/07/statistical-functions-in-mysql/

после этого, вычислить медиану легко:

выберите медиану (x) из t1

этот способ, кажется, включает в себя как четное, так и нечетное количество без подзапроса.

SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0

часто нам может потребоваться вычислить медиану не только для всей таблицы, но и для агрегатов относительно нашего идентификатора. Другими словами, вычислите медиану для каждого идентификатора в нашей таблице, где каждый идентификатор имеет много записей. (хорошая производительность и работает во многих 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 имеет ROW_NUMBER, то медиана (вдохновляйтесь этим запросом 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 используется в случае, если у вас есть четное количество записей.

Если вы хотите найти медиану для каждой группы, то просто разделите по группам в ваших предложениях OVER.

Роб

после прочтения всех предыдущих они не совпадали с моим фактическим требованием, поэтому я реализовал свой собственный, который не нуждается в какой-либо процедуре или усложняет утверждения, просто я GROUP_CONCAT все значения из столбца я хотел получить медиану и применяя счетчик DIV на 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

Я надеюсь, что это может быть полезно для кто-то в пути многие другие комментарии были для меня с этого сайта.

зная точное количество строк, вы можете использовать этот запрос:

SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>

здесь <half> = ceiling(<size> / 2.0) - 1

У меня есть база данных, содержащая около 1 миллиарда строк, которые нам нужны для определения среднего возраста в наборе. Сортировка миллиарда строк трудна, но если вы объедините различные значения, которые можно найти (возраст от 0 до 100), вы можете отсортировать этот список и использовать некоторую арифметическую магию, чтобы найти любой процентиль, который вы хотите следующим образом:

with rawData(count_value) as
(
    select p.YEAR_OF_BIRTH
        from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select avg(1.0 * count_value) as avg_value,
    stdev(count_value) as stdev_value,
    min(count_value) as min_value,
    max(count_value) as max_value,
    count(*) as total
  from rawData
),
aggData (count_value, total, accumulated) as
(
  select count_value, 
    count(*) as total, 
        SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
  FROM rawData
  group by count_value
)
select o.total as count_value,
  o.min_value,
    o.max_value,
    o.avg_value,
    o.stdev_value,
    MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
    MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
    MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
    MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
    MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;

этот запрос зависит от вашей БД, поддерживающей оконные функции (включая строки, не ограниченные предыдущими), но если у вас их нет, это просто чтобы объединить aggData CTE с самим собой и объединить все предыдущие итоги в столбец "накопленный", который используется для определения того, какое значение содержит указанный прецентиль. Приведенная выше выборка вычисляет p10, p25, P50 (медиана), p75 и p90.

-Крис

взяты из: http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

Я бы предложил другой способ, без join, но работа с строки

Я не проверял его с таблицами с большим объемом данных, но малые / средние таблицы он работает просто отлично.

хорошая вещь здесь, что он работает также по группировке так что он может вернуть медиану для нескольких предметы.

вот тестовый код для тестовой таблицы:

DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26 

и код для нахождения медианы для каждой группы:

SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

выход:

grp | the_median| all_vals_for_debug
bike| 22        | 22,26
book| 4         | 2,2,3,4,7,8,9
note| 11        | 11

в некоторых случаях медиана вычисляется следующим образом:

"медианный" - это "среднее" значение в списке чисел, когда они упорядочены по значению. Для четных множеств подсчета,медиана-это среднее из двух средних значений. Я создал простой код :

$midValue = 0;
$rowCount = "SELECT count(*) as count {$from} {$where}";

$even = FALSE;
$offset = 1;
$medianRow = floor($rowCount / 2);
if ($rowCount % 2 == 0 && !empty($medianRow)) {
  $even = TRUE;
  $offset++;
  $medianRow--;
}

$medianValue = "SELECT column as median 
               {$fromClause} {$whereClause} 
               ORDER BY median 
               LIMIT {$medianRow},{$offset}";

$medianValDAO = db_query($medianValue);
while ($medianValDAO->fetch()) {
  if ($even) {
    $midValue = $midValue + $medianValDAO->median;
  }
  else {
    $median = $medianValDAO->median;
  }
}
if ($even) {
  $median = $midValue / 2;
}
return $median;

возвращенный $ median будет требуемым результатом : -)

медианы сгруппированы по размерности:

SELECT your_dimension, avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 AS `row_number`,
   IF(@dim <> d.your_dimension, @rownum := 0, NULL),
   @dim := d.your_dimension AS your_dimension,
   d.val
   FROM data d,  (SELECT @rownum:=0) r, (SELECT @dim := 'something_unreal') d
  WHERE 1
  -- put some where clause here
  ORDER BY d.your_dimension, d.val
) as t1
INNER JOIN  
(
  SELECT d.your_dimension,
    count(*) as total_rows
  FROM data d
  WHERE 1
  -- put same where clause here
  GROUP BY d.your_dimension
) as t2 USING(your_dimension)
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )

GROUP BY your_dimension;

основываясь на ответе @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;