mysql - optimizar - obtener mediana sql

Cálculo de la mediana con Mysql

Question

Tengo problemas para calcular la mediana de una lista de valores, no el promedio.

Encontré este artículo Manera simple de calcular la mediana con MySQL

Tiene una referencia a la siguiente consulta que no entiendo correctamente.

SELECCIONE x.val de datos x, datos y GROUP BY x.val TENGA SUM (SIGN (1-SIGN (y.val-x.val))) = (COUNT (*) + 1) / 2

Si tengo una columna de tiempo y quiero calcular el valor de la mediana, ¿a qué se refieren las columnas xey?

Answer 1

Primero intente comprender cuál es la mediana: es el valor medio en la lista ordenada de valores.

Una vez que comprenda eso, el enfoque es de dos pasos:

1. ordenar los valores en cualquier orden
2. elige el valor medio (si no es un número impar de valores, elige el promedio de los dos valores medios)

Ejemplo:

Median of 0 1 3 7 9 10: 5 (because (7+3)/2=5)
Median of 0 1 3 7 9 10 11: 7 (because 7 is the middle value)

Entonces, para ordenar las fechas necesitas un valor numérico; puede obtener su marca de tiempo (como segundos transcurridos desde la época) y usar la definición de mediana.

Answer 2

val es su columna de tiempo, y son dos referencias a la tabla de datos (puede escribir data AS x, data AS y ).

EDITAR: Para evitar calcular sus sumas dos veces, puede almacenar los resultados intermedios.

CREATE TEMPORARY TABLE average_user_total_time
(SELECT SUM(time) AS time_taken
FROM scores
WHERE created_at >= ''2010-10-10''
and created_at <= ''2010-11-11''
GROUP BY user_id);

Luego puede calcular la mediana sobre estos valores que están en una tabla con nombre.

EDITAR: la tabla temporal no funcionará aquí. Podría intentar usar una tabla normal con el tipo de tabla "MEMORIA". O simplemente haga que su subconsulta calcule los valores de la mediana dos veces en su consulta. Aparte de esto, no veo otra solución. Esto no significa que no hay una mejor manera, tal vez alguien más tenga una idea.

Answer 3

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.

Esto encontrará un percentil arbitrario reemplazando el COUNT(*)/2 con COUNT(*)*n donde n es el percentil (.5 para la mediana, .75 para el percentil 75, etc.).

Answer 4

Encontrar la mediana en mysql usando group_concat

Consulta:

SELECT
IF(count%2=1,
SUBSTRING_INDEX(substring_index(data_str,",",pos),",",-1),
(SUBSTRING_INDEX(substring_index(data_str,",",pos),",",-1)
+ SUBSTRING_INDEX(substring_index(data_str,",",pos+1),",",-1))/2)
as median
FROM (SELECT group_concat(val order by val) data_str,
CEILING(count(*)/2) pos,
count(*) as count from data)temp;

Explicación:

La ordenación se realiza utilizando el orden dentro de la función group_concat

Se identifica la posición (pos) y el número total de elementos (conteo). CEILING para identificar la posición nos ayuda a utilizar la función substring_index en los pasos a continuación.

Según el recuento, se decide el número de valores pares o impares.

• Valores impares: elija directamente el elemento que pertenece a la posición usando substring_index.
• Incluso valores: encuentre el elemento que pertenece a pos y pos + 1, luego agréguelos y divida por 2 para obtener la mediana.

Finalmente, se calcula la mediana.

Answer 5

Si tiene una tabla R con una columna llamada A , y quiere la mediana de A , puede hacer lo siguiente:

SELECT A FROM R R1
WHERE ( SELECT COUNT(A) FROM R R2 WHERE R2.A < R1.A ) = ( SELECT COUNT(A) FROM R R3 WHERE R3.A > R1.A )

Nota : Esto solo funcionará si no hay valores duplicados en A. Además, los valores nulos no están permitidos.