Forma sencilla de calcular la mediana con MySQL

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 que el índice se utilice para filtrar y ordenar. Verifique 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).

Luego escógelo de la lista:

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

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

Jacob

Encontré que la solución aceptada no funcionaba en mi instalación de MySQL, devolviendo un conjunto vacío, pero esta consulta funcionó para mí en todas las situaciones en las 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 Jacobtaylor ni las de velcro devuelven resultados precisos para las versiones actuales de MySQL.

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

Entonces, aquí está la solución de velcro parcheada para manejar tanto el número impar como el par conjuntos:

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, siga estos 3 sencillos pasos:

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

Propongo un camino más rápido.

Obtener el recuento de filas:

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

Luego tome el valor medio en una subconsulta ordenada:

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

Probé esto con un conjunto de datos 5x10e6 de números aleatorios y encontrará la mediana en menos de 10 segundos.

Un comentario sobre esta página en la documentación 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 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 

Construyendo a partir de la respuesta de velcro, para aquellos de ustedes que tienen que hacer una mediana de algo que está agrupado por otro parámetro:

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;

La mayoría de las soluciones anteriores funcionan solo para un campo de la tabla, es posible que necesite obtener 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 reemplazar el "50" en el ejemplo anterior a cualquier percentil, es muy eficiente.

Solo asegúrate de tener suficiente memoria para el GROUP_CONCAT, puedes cambiarlo 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/

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

Puede usar la función definida por el usuario que se encuentra aquí.

Se ocupa de un recuento de valores impares - 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

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

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í.

Utilicé un enfoque de dos consultas:

• el primero en obtener conteo, min, max y avg
• segundo (declaración preparada) con un "LIMIT @count / 2, 1" y " ORDER BY .."cláusulas para obtener el valor medio

Estos están envueltos en una función defn, por lo que todos los valores se pueden devolver desde una llamada.

Si sus rangos 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 rasca cada vez.

Como solo necesitaba una solución mediana y percentil, hice una función simple y bastante flexible basada en los hallazgos en este hilo. Sé que estoy feliz si encuentro funciones" readymade " que son fáciles de incluir en mis proyectos, así que decidí compartir 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);
...

Este es mi camino . Por supuesto, usted podría 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;

Podrías evitar la variable @median_counter, si la sustituyes:

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 sub-consulta. Además, le permite obtener mediana para cada grupo en grupo-por consultas (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 big group_concat, debe establecer group_concat_max_len a un valor más alto (1024 caracteres por defecto). Puede configurarlo así (para la sesión sql actual):

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

Más infos para 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 utiliza una sola tabla intermedia y aprovecha la variable utilizada para la numeración de filas para obtener el recuento, en lugar de realizar una consulta adicional para calcularlo. También comienza el conteo para que la primera fila sea la fila 0 para permitir simplemente usar Piso y techo para seleccionar la fila 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));

Instale y utilice las siguientes funciones estadísticas de mysql: http://www.xarg.org/2012/07/statistical-functions-in-mysql /

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

SELECCIONE mediana (x) DE t1

De esta manera parece incluir tanto la cuenta par como la impar sin subconsulta.

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

A menudo, es posible que necesitemos calcular la Mediana no solo para toda la tabla, sino para los agregados con respecto a nuestro ID. En otras palabras, calcule la mediana para cada ID en nuestra tabla, donde cada ID tiene muchos registros. (buen rendimiento y funciona en muchos SQL + soluciona el problema de pares y probabilidades, más sobre el rendimiento de diferentes métodos de mediana 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;

Espero que ayude

Si MySQL tiene ROW_NUMBER, entonces la MEDIANA es (inspirarse en esta consulta de 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)
;

El IN se usa en caso de que tenga un número par de entradas.

Si desea encontrar la mediana por grupo, simplemente DIVIDA POR grupo en sus cláusulas OVER.

Rob

Después de leer todos los anteriores no coincidían con mi requisito real, así que implementé mi propio procedimiento que no necesita ningún procedimiento o declaraciones complicadas, solo yo GROUP_CONCAT todos los valores de la columna que quería obtener la MEDIANA y aplicando un DIV DE CONTEO POR 2 extraigo el valor de la mitad de la lista como lo hace la siguiente consulta:

(POS es el nombre de la columna que quiero obtener 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í de este sitio web.

Conociendo el recuento exacto de filas puede usar esta consulta:

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

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

Tengo una base de datos que contiene alrededor de 1 mil millones de filas que necesitamos para determinar la edad media en el conjunto. Ordenar mil millones de filas es difícil, pero si agrega los distintos valores que se pueden encontrar (las edades varían de 0 a 100), puede ordenar ESTA lista y usar algo de magia aritmética para encontrar cualquier percentil que desee de la siguiente manera:

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
;

Esta consulta depende de la base de datos que soporta las funciones de ventana (incluidas las FILAS SIN LÍMITE ANTERIORES), pero si no tiene que es una simple cuestión de unir aggData CTE consigo mismo y agregar todos los totales anteriores en la columna 'acumulado' que se utiliza para determinar qué valor contiene el precentile especificado. La muestra anterior calcula p10, p25 ,p50 (mediana), p75 y p90.

- Chris

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

Yo sugeriría otra manera, sin unir , pero trabajando con cadenas

No lo comprobé con tablas con datos grandes, pero las mesas pequeñas / medianas funciona muy bien.

Lo bueno aquí, que funciona también AGRUPANDO para que pueda devolver la mediana para varios elementos.

Aquí está el código de prueba para la prueba cuadro:

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 

Y el código para encontrar la mediana para cada grupo:

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

Salida:

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

En algunos casos la mediana se calcula de la siguiente manera :

La "mediana" es el valor "medio" en la lista de números cuando están ordenados por valor. Para conjuntos de cuentas pares, la mediana es el promedio de los dos valores medios. He creado un código simple para eso:

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

La median mediana devuelta sería el resultado requerido: -)

Medianas agrupadas por dimensión:

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;

Basado en la respuesta de @bob, esto generaliza la consulta para tener la capacidad de devolver varias medianas, agrupadas por algunos criterios.

Piense, por ejemplo, el precio medio de venta de automóviles usados en un lote de automóviles, agrupados por año-mes.

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;