высчитывать сумму бонуса с учетом предыдущих / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / высчитывать сумму бонуса с учетом предыдущих

9 сообщений из 9, страница 1 из 1

высчитывать сумму бонуса с учетом предыдущих

#39884287

mayapple

Гость

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

Код: xml

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

Клиент	Дата платежа	сумма	Бонус
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. Бонусы начисляются ежедневно за платежи сделанные вчера.

Я написала так:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

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)

Но это подходит для первого запуска, как написать так чтобы учитывались ранее начисленные бонусы по клиенту.

...

Рейтинг:

0 / 0

01.11.2019, 14:38

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39884299

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.

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.

...

Рейтинг:

0 / 0

01.11.2019, 15:04

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39884307

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

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

Код: xml

1.
2.
3.
4.
5.
6.
7.

Клиент	Дата платежа	сумма	Бонус
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 рублей за месяц.
Что такое "за месяц" в Вашем случае?

...

Рейтинг:

0 / 0

01.11.2019, 15:16

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39884310

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

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.

...

Рейтинг:

0 / 0

01.11.2019, 15:19

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39884340

mayapple

Гость

andrey_anonymous, каждый месяц счетчик бонусов обнуляется. Т.е если по клиенту в октябре выплачено 500 бонусов, то в ноябре его ноябрьские платежи будут считаться снова. Месяц календарный.

...

Рейтинг:

0 / 0

01.11.2019, 15:48

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39884363

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

mayappleМесяц календарный.
Почему обнулен бонус в подсвеченных строках?

...

Рейтинг:

0 / 0

01.11.2019, 16:14

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39885059

mayapple

Гость

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

...

Рейтинг:

0 / 0

04.11.2019, 20:30

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39885060

mayapple

Гость

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

...

Рейтинг:

0 / 0

04.11.2019, 20:45

| Ответить | Цитировать | Написать

высчитывать сумму бонуса с учетом предыдущих

#39885233

rpovarov

Гость

mayapple

Вот тут хорошо и понятно описано про границы окна: SQL: Rows between unbounded preceding

...

Рейтинг:

0 / 0

05.11.2019, 12:49

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / высчитывать сумму бонуса с учетом предыдущих

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

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

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39884287&tid=1881907]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	51ms
get topic data:	11ms
get forum data:	3ms
get page messages:	42ms
get tp. blocked users:	1ms
others:	18ms

total:	158ms