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

    Проблема с предлагаемым решением (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 

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

    Для медиан в почти любом 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 простых шага:

    1. Замените «median_table» (2 вхождения) в указанном выше коде с именем вашей таблицы
    2. Замените «median_column» (3 вхождения) на имя столбца, в котором вы хотите найти медиану для
    3. Если у вас есть условие WHERE, замените «WHERE 1» (2 вхождения) на ваше место, где условие

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

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

    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 oc AS shifted_n, IF(@a mod oc=0, oc, @a) AS n, o.id, o.val, -- the number of elements for every id oc 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 

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

     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
     ) как t1 JOIN (
       SELECT grp_field, count (*) как total_rows
       FROM данных d
       GROUP BY grp_field
     ) при 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-any-percentile-with-single-mysql-query/

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

    Учитывает счетчик нечетных значений – в этом случае в средстве avg выделяется среднее значение.

     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 

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

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

    SELECT медиана (x) FROM t1

     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 

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

    У меня есть код ниже, который я нашел в 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 ); 

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

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

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

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

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

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

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

     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 char). Вы можете установить его так (для текущей сессии 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 имеет 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.

    обкрадывать

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

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

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

     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 ; 

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

    -Крис

    Снято с: http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

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

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

    Хорошая вещь здесь, что она работает также 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 

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

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

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

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

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

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

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

     select KEY_FIELD, AVG(VALUE_FIELD) MEDIAN_VALUE from ( select KEY_FIELD, VALUE_FIELD, RANKF , @rownumr := IF(@prevrowidr=KEY_FIELD,@rownumr+1,1) RANKR , @prevrowidr := KEY_FIELD FROM ( SELECT KEY_FIELD, VALUE_FIELD, RANKF FROM ( SELECT KEY_FIELD, VALUE_FIELD , @rownumf := IF(@prevrowidf=KEY_FIELD,@rownumf+1,1) RANKF , @prevrowidf := KEY_FIELD FROM ( SELECT KEY_FIELD, VALUE_FIELD FROM ( -- some expensive query ) B ORDER BY KEY_FIELD, VALUE_FIELD ) C , (SELECT @rownumf := 1) t_rownum , (SELECT @prevrowidf := '*') t_previd ) D ORDER BY KEY_FIELD, RANKF DESC ) E , (SELECT @rownumr := 1) t_rownum , (SELECT @prevrowidr := '*') t_previd ) F WHERE RANKF-RANKR BETWEEN -1 and 1 GROUP BY KEY_FIELD