Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / высчитывать сумму бонуса с учетом предыдущих / 9 сообщений из 9, страница 1 из 1
01.11.2019, 14:38
    #39884287
mayapple
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, 15:04
    #39884299
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
Код: 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:16
    #39884307
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
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:19
    #39884310
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
Код: 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:48
    #39884340
mayapple
mayapple
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
andrey_anonymous, каждый месяц счетчик бонусов обнуляется. Т.е если по клиенту в октябре выплачено 500 бонусов, то в ноябре его ноябрьские платежи будут считаться снова. Месяц календарный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.11.2019, 16:14
    #39884363
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
mayappleМесяц календарный.
Почему обнулен бонус в подсвеченных строках?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 20:30
    #39885059
mayapple
mayapple
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
andrey_anonymous, я тестила на текущих платежах, брала несколько дней, т.к. таблицы большие, но вы все верно заметили, так не должно быть при реализации задачи в конечном виде.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 20:45
    #39885060
mayapple
mayapple
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
AmKad, большое спасибо! Очень красивое решение, все работает. Но не мой уровень знаний...могли бы объяснить как это работает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 12:49
    #39885233
rpovarov
rpovarov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
высчитывать сумму бонуса с учетом предыдущих
mayapple
AmKad, большое спасибо! Очень красивое решение, все работает. Но не мой уровень знаний...могли бы объяснить как это работает?

Вот тут хорошо и понятно описано про границы окна: SQL: Rows between unbounded preceding
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / высчитывать сумму бонуса с учетом предыдущих / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 408ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1881907]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 38ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 261ms
total:  408ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]