moda - sql mean function

Forma simple de calcular la mediana con MySQL

Question

¿Cuál es la forma más simple (y afortunadamente no demasiado lenta) de calcular la mediana con MySQL? Utilicé 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 ordenació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 .

Answer 1

A menudo, es posible que necesitemos calcular Median no solo para toda la tabla, sino también para agregados con respecto a nuestra 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 problemas de parches SQL + de par y probabilidad, más sobre el rendimiento de diferentes métodos de Median https://sqlperformance.com/2012/08/t-sql-queries/median )

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

Espero eso ayude

Answer 2

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 lista:

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

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

Jacob

Answer 3

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

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;

Answer 4

Aquí está mi camino. Por supuesto, podrías ponerlo en un procedimiento :-)

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;

Puede evitar la variable @median_counter , si la sustituye:

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

Answer 5

Conociendo el recuento exacto de filas, puede usar esta consulta:

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
;

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

Answer 6

De esta manera parece incluir recuento par e impar sin subconsulta.

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;

Answer 7

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 conjuntos de resultados con un número par de filas. Las medianas se definen como 1) el número del medio en los conjuntos numerados impares, o 2) el promedio de los dos números del medio en los conjuntos de números pares.

Entonces, aquí está la solución de Velcro parchada para manejar conjuntos de números 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

Answer 8

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

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

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

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

Answer 9

El problema con la solución propuesta (TheJacobTaylor) es el tiempo de ejecución. Unirse a la mesa en sí mismo es lento como melaza para grandes conjuntos de datos. Mi alternativa propuesta se ejecuta en mysql, tiene un tiempo de ejecución impresionante, usa una instrucción ORDER BY explícita, por lo que no tiene que esperar que sus índices lo hayan ordenado 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 () alrededor de t1.val y row_number en (...) para producir correctamente una mediana cuando hay un número par de registros. 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

Answer 10

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

La "mediana" es el valor "medio" en la lista de números cuando se ordenan por valor. Para incluso los conjuntos de conteo, la mediana es el promedio de los dos valores medios . Creé un código simple para eso:

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;

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

Answer 11

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

Answer 12

Instale y use estas 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 la mediana (x) DESDE t1

Answer 13

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

Yo uso esto:

SET group_concat_max_len = 10485760; #10MB max length

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

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

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

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

Answer 14

Medianas agrupadas por dimensión:

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

Answer 15

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

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

Answer 16

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 obtener una mediana para cada grupo en consultas grupales (¡esto es lo que necesitaba!):

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

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 sesión SQL actual):

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

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

Answer 17

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

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

Answer 18

Otro riff en la respuesta de Velcrow, pero usa una sola tabla intermedia y aprovecha la variable utilizada para numerar las filas para obtener 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 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;

Answer 19

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

Answer 20

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

Answer 21

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

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;

Answer 22

Según la respuesta de @ bob, esto generaliza la consulta para que tenga la capacidad de devolver múltiples medianas, agrupadas por algunos criterios.

Piense, por ejemplo, en el precio medio de venta de automóviles usados ​​en un estacionamiento, 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;

Answer 23

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

(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

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

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

Robar

Answer 24

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

Answer 25

Tengo una base de datos que contiene alrededor de mil millones de filas que necesitamos para determinar la edad mediana en el conjunto. Clasificar mil millones de filas es difícil, pero si agrega los distintos valores que se pueden encontrar (las edades oscilan entre 0 y 100), puede ordenar ESTA lista y utilizar alguna magia aritmética para buscar el percentil que desee de la siguiente manera:

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

Esta consulta depende de las funciones de ventana de soporte de su BD (incluidas las FILAS SIN VENCIMIENTO PRECEDENTES), pero si no tiene eso, es sencillo unirse a aggData CTE consigo mismo y agregar todos los totales anteriores en la columna ''acumulada'' que se utiliza para determinar qué value contiene el precentile especificado. La muestra anterior calcule p10, p25, p50 (mediana), p75 y p90.

-Chris

Answer 26

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

Sugeriría otra manera, sin unirme , pero trabajando con cadenas

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

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

aquí está el código de prueba para la tabla de prueba:

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

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

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

Salida:

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

Answer 27

Un comentario en 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

Answer 28

Usé un enfoque de dos consultas:

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

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

Si sus rangos son estáticos y sus datos no cambian con frecuencia, puede ser más eficiente precomputar / almacenar estos valores y usar los valores almacenados en lugar de consultar desde cero cada vez.

Answer 29

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

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

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

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;

Answer 30

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

Lo anterior parece funcionar para mí.