¿Cómo puedo calcular la mediana de valores en SQLite?

mysql median

Question

Me gustaría calcular el valor de la mediana en una fila numérica. ¿Cómo puedo hacer eso en SQLite 4?

Answer 1

Digamos que la mediana es el elemento en medio de una lista ordenada.

SQLite (4 o 3) no tiene ninguna función integrada para eso, pero es posible hacerlo a mano:

SELECT x
FROM MyTable
ORDER BY x
LIMIT 1
OFFSET (SELECT COUNT(*)
        FROM MyTable) / 2

Cuando hay un número par de registros, es común definir la mediana como el promedio de los dos registros del medio. En este caso, el promedio se puede calcular así:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

La combinación de los casos pares e impares da como resultado esto:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2 - (SELECT COUNT(*) FROM MyTable) % 2    -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

Answer 2

Hay un paquete de extensión de varias funciones matemáticas para sqlite3. Incluye funciones de grupo como la mediana.

Será más trabajo lograr que la respuesta de CL, pero podría valer la pena si cree que necesitará alguna de las otras funciones.

http://www.sqlite.org/contrib/download/extension-functions.c?get=25

( Here está la guía sobre cómo compilar y cargar extensiones de SQLite).

De la descripción:

Proporcione funciones matemáticas y de extensión de cadena para consultas SQL utilizando el mecanismo de extensiones cargables. Matemáticas: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, diferencia, grados, radianes, cos, pecado, tan, cuna, cosh, sinh, tanh, coth, exp, log, log10, poder, signo, sqrt, square, ceil, floor, pi. Cadena: replicar, charindex, leftstr, rightstr, ltrim, rtrim, recortar, reemplazar, revertir, correcto, padl, padr, padc, strfilter. Agregado: stdev, varianza, modo, mediana, quartil inferior, quartil superior.

ACTUALIZACIÓN 2015-04-12: Corrección "símbolo indefinido: sinh"

Como se ha mencionado en los comentarios, esta extensión puede no funcionar correctamente a pesar de una compilación exitosa.

Por ejemplo, la compilación puede funcionar y en Linux puede copiar el archivo .so resultante a /usr/local/lib . Pero las .load /usr/local/lib/libsqlitefunctions del shell sqlite3 pueden generar este error:

Error: /usr/local/lib/libsqlitefunctions.so: undefined symbol: sinh

Compilarlo de esta manera parece funcionar:

gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so -lm

Y copiar el archivo .so a /usr/local/lib no muestra un error similar:

sqlite> .load /usr/local/lib/libsqlitefunctions

sqlite> select cos(pi()/4.0);
---> 0.707106781186548

No estoy seguro de por qué el orden de opciones a gcc importa en este caso particular, pero aparentemente lo hace.

El crédito por darse cuenta de esto se debe al comentario de Ludvick Lidicky en esta publicación del blog

Answer 3

Hay una tabla de registro con marca de tiempo, etiqueta y latencia. Queremos ver el valor de la mediana de latencia de cada etiqueta, agrupados por marca de tiempo. Formatee todo el valor de latencia a una longitud de 15 caracteres con ceros iniciales, concaténelo y corte el (los) valor (es) de la mitad de la posición. Existe la mediana.

select L, --V, 
       case when C % 2 = 0 then
       ( substr( V, ( C - 1 ) * 15 + 1, 15) * 1 + substr( V, C * 15 + 1, 15) * 1 ) / 2
       else
        substr( V, C * 15 + 1, 15) * 1
       end as MEDST
from (
    select L, group_concat(ST, "") as V, count(ST) / 2 as C
    from (
        select label as L, 
               substr( timeStamp, 1, 8) * 1 as T, 
               printf( ''%015d'',latency) as ST
        from log
        where label not like ''%-%'' and responseMessage = ''OK''
        order by L, T, ST ) as XX
    group by L
    ) as YY

Answer 4

SELECT AVG (x) devuelve solo el año de los valores de fecha formateados como YYYY-MM-DD, así que modifiqué la solución de CL solo un poco para acomodar las fechas:

SELECT DATE(JULIANDAY(MIN(MyDate)) + (JULIANDAY(MAX(MyDate)) - JULIANDAY(MIN(MyDate)))/2) as Median_Date
FROM (
   SELECT MyDate
      FROM MyTable
      ORDER BY MyDate
      LIMIT 2 - ((SELECT COUNT(*) FROM MyTable) % 2) -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2 FROM MyTable)
);