Рассчитать текущую общую сумму с бонусом

#sql #psql #postgresql-9.5

Вопрос:

У меня есть следующая таблица:

 create table test_table
(
   employee_id integer, 
   salary_year integer, 
   raise_in_salary_perentage decimal(18,2), 
   annual_salary decimal(18,2)
);

** Тестовые данные следующие: **

 insert into test_table values ( 1,2016, 0 , 100); 
insert into test_table values ( 1,2017, 10, 100); 
insert into test_table values ( 1,2018, 10, 100); 
insert into test_table values ( 1,2019, 0,  100); 
insert into test_table values ( 1,2020, 10,  100); 
insert into test_table values ( 2,2016, 10 , 100); 
insert into test_table values ( 2,2017, 10, 100); 
insert into test_table values ( 2,2018, 0, 100); 
insert into test_table values ( 2,2019, 0,  100); 
insert into test_table values ( 2,2020, 0,  100);

Я пытаюсь добиться следующего результата:

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

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

Я пытался достичь этого, используя следующий SQL, но результаты кажутся правильными. Буду благодарен за решение.

 SELECT *
    ,sum(annual_salary) OVER (
        PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW
        ) AS cummulative_salary
    ,(
        sum(annual_salary) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            )
        )   (
        sum(annual_salary) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            )
        ) * (
        sum(raise_in_salary_perentage) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            ) / 100
        ) AS csalary
FROM test_table;

1. Большинство людей не стали бы выполнять такую задачу напрямую в sql, а запросили бы данные на выбранном ими языке (Python, PHP, Java …) и обработали бы их там.

2. Я не получаю результатов. Почему вторая строка 220, а не 210? То есть 100 за первый год, а затем 110 за второй?

Ответ №1:

Исходя из вашего описания, увеличение зарплаты должно быть кумулятивным. Однако увеличение данного года не должно влиять на предыдущие годы.

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

 with recursive cte as (
      select employee_id, salary_year, (t.annual_salary * (1   raise_in_salary_perentage / 100.0))::numeric(18, 2) as annual_salary,
             raise_in_salary_perentage,
             (t.annual_salary * (1   raise_in_salary_perentage / 100.0))::numeric(18, 2) as total
      from test_table t
      where salary_year = 2016
      union all
      select t.employee_id, t.salary_year, (cte.annual_salary * (1   t.raise_in_salary_perentage / 100.0))::numeric(18, 2),
             t.raise_in_salary_perentage,
             (cte.total   cte.annual_salary * (1   t.raise_in_salary_perentage / 100.0))::numeric(18, 2)
      from cte join
           test_table t
           on t.employee_id = cte.employee_id and t.salary_year = cte.salary_year   1
     )
select *
from cte
order by employee_id, salary_year;

Вот скрипка db<> .