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

Рейтинг: 1Ответов: 2Опубликовано: 29.07.2023

Есть задача, написать код на SQL: Написать запрос, добавляющий новых сотрудников таким образом, чтобы средняя зарплата в каждом отделе стала одинаковая, причем зарплата новых сотрудников должна быть положительной. Можно добавить не более чем по 1 сотруднику в отдел. Поле CHIEF_ID для новых сотрудников оставить пустым.

введите сюда описание изображения

Код для создания таблиц:

CREATE TABLE department
    ("department_id" numeric, "name" varchar(100), "upper_dep" numeric)
;
    
INSERT INTO department
    ("department_id", "name", "upper_dep")
VALUES
    (1, 'prodazhniki', 0),
    (2, 'kreditniki', 1),
    (3, 'service', 1),
    (4, 'vsp', 2),
    (5, 'marketing', 4)
;


CREATE TABLE employee
    ("employer_id" numeric, "department_id" numeric, "name" varchar(100), "chief_id" numeric, "salary" numeric)
;
    
INSERT INTO employee
    ("employer_id", "department_id", "name", "chief_id", "salary")
VALUES
    (1, 1, 'Ivan', 1, 99000),
    (2, 1, 'Oleg', 0, 60000),
    (3, 1, 'Mariazw', 0, 55000),
    (4, 1, 'Veronika', 0, 60000),
    (5, 1, 'Marat', 0, 87000),
    (6, 2, 'Gulnaz', 1, 99000),
    (7, 2, 'Viktor', 0, 70000),
    (8, 2, 'Alina', 0, 69000),
    (9, 2, 'Grigoriy', 0, 55000),
    (10, 2, 'Michael', 0, 55000),
    (11, 3, 'Stepan', 1, 96000),
    (12, 3, 'Vasilisa', 0, 60000),
    (13, 3, 'Karina', 0, 60000),
    (14, 3, 'Olesya', 0, 55000),
    (15, 3, 'Milana', 0, 59999),
    (16, 4, 'Odrej', 1, 99000),
    (17, 4, 'Ivanes', 0, 80000),
    (18, 4, 'Vantey', 0, 78000),
    (19, 4, 'Shamsutdin', 0, 80000),
    (20, 4, 'Shamil', 0, 77000),
    (21, 4, 'Mechman', 0, 76000),
    (22, 4, 'Zwonarev', 0, 75000),
    (23, 5, 'Snegana', 1, 90000),
    (24, 5, 'Vasilen', 0, 45000),
    (25, 5, 'Okolikos', 0, 65000),
    (26, 5, 'Pasaremos', 0, 76000),
    (27, 5, 'Solaris', 0, 77000),
    (28, 5, 'Lok dog', 0, 89000),
    (29, 5, 'Pazwiz', 0, 90000),
    (30, 5, 'Dmitrislo', 0, 76000)
;

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

INSERT INTO employee (employer_id, department_id, name, chief_id, salary)
SELECT
  (SELECT MAX(employer_id) + 1 FROM employee) AS employer_id,
  department_id,
  CONCAT('NewEmployee', department_id) AS name,
  NULL AS chief_id,
  GREATEST(ROUND(((SELECT AVG(salary) FROM employee) - 
          (SELECT AVG(salary) FROM employee WHERE department_id = e.department_id)) / 
          (SELECT COUNT(DISTINCT department_id) FROM employee), 2), 0) AS salary_adjustment
FROM employee e
GROUP BY department_id;
select * from employee
sql
Источник: Stack Overflow на русском

Ответы

▲ 2

Вариант решения. Демо.

select department.name, addemployee, addsalary from 
 (
  select department_id,
    case when (srednee < maxsrednee) then 1 else 0 end as AddEmployee,
    case when (srednee < maxsrednee) then (maxsrednee*(chisl+1)-summa) else 0 end as AddSalary FROM 
   (
    select department_id,Summa,Srednee, MAX(Srednee) OVER() AS MaxSrednee, Chisl FROM 
     (
      select department_id,
        sum(salary) as Summa,
        avg(salary) as Srednee, 
        count(*) as Chisl
      from employee group by department_id
     ) as sq1
   ) as sq2
) as sq3, department where department.department_id = sq3.department_id
name addemployee addsalary
prodazhniki 1 123285.71428571429
kreditniki 1 136285.7142857143
service 1 153286.7142857143
vsp 0 0
marketing 1 118428.57142857142
▲ 1

Алгоритм решения: Для этого нужно добавить по 1 сотруднику в каждый отдел, кроме отдела с максимальной средней зарплатой. Алгоритм расчета зарплаты для добавляемого сотрудника: 1 Вычислить максимальное среднее значение зарплаты среди отделов 2. отнять от этого значения среднее значение зарплаты по рассчитываемому отделу. 3. умножить полученную разницу на количество сотрудников с учетом добавляемого сотрудника 4 добавить к этому значению среднее по рассчитываемому отделу

код решения:

WITH avg_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY department_id
),
max_avg_salary AS (
    SELECT MAX(avg_salary) AS max_avg_salary
    FROM avg_salaries
),
employee_count AS (
    SELECT department_id, COUNT(*) AS emp_count
    FROM employee
    GROUP BY department_id
),
eligible_departments AS (
    SELECT a.department_id, a.avg_salary, e.emp_count
    FROM avg_salaries a
    JOIN max_avg_salary mas ON a.avg_salary < mas.max_avg_salary
    JOIN employee_count e ON a.department_id = e.department_id
),
calculated_salaries AS (
    SELECT
        ed.department_id,
        (mas.max_avg_salary - ed.avg_salary) * (ed.emp_count + 1) + ed.avg_salary AS calculated_salary
    FROM eligible_departments ed
    CROSS JOIN max_avg_salary mas
)

INSERT INTO employee (department_id, name, chief_id, salary)
SELECT
    cs.department_id,
    CONCAT('New Employee in Department ', cs.department_id) AS name,
    0 AS chief_id,
    cs.calculated_salary AS salary
FROM calculated_salaries cs;