Средняя зарплата по отделу

Решение

Задача требует добавления столбца с средней зарплатой по отделу используя оконные функции.

SQL-запрос

SELECT 
  depname,
  empno,
  salary,
  ROUND(
    AVG(salary) OVER (PARTITION BY depname)::NUMERIC,
    2
  ) AS avg_salary_dept
FROM salaries
ORDER BY depname, empno;

Пошаговое объяснение

1. SELECT столбцы

• depname — название отдела
• empno — номер сотрудника
• salary — зарплата
• ROUND(..., 2) — округляем среднее до 2 знаков

2. AVG(salary) OVER (PARTITION BY depname)

• Оконная функция AVG()
• PARTITION BY depname — считаем среднее отдельно для каждого отдела
• Результат: для каждой строки выводим среднюю зарплату её отдела

3. ::NUMERIC для корректного деления

• Гарантирует правильное вычисление среднего

4. ORDER BY depname, empno

• Упорядочиваем результат по отделу и номеру сотрудника

Пример выполнения

Исходные данные:

depname | empno | salary
--------|-------|-------
IT      | 1     | 5000
IT      | 2     | 6000
Sales   | 3     | 4500
Sales   | 4     | 5500

Расчёт средней зарплаты по отделам:

• IT: (5000 + 6000) / 2 = 5500.00
• Sales: (4500 + 5500) / 2 = 5000.00

Результат (с добавлением каждой строке её среднего):

depname | empno | salary | avg_salary_dept
--------|-------|--------|----------------
IT      | 1     | 5000   | 5500.00
IT      | 2     | 6000   | 5500.00
Sales   | 3     | 4500   | 5000.00
Sales   | 4     | 5500   | 5000.00

Альтернативные варианты

Вариант 1: С использованием JOIN (без оконных функций)

SELECT 
  s.depname,
  s.empno,
  s.salary,
  ROUND(dept_avg.avg_salary, 2) AS avg_salary_dept
FROM salaries s
LEFT JOIN (
  SELECT 
    depname,
    AVG(salary) AS avg_salary
  FROM salaries
  GROUP BY depname
) dept_avg ON s.depname = dept_avg.depname
ORDER BY s.depname, s.empno;

Менее эффективен, чем оконная функция.

Вариант 2: С использованием CTE

WITH dept_averages AS (
  SELECT 
    depname,
    ROUND(AVG(salary)::NUMERIC, 2) AS avg_salary
  FROM salaries
  GROUP BY depname
)
SELECT 
  s.depname,
  s.empno,
  s.salary,
  da.avg_salary AS avg_salary_dept
FROM salaries s
JOIN dept_averages da ON s.depname = da.depname
ORDER BY s.depname, s.empno;

Помощнее для более сложных операций.

Преимущества оконных функций

✓ Производительность — одно сканирование таблицы ✓ Простота — не требует JOIN или подзапросов ✓ Читаемость — явно показывает логику разбиения по окнам ✓ Гибкость — легко добавлять другие оконные функции (MAX, MIN, ROW_NUMBER и т.д.)

Расширенный вариант с дополнительной статистикой

SELECT 
  depname,
  empno,
  salary,
  ROUND(
    AVG(salary) OVER (PARTITION BY depname)::NUMERIC,
    2
  ) AS avg_salary_dept,
  MIN(salary) OVER (PARTITION BY depname) AS min_salary_dept,
  MAX(salary) OVER (PARTITION BY depname) AS max_salary_dept,
  salary - ROUND(AVG(salary) OVER (PARTITION BY depname)::NUMERIC, 2) AS diff_from_avg
FROM salaries
ORDER BY depname, empno;

Это добавляет минимум, максимум и разницу от среднего.

Различие между WHERE и PARTITION BY

Производительность

Оконная функция (быстро):

• 1 полное сканирование таблицы
• Сложность: O(n)

С JOIN/подзапросом (медленнее):

• 2 сканирования таблицы (+ JOIN операция)
• Сложность: O(n log n)

Для таблиц > 100,000 строк разница заметна.