Ответ 1

Скажем, что медиана - это элемент в середине упорядоченного списка.

SQLite (4 или 3) для этого не имеет встроенной функции, но это можно сделать вручную:

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

Когда есть четное количество записей, обычно определяется медиана как среднее из двух средних записей. В этом случае среднее значение можно вычислить следующим образом:

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

Сочетание нечетных и четных случаев приводит к следующему:

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

Ответ 2

Для sqlite3 существует набор расширений различных математических функций. Он включает в себя функции группы, такие как медиана.

Будет больше работать над этим, чем CL, но может быть целесообразным, если вы считаете, что вам понадобятся какие-либо другие функции.

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

(Здесь описано, как компилировать и загружать расширения SQLite.)

Из описания:

Предоставление математических и строковых функций расширения для SQL-запросов с использованием механизма загружаемых расширений. Математика: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, разница, градусы, радианы, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Совокупность: stdev, дисперсия, режим, медиана, lower_quartile, upper_quartile.

ОБНОВЛЕНИЕ 2015-04-12: Фиксация "undefined symbol: sinh"

Как уже упоминалось в комментариях, это расширение может работать неправильно, несмотря на успешную компиляцию.

Например, компиляция может работать, и в Linux вы можете скопировать полученный .so файл в /usr/local/lib. Но .load /usr/local/lib/libsqlitefunctions из оболочки sqlite3 может затем сгенерировать эту ошибку:

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

Скомпилировать его таким образом, кажется, работает:

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

И копирование файла .so на /usr/local/lib не показывает аналогичной ошибки:

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

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

Я не уверен, почему порядок опций gcc имеет значение в данном конкретном случае, но, по-видимому, это так.

Кредит за это замечает Ludvick Lidicky комментарий это сообщение в блоге

Ответ 3

Существует таблица журнала с отметкой времени, меткой и задержкой. Мы хотим видеть значение медианы задержки каждой метки, сгруппированное по метке времени. Отформатируйте все значения задержки до 15 символов длины с ведущими нулями, объедините их и обрежьте половину позиционных значений. Это медиана.

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

Ответ 4

Dixtroy предоставил лучшее решение через group_concat(). Вот полный пример для этого:

DROP TABLE [t];
CREATE TABLE [t] (name, value INT);
INSERT INTO t VALUES ('A', 2);
INSERT INTO t VALUES ('A', 3);
INSERT INTO t VALUES ('B', 4);
INSERT INTO t VALUES ('B', 5);
INSERT INTO t VALUES ('B', 6);
INSERT INTO t VALUES ('C', 7);

результаты в этой таблице:

name|value
A|2
A|3
B|4
B|5
B|6
C|7

теперь мы используем (слегка измененный) запрос от Dextroy:

SELECT name, --string_list, count, middle,
    CASE WHEN count%2=0 THEN
        0.5 * substr(string_list, middle-10, 10) + 0.5 * substr(string_list, middle, 10)
    ELSE
        1.0 * substr(string_list, middle, 10)
    END AS median
FROM (
    SELECT name, 
        group_concat(value_string,"") AS string_list,
        count() AS count, 
        1 + 10*(count()/2) AS middle
    FROM (
        SELECT name, 
            printf( '%010d',value) AS value_string
        FROM [t]
        ORDER BY name,value_string
    )
    GROUP BY name
);

... и получите такой результат:

name|median
A|2.5
B|5.0
C|7.0

Ответ 5

SELECT AVG (x) возвращает только данные даты года, отформатированные как YYYY-MM-DD, поэтому я немного изменил решение CL для размещения дат:

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