Посоветуйте способ решения такой задачи.
Есть клиенты, которым начисляются бонусные баллы.
В некоторых случаях у начисленных бонусных баллов есть срок действия, после которого они «сгорают», если не будут использованы до этого момента.
Когда бонусные баллы используются, то используются в первую очередь бонусные баллы с ближайшим сроком действия. Если таковых несколько, то в первую очередь используются бонусные баллы с минимальной суммой. Если же бонусных баллов со сроком действия не осталось, то используются бонусные баллы без ограничения срока действия.
На примере это будет выглядеть примерно так:
01.01.2017: 0 баллов
02.01.2017: начисление 100 баллов без срока действия
05.01.2017: начисление 100 баллов сроком до 10.01.2017
06.01.2017: начисление 200 баллов сроком до 20.01.2017
07.01.2017: начисление 50 баллов сроком до 10.01.2017
08.01.2017: на счету 450 баллов, из которых 150 действуют до 10.01.2017 и 200 действуют до 20.01.2017
09.01.2017: использование 100 баллов:
- используется 50/50 баллов со сроком действия до 10.01.2017 (зачисленных 07.01.2017)
- используется 50/100 баллов со сроком действия до 10.01.2017 (зачисленных 05.01.2017)
10.01.2017: сгорают 50/100 баллов, на счету 300 баллов, из которых 200 действуют до 20.01.2017
Структура данных планируется примерно такая (sqlfiddle почему-то сломался, поэтому даю не ссылкой, а кодом):
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.
create table BONUS_ACCOUNT (
CLIENT_ID number not null,
BONUS_ID number not null,
AMOUNT number not null
);
insert into BONUS_ACCOUNT (CLIENT_ID, BONUS_ID, AMOUNT) values (1,1,0);
create table BONUS_CHARGES (
CHARGE_ID number not null,
BONUS_ID number not null,
MOMENT date default sysdate not null,
AMOUNT number not null,
DISCARD_DATE date null,
DISCARD_AMOUNT number null,
IS_INCOME number(1) null
);
insert into BONUS_CHARGES (CHARGE_ID, BONUS_ID, MOMENT, AMOUNT, DISCARD_DATE, DISCARD_AMOUNT, IS_INCOME)
values (1, 1, date'2017-01-02', 100, null, null, 1);
insert into BONUS_CHARGES (CHARGE_ID, BONUS_ID, MOMENT, AMOUNT, DISCARD_DATE, DISCARD_AMOUNT, IS_INCOME)
values (2, 1, date'2017-01-05', 100, date'2017-01-10', 100, 1);
insert into BONUS_CHARGES (CHARGE_ID, BONUS_ID, MOMENT, AMOUNT, DISCARD_DATE, DISCARD_AMOUNT, IS_INCOME)
values (3, 1, date'2017-01-06', 200, date'2017-01-20', 200, 1);
insert into BONUS_CHARGES (CHARGE_ID, BONUS_ID, MOMENT, AMOUNT, DISCARD_DATE, DISCARD_AMOUNT, IS_INCOME)
values (4, 1, date'2017-01-07', 50, date'2017-01-10', 50, 1);
-- Использование баллов 09.01.2017
insert into BONUS_CHARGES (CHARGE_ID, BONUS_ID, MOMENT, AMOUNT, DISCARD_DATE, DISCARD_AMOUNT, IS_INCOME)
values (5, 1, date'2017-01-09', 100, null, null, null);
update BONUS_CHARGES set DISCARD_AMOUNT = 0 where CHARGE_ID = 4;
update BONUS_CHARGES set DISCARD_AMOUNT = 50 where CHARGE_ID = 2;
update BONUS_ACCOUNT set AMOUNT = AMOUNT - 100 where BONUS_ID = 1;
-- Сгорание неиспользованных баллов 10.01.2017
update BONUS_ACCOUNT set AMOUNT = AMOUNT - (select ... from ...) where ... ;
update BONUS_CHARGES set DISCARD_AMOUNT = 0 where DISCARD_AMOUNT > 0 and DISCARD_DATE = date'2017-01-10';
При использовании бонусов нужно зафиксировать эту операцию в журнале операций (insert с CHARGE_ID=5), затем использовать баллы с ближайшим сроком действия и с меньшей суммой.
Возможно ли это сделать с помощью чистого SQL (выбор подходящих начислений с набегающим итогом и обновление выбранных строк)?
Или целесообразнее это делать циклом в PL/SQL скрипте (хранимой процедуре)?
________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
