sql-запрос для поиска медианного [дубликата]

Если кто-нибудь задается вопросом, как выполнить связанную проблему: «Выбрать столбец частичной строкой»

Использовать:

df.filter(like='hello')  # select columns which contain the word hello

И выбирать строки путем частичного совпадения строк, передать axis=0 для фильтрации:

# selects rows which contain the word hello in their index label
df.filter(like='hello', axis=0)  
171
задан Török Gábor 11 March 2010 в 17:22
поделиться

30 ответов

Проблема с предлагаемым решением (TheJacobTaylor) - это время выполнения. Присоединение стола к себе медленнее, как меласса для больших наборов данных. Мой предлагаемый альтернативный запуск в mysql, имеет потрясающее время выполнения, использует явный оператор ORDER BY, поэтому вам не нужно надеяться, что ваши индексы упорядочили его правильно, чтобы дать правильный результат, и легко развернуть запрос для отладки.

SELECT avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.val
  FROM data d,  (SELECT @rownum:=0) r
  WHERE 1
  -- put some where clause here
  ORDER BY d.val
) as t1, 
(
  SELECT count(*) as total_rows
  FROM data d
  WHERE 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) );

[edit] Добавлена ​​функция avg () вокруг t1.val и row_number в (...), чтобы правильно создать медиану, когда есть четное количество записей. Рассуждение:

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
193
ответ дан velcrow 22 August 2018 в 00:31
поделиться
  • 1
    этот очень очень намного быстрее (по крайней мере, на больших таблицах), чем принятое решение. – Hampus Brynolf 9 April 2012 в 09:46
  • 2
    Спасибо за Ваш ответ. Мне бы хотелось получить более полное объяснение, почему оно работает. Например, почему вам нужны предложения where. – Pascal Klein 4 March 2013 в 10:24
  • 3
    WHERE 1 - это не-op ... точка ... если вы добавите предложение WHERE к первому запросу, вам нужно добавить тождественное предложение WHERE к другому, вот и все. – velcrow 5 March 2013 в 22:09
  • 4
    любым способом сделать это, чтобы показать значения группы? например: место / медианное для этого места ... как выбрать место, медианное значение из таблицы ... в любом случае? благодаря – saulob 18 January 2014 в 06:45
  • 5
    @rowNum будет иметь «общее количество» в конце выполнения. Поэтому вы можете использовать это, если хотите избежать повторного «подсчета всех» (это было моим делом, потому что мой запрос был не таким простым) – Ahmed-Anas 15 October 2016 в 12:33
  • 6
    – Shanemeister 1 June 2017 в 16:40

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

3
ответ дан Alex Martelli 22 August 2018 в 00:31
поделиться
  • 1
    Это выглядит наиболее полезным, но я не хочу устанавливать нестабильное альфа-программное обеспечение, которое может привести к сбою mysql на моем производственном сервере :( – davr 20 August 2009 в 18:40
  • 2
    Поэтому изучите их источники для интересующей функции, исправьте их или измените по мере необходимости, и установите «свой собственный». стабильной и не альфа-версии после того, как вы сделали это - как это хуже, чем аналогичная настройка менее проверенных кодовых предложений, которые вы получаете на SO? -) – Alex Martelli 20 August 2009 в 18:42

Основываясь на ответе @ 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;
0
ответ дан Ariel Allon 22 August 2018 в 00:31
поделиться

, поскольку я просто нуждался в медианном И процентильном решении, я сделал простую и довольно гибкую функцию, основанную на результатах этой темы. Я знаю, что я счастлив, если найду «готовые» функции, которые легко включить в мои проекты, поэтому я решил быстро поделиться:

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);
...
1
ответ дан bezoo 22 August 2018 в 00:31
поделиться

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

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);
2
ответ дан bob 22 August 2018 в 00:31
поделиться
  • 1
    Спасибо за это! Пользователь должен знать, что недостающие значения (NULL) считаются значениями. чтобы избежать этой проблемы, добавьте 'x IS NOT NULL, где условие. – giordano 24 September 2013 в 08:32
  • 2
    @giordano В какую строку кода x IS NOT NULL следует добавить? – Przemyslaw Remin 13 May 2015 в 07:43
  • 3
    @PrzemyslawRemin Извините, я не был ясен в своем заявлении, и теперь я понял, что SP уже рассматривает случай отсутствия значений. SP следует вызывать таким образом: CALL median("table","x","x IS NOT NULL"). – giordano 14 May 2015 в 13:28

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

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

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

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

1
ответ дан btk 22 August 2018 в 00:31
поделиться

У меня есть база данных, содержащая около 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
;

Этот запрос зависит от ваших поддерживающих оконных функций (включая ROWS UNBOUNDED PRECEDING), но если у вас нет, то просто присоединить aggData CTE к себе и объединить все предыдущие итоговые значения в «накопленный» который используется для определения того, какое значение содержит указанный предусилитель. Вышеприведенный образец вычисляет p10, p25, p50 (медиана), p75 и p90.

-Chris

0
ответ дан Chris Knoll 22 August 2018 в 00:31
поделиться

К сожалению, ни ответы TheJacobTaylor, ни ответы velcro не дают точных результатов для текущих версий MySQL.

Ответ на Velcro сверху близок, но он не вычисляет правильно для наборов результатов с четным числом строк. Медианы определяются как 1) среднее число на нечетных нумерационных множествах, или 2) среднее из двух средних чисел на четных наборах чисел.

Итак, вот решение velcro, исправленное для обработки как нечетного, так и четного числа sets:

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 вхождения ) с вашим условием
14
ответ дан cmaher 22 August 2018 в 00:31
поделиться
  • 1
    Он уточнил свой ответ, чтобы исправить это. – Blazemonger 20 August 2014 в 17:39

Если 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 в ваших предложениях OVER.

Rob

0
ответ дан Community 22 August 2018 в 00:31
поделиться
  • 1
    Нет, нет ROW_NUMBER OVER, нет PARTITION BY, ничто из этого; это MySql, а не настоящий движок DB, такой как PostgreSQL, IBM DB2, MS SQL Server и т. д .;;). – Alex Martelli 20 August 2009 в 18:44

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

Надеюсь, что это поможет

1
ответ дан Danylo Zherebetskyy 22 August 2018 в 00:31
поделиться
  • 1
    Это лучшее решение. Однако для больших наборов данных он будет замедляться, потому что он пересчитывает каждый элемент в каждом наборе. Чтобы сделать его более быстрым, введите «COUNT (*)». для разделения подзапроса. – Slava Murygin 28 February 2018 в 17:42

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

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 -системы-variables.html # sysvar_group_concat_max_len

1
ответ дан didier2l 22 August 2018 в 00:31
поделиться

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

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
3
ответ дан Franz K. 22 August 2018 в 00:31
поделиться

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

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

$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 был бы требуемым результатом: -)

0
ответ дан jitendrapurohit 22 August 2018 в 00:31
поделиться

Установите и используйте статистические функции mysql: http://www.xarg.org/2012/07/statistical-functions-in-mysql/

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

ВЫБОР медианной (x) FROM t1

1
ответ дан Leonardo Nicolas 22 August 2018 в 00:31
поделиться

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

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

0
ответ дан Louis van Tonder 22 August 2018 в 00:31
поделиться

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

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;
2
ответ дан Michael Myers 22 August 2018 в 00:31
поделиться
  • 1
    Это приведет к сбою любого значительного объема данных, потому что GROUP_CONCAT ограничено 1023 символами, даже если используется внутри другой функции. – Rob Van Dam 8 June 2013 в 00:43

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

I предложил бы другой способ, не присоединившись, но работая со строками

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

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

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

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
0
ответ дан mr.baby123 22 August 2018 в 00:31
поделиться
  • 1
    Разве вы не считаете, что медиана «{22,26}» должна быть 24? – Noman Dilawar 28 April 2017 в 10:40

Большинство решений выше работают только для одного поля таблицы, вам может понадобиться получить средний (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 -любой-процентиль-с-одной MySQL-запрос /

4
ответ дан Nico 22 August 2018 в 00:31
поделиться
  • 1
    Помните: для четного числа значений он принимает более высокие значения из двух средних значений. Для коэффициентов число значений оно принимает следующее более высокое значение после медианного. – giordano 24 September 2013 в 07:43
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

Выше, похоже, работает для меня.

2
ответ дан Nochum Sossonko 22 August 2018 в 00:31
поделиться
  • 1
    Он не возвращает правильную медиану для четного числа значений. Например, медиана {98,102,102,98} равна 100, но ваш код дает 102. Он работал отлично для нечетных чисел. – Noman Dilawar 28 April 2017 в 10:36

У меня есть код ниже, который я нашел в 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 );
3
ответ дан Paul Roub 22 August 2018 в 00:31
поделиться
  • 1
    Я считаю, что это работает только с таблицей, в которой количество записей нечетно. Для четного количества записей это может вызвать проблемы. – Y. Chang 14 August 2018 в 20:22

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

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;
1
ответ дан pucawo 22 August 2018 в 00:31
поделиться

Я предлагаю более быстрый способ.

Получить число строк:

SELECT CEIL(COUNT(*)/2) FROM data;

Затем взять среднее значение в отсортированном подзапросе:

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

Я проверил это с набором данных 5x10e6 случайных чисел, и он найдет медиану менее чем за 10 секунд.

9
ответ дан Reggie Edwards 22 August 2018 в 00:31
поделиться
  • 1
    Почему бы нет: SELECT val FROM data ORDER BY val limit @middlevalue, 1 – Bryan 13 July 2011 в 01:57
  • 2
    Как вы вытаскиваете переменный вывод вашего первого блока кода во второй блок кода? – Trip 21 December 2012 в 00:55
  • 3
    Как в, откуда берется @middlevalue? – Trip 21 December 2012 в 01:01
  • 4
    @Bryan - Я согласен с тобой, это имеет для меня гораздо больше смысла. Вы когда-нибудь находили причину не делать этого? – Shane N 24 February 2014 в 21:45
  • 5
    Это не работает, поскольку переменная не может использоваться в предложении limit. – codepk 28 June 2017 в 05:41

Комментарий к этой странице в документации 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 
7
ответ дан Sebastian Paaske Tørholm 22 August 2018 в 00:31
поделиться
  • 1
    IMHO, это, безусловно, лучше всего для ситуаций, когда вам нужна медиана из сложного подмножества (мне нужно было вычислить отдельные медианы большого числа подмножеств данных) – mblackwell8 19 March 2012 в 22:57
  • 2
    Прекрасно работает для меня. 5.6.14. Сервер сообщества MySQL. Таблица с записями 11M (около 20 Гб на диске) имеет два не первичных индекса (model_id, price). В таблице (после фильтрации) у нас есть 500K записей для расчета медианного значения. В результате у нас есть 30K записей (model_id, median_price). Длительность запроса составляет 1,5-2 секунды. Скорость скоро для меня. – Mikl 3 July 2014 в 18:57

Еще один рифф на ответ 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));
1
ответ дан Steve Cohen 22 August 2018 в 00:31
поделиться

Я просто нашел еще один ответ онлайн в комментариях :

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

Это должно вернуть вам одну строку с нужным значением.

Jacob

50
ответ дан Török Gábor 22 August 2018 в 00:31
поделиться
  • 1
    этот ответ больше не подходит для последних версий mysql – Rob 13 June 2012 в 14:52
  • 2
    @rob вы можете помочь в редактировании, пожалуйста? Или я должен просто поклониться липучке? (не совсем уверен, как отложить до другого решения) Спасибо, Джейкоб – TheJacobTaylor 19 June 2012 в 00:50
  • 3
    Обратите внимание, что он выполняет «кросс-соединение», что очень мало для больших таблиц. – Rick James 1 February 2016 в 01:53
  • 4
    Этот ответ ничего не возвращает для even количества строк. – kuttumiah 16 August 2018 в 19:51

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

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;

4
ответ дан Trip 22 August 2018 в 00:31
поделиться
  • 1
    не работает для меня :(, не возвращать значения – saulob 18 January 2014 в 06:44

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

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;
0
ответ дан Vladimir_M 22 August 2018 в 00:31
поделиться

Этот способ кажется как четным, так и нечетным числом без подзапроса.

SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0
1
ответ дан yuhanluo 22 August 2018 в 00:31
поделиться

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

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

Где <half> = ceiling(<size> / 2.0) - 1

0
ответ дан ZhekaKozlov 22 August 2018 в 00:31
поделиться

Я нашел, что принятое решение не работало над моей установкой 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
26
ответ дан zookatron 22 August 2018 в 00:31
поделиться
  • 1
    абсолютно правильно, работает отлично и очень быстро на моих проиндексированных таблицах – Rob 13 June 2012 в 14:49
  • 2
    это, по-видимому, самое быстрое решение на mysql из всех ответов здесь, 200 мс, всего за миллион записей в таблице – Rob 13 June 2012 в 15:01
  • 3
    Я сторонний разработчик с базовыми знаниями MySQL, и у меня проблема с синтаксисом. После «FROM» я видел только одну переменную, имя таблицы. Выбирает ли эта формула данные из двух таблиц, и если да, то каким образом была бы формула, если требуется только медиана одного столбца данных одной таблицы? – Frank Conijn 30 April 2013 в 14:27
  • 4
    @FrankConijn: он выбирает из одной таблицы дважды. Имя таблицы - data, и оно используется с двумя именами, x и y. – Brian 26 June 2014 в 22:24
  • 5
    просто говоря, что я остановил мой mysqld с этим точным запросом на таблице с 33k строк ... – Xenonite 4 February 2016 в 10:40
Другие вопросы по тегам:

Похожие вопросы: