Нахождение среднего, медианы и моды с помощью SQL.

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

В этом блоге мы обсудим способы вычисления этих значений с помощью SQL.

Давайте рассмотрим таблицу ниже, чтобы найти среднее значение, медиану и режим зарплаты по отделам для всех отделов.

╔═══════╦════════╦════════╗
║ empid ║ deptid ║ salary ║
╠═══════╬════════╬════════╣
║ 1     ║ 1      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 2     ║ 2      ║ 3400   ║
╠═══════╬════════╬════════╣
║ 3     ║ 3      ║ 45000  ║
╠═══════╬════════╬════════╣
║ 4     ║ 4      ║ 1320   ║
╠═══════╬════════╬════════╣
║ 5     ║ 4      ║ 5600   ║
╠═══════╬════════╬════════╣
║ 6     ║ 2      ║ 2700   ║
╠═══════╬════════╬════════╣
║ 7     ║ 1      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 8     ║ 3      ║ 34000  ║
╠═══════╬════════╬════════╣
║ 9     ║ 4      ║ 4600   ║
╠═══════╬════════╬════════╣
║ 10    ║ 1      ║ 9000   ║
╠═══════╬════════╬════════╣
║ 11    ║ 1      ║ 13000  ║
╠═══════╬════════╬════════╣
║ 12    ║ 2      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 13    ║ 4      ║ 1320   ║
╠═══════╬════════╬════════╣
║ 14    ║ 3      ║ 24000  ║
╠═══════╬════════╬════════╣
║ 15    ║ 3      ║ 45000  ║
╠═══════╬════════╬════════╣
║ 16    ║ 2      ║ 3400   ║
╠═══════╬════════╬════════╣
║ 17    ║ 2      ║ 1230   ║
╠═══════╬════════╬════════╣
║ 18    ║ 1      ║ 15000  ║
╚═══════╩════════╩════════╝

Войти в полноэкранный режим Выйти из полноэкранного режима

Найти среднее значение довольно просто, так как это просто среднее значение всех зарплат для отдела, и для этого мы можем использовать функцию AVG.

SELECT deptid
    ,AVG(SALARY) AS mean_salary
FROM employee
GROUP BY deptid

Войти в полноэкранный режим Выход из полноэкранного режима

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

Шаги для этого следующие:

Для этого мы начинаем с подсчета количества зарплат для каждой уникальной зарплаты в каждом отделе.
Затем мы ранжируем зарплаты по их частоте (количеству зарплат) в каждом отделе.

salary_counts AS (
    SELECT deptid,
        salary,
        COUNT(salary) AS salary_counts
    FROM employee 
    GROUP BY deptid, salary 
)
-- ranking salaries by frequency
, mode_salaries AS (
    SELECT deptid,
        salary,
        RANK() OVER( PARTITION BY deptid ORDER BY salary_counts DESC ) AS salary_counts_rank
    FROM salary_counts
)
SELECT deptid,
    salary as mode_salary
FROM mode_salaries
WHERE salary_counts_rank = 1

Войти в полноэкранный режим Выйти из полноэкранного режима

Для вычисления медианы зарплаты для каждого отдела нам поможет SQL-функция PERCENTILE_DISC вместе с GROUP.

median_salaries AS (
    SELECT deptid,
        PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY salary) AS median_salary
    FROM employee
    GROUP BY deptid
)
SELECT deptid,
    median_salary 
FROM median_salaries

Войти в полноэкранный режим Выйти из полноэкранного режима

Вот как мы можем вычислить среднее, медиану, режим в SQL.