Cómo calcular la mediana en MySQL

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.

About these ads

2 comentarios to “Cómo calcular la mediana en MySQL”

  1. Oscar Canon Says:

    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. Lu Ponce Says:

    GENIAL EL APORTE

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s


Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

A %d blogueros les gusta esto: