SQL и Базы Данных

Трюк дня. Найти медиану



Предположим, есть таблица, содержащая в столбце sales такие записи:

2, 1, 3, 8, 7, 5.



Необходимо найти медиану для данного столбца.





Решение:

В PostgreSQL используйте функцию percentile_count:

SELECT percentile_cont (0.5) WITHIN GROUP (ORDER BY sales) FROM table;

В MS SQL также используйте функцию percentile_count:

SELECT percentile_cont (0.5) WITHIN GROUP (ORDER BY sales) OVER (PARTITION BY 1) FROM table;

В PARTITION BY можно внести столбец, по которому группируются данные. В данном случае мы использовали 1, чтобы посчитать медиану по всем данным столбца.



В Oracle используйте функцию MEDIAN:

SELECT MEDIAN (sales) OVER (PARTITION BY 1) FROM table;



Однако в MySQL подобных функций нет. Поэтому медиану нужно вычислять самостоятельно.

Напомним: медиана - это число, которое находится в середине набора чисел, отсортированных по возрастанию.

/*задаем переменную row_index = -1, чтобы отсчет индекса начался с 0 */

SET @row_index := -1;



/*находим среднее двух значений в центре отсортированного набора. Охватывает те случаи, когда общее число записей четное*/

SELECT AVG (subq.sales) as median_value

FROM (

    SELECT @row_index:=@row_index + 1 AS row_index, sales

    FROM table

    ORDER BY sales

  ) AS subq

  WHERE subq.row_index 

/*выбираем только значения в центре: одно если число записей нечетное и два если четное*/

  IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));

Ответ: медиана = 4.



#tips