Forma simple de calcular la mediana con MySQL

¿Cuál es la forma más simple (y afortunadamente no demasiado lenta) de calcular la mediana con MySQL? He usado AVG(x) para encontrar la media, pero me está costando encontrar una forma simple de calcular la mediana. Por ahora, estoy devolviendo todas las filas a PHP, haciendo una orderación, y luego escogiendo la fila del medio, pero seguramente debe haber alguna forma simple de hacerlo en una sola consulta MySQL.

Ejemplo de datos:

 id | val -------- 1 4 2 7 3 2 4 2 5 9 6 8 7 3 

Ordenar en val da 2 2 3 4 7 8 9 , por lo que la mediana debe ser 4 , frente a SELECT AVG(val) que == 5 .

    El problema con la solución propuesta (TheJacobTaylor) es el time de ejecución. Unirse a la table en sí mismo es lento como melaza para grandes sets de datos. Mi alternativa propuesta se ejecuta en mysql, tiene un time de ejecución impresionante, usa una instrucción ORDER BY explícita, por lo que no tiene que esperar que sus índices lo hayan orderado correctamente para dar un resultado correcto, y es fácil desenrollar la consulta para depurar.

     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] Agregó avg () alnetworkingedor de t1.val y row_number en (…) para producir correctamente una mediana cuando hay un número par de loggings. Razonamiento:

     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 

    Acabo de encontrar otra respuesta en línea en los comentarios :

    Para medianas en casi cualquier 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 

    Asegúrese de que sus columnas estén bien indexadas y el índice se use para filtrar y clasificar. Verificar con los planes de explicación.

     select count(*) from table --find the number of rows 

    Calcule el número de fila "mediana". Tal vez use: median_row = floor(count / 2) .

    A continuación, selecciónelo de la list:

     select val from table order by val asc limit median_row,1 

    Esto debería devolverle una fila con el valor que desea.

    Jacob

    Encontré que la solución aceptada no funcionaba en mi installation de MySQL, devolviendo un set vacío, pero esta consulta funcionó para mí en todas las situaciones en que la probé:

     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 

    Desafortunadamente, ni las respuestas de TheJacobTaylor ni las de Velcro arrojan resultados precisos para las versiones actuales de MySQL.

    La respuesta de Velcro de arriba está cerca, pero no calcula correctamente para sets de resultados con un número par de filas. Las medianas se definen como 1) el número del medio en los sets numerados impares, o 2) el promedio de los dos numbers del medio en los sets de numbers pares.

    Entonces, aquí está la solución de Velcro parchada para manejar sets de numbers pares e impares:

     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; 

    Para usar esto, sigue estos 3 sencillos pasos:

    1. Reemplaza "tabla_median" (2 apariciones) en el código anterior con el nombre de tu tabla
    2. Reemplace "median_column" (3 apariciones) con el nombre de columna para el que desea encontrar una mediana
    3. Si tiene una condición WHERE, reemplace "WHERE 1" (2 veces) con su condición where

    Propongo una manera más rápida.

    Obtenga el recuento de filas:

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

    Luego tome el valor medio en una subconsulta orderada:

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

    Probé esto con un set de datos 5x10e6 de numbers aleatorios y encontrará la mediana en less de 10 segundos.

    Un comentario en esta página en la documentation de MySQL tiene la siguiente sugerencia:

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

    Aprovechando la respuesta de Velcro, para aquellos que tienen que hacer una mediana de algo que está agrupado por otro parámetro:

     SELECCIONE grp_field, t1.val FROM (
        SELECCIONE 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 datos d, (SELECCIONAR @rownum: = 0, @s: = 0) r
       ORDEN POR grp_field, d.val
     ) como t1 JOIN (
       SELECCIONE grp_field, count (*) como total_rows
       DE datos d
       GRUPO POR grp_field
     ) como t2
     EN t1.grp_field = t2.grp_field
     DONDE t1.row_number = floor (total_rows / 2) +1;
    

    La mayoría de las soluciones anteriores solo funcionan para un campo de la tabla, es posible que necesite get la mediana (percentil 50) para muchos campos de la consulta.

    Yo uso esto:

     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; 

    Puede replace el "50" en el ejemplo anterior a cualquier percentil, es muy eficiente.

    Solo asegúrate de tener suficiente memory para GROUP_CONCAT, puedes cambiarla con:

     SET group_concat_max_len = 10485760; #10MB max length 

    Más detalles: http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/

    Puede usar la function definida por el usuario que se encuentra aquí .

    Se preocupa por un recuento de valores impar: da el promedio de los dos valores en el medio en ese caso.

     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 

    Instale y use estas funciones statistics de mysql: http://www.xarg.org/2012/07/statistical-functions-in-mysql/

    Después de eso, calcular la mediana es fácil:

    SELECCIONE la mediana (x) DESDE 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 

    Lo anterior parece funcionar para mí.

    Tengo este código a continuación que encontré en HackerRank y es bastante simple y funciona en todos y cada uno de los casos.

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

    Usé un enfoque de dos consultas:

    • el primero en contar, min, max y avg
    • segundo (statement preparada) con cláusulas "LIMIT @ count / 2, 1" y "ORDER BY …" para get el valor mediano

    Estos están envueltos en una definición de function, por lo que todos los valores pueden devolverse desde una llamada.

    Si sus ranges son estáticos y sus datos no cambian a menudo, podría ser más eficiente precomputar / almacenar estos valores y usar los valores almacenados en lugar de consultar desde cero cada vez.

    Mi código, eficiente sin tablas o variables adicionales:

     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; 

    Opcionalmente, también puede hacer esto en un procedimiento almacenado:

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

    como solo necesitaba una mediana y una solución percentil, hice una function simple y bastante flexible basada en los hallazgos de este hilo. Sé que también estoy contento si encuentro funciones "lists para usar" que son fáciles de include en mis proyectos, así que decidí compartirlas rápidamente:

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

    El uso es muy fácil, ejemplo de mi proyecto actual:

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

    Aquí está mi path. Por supuesto, podrías ponerlo en un procedimiento 🙂

     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; 

    Puede evitar la variable @median_counter , si la sustituye:

     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; 

    Mi solución presentada a continuación funciona en una sola consulta sin creación de tabla, variable o incluso subconsulta. Además, le permite get una mediana para cada grupo en consultas grupales (¡esto es lo que necesitaba!):

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

    Funciona debido a un uso inteligente de group_concat y substring_index.

    Pero, para permitir group_concat grande, debe establecer group_concat_max_len a un valor más alto (1024 char por defecto). Puede configurarlo así (para la session SQL actual):

     SET SESSION group_concat_max_len = 10000; -- up to 4294967295 in 32-bits platform. 

    Más información sobre group_concat_max_len: https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

    Otro riff en la respuesta de Velcrow, pero usa una sola tabla intermedia y aprovecha la variable utilizada para numerar filas para get el conteo, en lugar de realizar una consulta adicional para calcularlo. También comienza el conteo de modo que la primera fila sea la fila 0 para permitir simplemente el uso de Piso y Cielo para seleccionar la (s) fila (s) mediana (s).

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

    Si MySQL tiene ROW_NUMBER, entonces el MEDIAN es (inspírate con esta consulta de SQL Server):

     WITH Numbenetworking AS ( SELECT *, COUNT(*) OVER () AS Cnt, ROW_NUMBER() OVER (ORDER BY val) AS RowNum FROM yourtable ) SELECT id, val FROM Numbenetworking WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2) ; 

    El IN se utiliza en caso de que tenga un número par de inputs.

    Si quiere encontrar la mediana por grupo, simplemente participe PARTICIPA POR en sus cláusulas OVER.

    Robar

    Después de leer todos los anteriores, no coincidían con mis requisitos reales, así que implementé el mío que no necesita ningún procedimiento o complicar las declaraciones, simplemente GROUP_CONCAT todos los valores de la columna que quería get MEDIAN y aplicando un COUNT DIV POR 2, extraigo el valor del medio de la list como lo hace la siguiente consulta:

    (POS es el nombre de la columna que quiero get su mediana)

     (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 

    Espero que esto pueda ser útil para alguien en la forma en que muchos otros comentarios fueron para mí desde este website.

    Knowing exact row count you can use this query:

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

    Where <half> = ceiling(<size> / 2.0) - 1

    I have a database containing about 1 billion rows that we require to determine the median age in the set. Sorting a billion rows is hard, but if you aggregate the distinct values that can be found (ages range from 0 to 100), you can sort THIS list, and use some arithmetic magic to find any percentile you want as follows:

     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 ; 

    This query depends on your db supporting window functions (including ROWS UNBOUNDED PRECEDING) but if you do not have that it is a simple matter to join aggData CTE with itself and aggregate all prior totals into the 'accumulated' column which is used to determine which value contains the specified precentile. The above sample calcuates p10, p25, p50 (median), p75, and p90.

    -Chris

    Taken from: http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

    I would suggest another way, without join , but working with strings

    i did not checked it with tables with large data, but small/medium tables it works just fine.

    The good thing here, that it works also by GROUPING so it can return the median for several items.

    here is test code for test table:

     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 

    and the code for finding the median for each group:

     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 

    Output:

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

    In some cases median gets calculated as follows :

    The "median" is the "middle" value in the list of numbers when they are ordenetworking by value. For even count sets, median is average of the two middle values . I've created a simple code for that :

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

    The $median returned would be the requinetworking result 🙂

    Medians grouped by dimension:

     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; 

    This way seems include both even and odd count without subquery.

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

    Based on @bob's answer, this generalizes the query to have the ability to return multiple medians, grouped by some criteria.

    Think, eg, median sale price for used cars in a car lot, grouped by year-month.

     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; 

    These methods select from the same table twice. If the source data are coming from an expensive query, this is a way to avoid running it twice:

     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