Recientemente me he encontrado con el problema de obtener la mediana en un conjunto de datos de una tabla en MySQL. MySQL no provee una función para obtener este estadístico así que me puse a buscar las soluciones propuestas por distintos usuarios en internet y no encontré ninguna que me convenció. Así que aquí os expongo mi solución:
Si tenemos una tabla DATOS, de la que queremos obtener la mediana de los valores de la columna llamada “valor”, tendremos que utilizar esta consulta:
select a.rownum, a.id, a.valor from (
select @rownum:=@rownum+1 AS rownum,id, valor
from ( select @rownum:=0) r, DATOS
order by valor) a,
(select round(count(*)/2) medio from DATOS) b
where a.rownum= b.medio;
Como veis tenemos dos subconsultas: en la primera obtenemos todos los valores ordenados y con una columna llamada rownum que tendrá el orden de cada valor, o sea el valor más pequeño el 1, el siguiente el 2 y así sucesivamente. La segunda subconsulta obtendrá el número de registros de la tabla dividido entre dos, o sea la posición dónde se encuentra la mediana. Con estas dos subconsultas obtenemos la mediana al cruzarlas cogiendo de la primera subconsulta el valor cuyo rownum sea la posición dónde se encuentra la mediana obtenido en la segunda subconsulta.
Esto se puede complicar un poco más, ya que puede ser que en nuestra tabla DATOS tengamos una columna “grupo_id” por la cual queramos agrupar nuestros valores, y calcular la mediana para cada grupo. Para ello tenemos la siguiente subconsulta:
select b.grupo_id,a.rownum, a.id, a.valor from(
select if(@grupo=grupo_id,@rownum:=@rownum+1,@rownum:=0) AS rownum, @grupo:=grupo_id as grupo,id, valor
from DATOS order by grupo_id, valor ) a,
(select grupo_id,round(count(*)/2) medio
from DATOS group by grupo_id) b
where a.rownum= b.medio and a.grupo=b.grupo_id;
Como se puede observar seguimos teniendo las dos subconsultas, pero en esta ocasión se obtienen los resultados de las subconsultas agrupados por la variable “group_id”. Entonces se obtiene en la primera subconsulta todos los valores pero ordenados por grupo y el valor y la nueva columna rownum sigue siendo la secuencia que marca el orden del valor, pero esta vez dentro del grupo. Es decir el valor más pequeño del grupo X, Y o el que sea, será 1, el siguiente 2 y así sucesivamente.
Otras soluciones al problema las podéis encontrar en los comentarios de la página de MySQL http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#c10384.
Sendero Digital 