Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 высчитывать сумму бонуса с учетом предыдущих  [new]
mayapple
Member

Откуда:
Сообщений: 4
Всех приветствую! Я новичок в SQL, поэтому Прошу помощи в написании запроса:
Есть список платежей клиентов:

Клиент	Дата платежа	сумма	Бонус
115	30.10.2019	100	100
115	31.10.2019	200	200
115	01.11.2019	50	50
115	01.11.2019	200	150
116	30.10.2019	50	50
116	31.10.2019	500	450
116	01.11.2019	10	0
116	01.11.2019	100	0
117	31.10.2019	100	100
118	01.11.2019	500	500


Задача запроса высчитывать столбец "Бонус", бонус равен сумме платежа, но не может превышать 500 рублей за месяц. Если клиент платит больше 500 рублей, то бонус все равно будет 500. Бонусы начисляются ежедневно за платежи сделанные вчера.

Я написала так:
select p.client, p.PAYMENT, trunc(sysdate) DAY,
case when sum(p.payment)<=500 then sum(p.payment)
  else 500
    end BONUS
    	from payment p
where trunc(p.pay_date) = trunc(sysdate)-1 
group by p.client, trunc(sysdate)


Но это подходит для первого запуска, как написать так чтобы учитывались ранее начисленные бонусы по клиенту.
1 ноя 19, 14:38    [22008156]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
AmKad
Member

Откуда:
Сообщений: 5111
with s (client, dt, psum) as (
select 115, to_date('15.08.2019', 'dd.mm.yyyy'), 100 from dual union all
select 115, to_date('17.08.2019', 'dd.mm.yyyy'), 150 from dual union all
select 115, to_date('15.09.2019', 'dd.mm.yyyy'), 900 from dual union all
select 115, to_date('15.10.2019', 'dd.mm.yyyy'), 100 from dual union all
select 115, to_date('16.10.2019', 'dd.mm.yyyy'), 200 from dual union all
select 115, to_date('19.10.2019', 'dd.mm.yyyy'), 100 from dual union all
select 115, to_date('20.10.2019', 'dd.mm.yyyy'), 200 from dual union all
select 115, to_date('21.10.2019', 'dd.mm.yyyy'), 200 from dual union all
select 115, to_date('22.10.2019', 'dd.mm.yyyy'), 200 from dual union all
select 115, to_date('24.10.2019', 'dd.mm.yyyy'), 200 from dual union all
select 115, to_date('01.11.2019', 'dd.mm.yyyy'), 50     from dual union all
select 115, to_date('01.11.2019', 'dd.mm.yyyy'), 200 from dual union all
select 116, to_date('30.10.2019', 'dd.mm.yyyy'), 50     from dual union all
select 116, to_date('31.10.2019', 'dd.mm.yyyy'), 500 from dual union all
select 116, to_date('01.11.2019', 'dd.mm.yyyy'), 10     from dual union all
select 116, to_date('01.11.2019', 'dd.mm.yyyy'), 100 from dual union all
select 117, to_date('31.10.2019', 'dd.mm.yyyy'), 100 from dual union all
select 118, to_date('01.11.2019', 'dd.mm.yyyy'), 500 from dual)
select *
from s
model
partition by (client, trunc(dt, 'month') mn)
dimension by (row_number() over (partition by client, trunc(dt, 'month') order by dt) rn)
measures (dt, psum, 0 bonus, 0 bsum_over)
rules automatic order
(
  bonus[rn] = least(500 - nvl(bsum_over[cv()-1], 0), psum[cv()]),
  bsum_over[rn] = nvl(bsum_over[cv()-1], 0) + bonus[cv()]
)
order by client, dt;

    CLIENT MN                          RN DT                        PSUM      BONUS  BSUM_OVER
---------- ------------------- ---------- ------------------- ---------- ---------- ----------
       115 2019-08-01 00:00:00          1 2019-08-15 00:00:00        100        100        100
       115 2019-08-01 00:00:00          2 2019-08-17 00:00:00        150        150        250
       115 2019-09-01 00:00:00          1 2019-09-15 00:00:00        900        500        500
       115 2019-10-01 00:00:00          1 2019-10-15 00:00:00        100        100        100
       115 2019-10-01 00:00:00          2 2019-10-16 00:00:00        200        200        300
       115 2019-10-01 00:00:00          3 2019-10-19 00:00:00        100        100        400
       115 2019-10-01 00:00:00          4 2019-10-20 00:00:00        200        100        500
       115 2019-10-01 00:00:00          5 2019-10-21 00:00:00        200          0        500
       115 2019-10-01 00:00:00          6 2019-10-22 00:00:00        200          0        500
       115 2019-10-01 00:00:00          7 2019-10-24 00:00:00        200          0        500
       115 2019-11-01 00:00:00          1 2019-11-01 00:00:00         50         50         50
       115 2019-11-01 00:00:00          2 2019-11-01 00:00:00        200        200        250
       116 2019-10-01 00:00:00          1 2019-10-30 00:00:00         50         50         50
       116 2019-10-01 00:00:00          2 2019-10-31 00:00:00        500        450        500
       116 2019-11-01 00:00:00          1 2019-11-01 00:00:00         10         10         10
       116 2019-11-01 00:00:00          2 2019-11-01 00:00:00        100        100        110
       117 2019-10-01 00:00:00          1 2019-10-31 00:00:00        100        100        100
       118 2019-11-01 00:00:00          1 2019-11-01 00:00:00        500        500        500

18 rows selected.
1 ноя 19, 15:04    [22008176]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17717
mayapple
Всех приветствую! Я новичок в SQL, поэтому Прошу помощи в написании запроса:
Есть список платежей клиентов:

Клиент	Дата платежа	сумма	Бонус
116	30.10.2019	50	50
116	31.10.2019	500	450
116	01.11.2019	10	0
116	01.11.2019	100	0
117	31.10.2019	100	100
118	01.11.2019	500	500

500 рублей за месяц.

Что такое "за месяц" в Вашем случае?
1 ноя 19, 15:16    [22008186]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
AmKad
Member

Откуда:
Сообщений: 5111
select s.*,
  greatest(
    least(500 - nvl(sum(psum) over (partition by client, trunc(dt, 'month') order by dt rows between unbounded preceding and 1 preceding), 0), psum),
          0
          ) bonus
from s
order by client, dt;

    CLIENT DT                        PSUM      BONUS
---------- ------------------- ---------- ----------
       115 2019-08-15 00:00:00        100        100
       115 2019-08-17 00:00:00        150        150
       115 2019-09-15 00:00:00        900        500
       115 2019-10-15 00:00:00        100        100
       115 2019-10-16 00:00:00        200        200
       115 2019-10-19 00:00:00        100        100
       115 2019-10-20 00:00:00        200        100
       115 2019-10-21 00:00:00        200          0
       115 2019-10-22 00:00:00        200          0
       115 2019-10-24 00:00:00        200          0
       115 2019-11-01 00:00:00         50         50
       115 2019-11-01 00:00:00        200        200
       116 2019-10-30 00:00:00         50         50
       116 2019-10-31 00:00:00        500        450
       116 2019-11-01 00:00:00         10         10
       116 2019-11-01 00:00:00        100        100
       117 2019-10-31 00:00:00        100        100
       118 2019-11-01 00:00:00        500        500

18 rows selected.
1 ноя 19, 15:19    [22008189]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
mayapple
Member

Откуда:
Сообщений: 4
andrey_anonymous, каждый месяц счетчик бонусов обнуляется. Т.е если по клиенту в октябре выплачено 500 бонусов, то в ноябре его ноябрьские платежи будут считаться снова. Месяц календарный.
1 ноя 19, 15:48    [22008230]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17717
mayapple
Месяц календарный.

Почему обнулен бонус в подсвеченных строках?
1 ноя 19, 16:14    [22008255]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
mayapple
Member

Откуда:
Сообщений: 4
andrey_anonymous, я тестила на текущих платежах, брала несколько дней, т.к. таблицы большие, но вы все верно заметили, так не должно быть при реализации задачи в конечном виде.
4 ноя 19, 20:30    [22009527]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
mayapple
Member

Откуда:
Сообщений: 4
AmKad, большое спасибо! Очень красивое решение, все работает. Но не мой уровень знаний...могли бы объяснить как это работает?
4 ноя 19, 20:45    [22009530]     Ответить | Цитировать Сообщить модератору
 Re: высчитывать сумму бонуса с учетом предыдущих  [new]
rpovarov
Member

Откуда:
Сообщений: 69
mayapple
AmKad, большое спасибо! Очень красивое решение, все работает. Но не мой уровень знаний...могли бы объяснить как это работает?

Вот тут хорошо и понятно описано про границы окна: SQL: Rows between unbounded preceding
5 ноя 19, 12:49    [22009875]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить