Я неплохо разбираюсь в аналитических функциях Oracle, но эта меня поставила в тупик. Я пинаю себя, если есть очевидное решение :)
У меня есть таблица JOURNAL, в которой записываются вставки, обновления и удаления из другой таблицы.
Журнал ведется в таблице BOND_PAYMENTS, которая представляет связи между PAYMENTS и BONDS; в нем хранится сумма денег (AMOUNT), которая была выделена для конкретной облигации (обозначенной BOND_NUMBER) из определенного платежа (обозначенного PAYMENT_ID). Кроме того, он записывает, какой аспект облигации он был назначен (BOP_DOMAIN), который может быть «BON», «PET» или каким-либо другим кодом. Таблица BOND_PAYMENTS имеет суррогатный ключ (BOP_ID).
Таким образом, моя таблица журнала обычно содержит 1 или несколько записей для каждого BOP_ID - сначала INSert, затем, возможно, несколько UPDates, а затем, возможно, DELete.
Вот таблица JOURNAL:
CREATE TABLE JOURNAL
( JN_DATE_TIME DATE NOT NULL,
JN_OPERATION VARCHAR2(3) NOT NULL,
BOP_ID NUMBER(9) NOT NULL,
PAYMENT_ID NUMBER(9) NOT NULL,
BOND_NUMBER VARCHAR2(20) NOT NULL,
BOP_DOMAIN VARCHAR2(10) NOT NULL,
AMOUNT NUMBER(14,2) NOT NULL
);
Вот несколько примеров данных:
INSERT INTO JOURNAL VALUES (TO_DATE('01/01/2010','DD/MM/YYYY'),'INS',1242043,1003700,'9995/10','BON',1800);
INSERT INTO JOURNAL VALUES (TO_DATE('03/01/2010','DD/MM/YYYY'),'INS',1242046,1003700,'9998/10','BON',1700);
INSERT INTO JOURNAL VALUES (TO_DATE('04/01/2010','DD/MM/YYYY'),'INS',1242048,1003700,'9999/10','BON',1800);
INSERT INTO JOURNAL VALUES (TO_DATE('05/01/2010','DD/MM/YYYY'),'INS',1242052,1003700,'10003/10','BON',1600);
INSERT INTO JOURNAL VALUES (TO_DATE('08/01/2010','DD/MM/YYYY'),'INS',1242058,1003700,'9998/10','BON',100);
INSERT INTO JOURNAL VALUES (TO_DATE('09/01/2010','DD/MM/YYYY'),'UPD',1242058,1003700,'9998/10','PET',100);
INSERT INTO JOURNAL VALUES (TO_DATE('01/01/2010','DD/MM/YYYY'),'INS',2242043,1003701,'8995/10','BON',1800);
INSERT INTO JOURNAL VALUES (TO_DATE('02/01/2010','DD/MM/YYYY'),'INS',2242046,1003701,'8998/10','BON',1700);
INSERT INTO JOURNAL VALUES (TO_DATE('03/01/2010','DD/MM/YYYY'),'INS',2242048,1003701,'8999/10','BON',1800);
INSERT INTO JOURNAL VALUES (TO_DATE('04/01/2010','DD/MM/YYYY'),'INS',2242058,1003701,'8998/10','BON',100);
INSERT INTO JOURNAL VALUES (TO_DATE('05/01/2010','DD/MM/YYYY'),'UPD',2242046,1003701,'8998/10','BON',1500);
INSERT INTO JOURNAL VALUES (TO_DATE('06/01/2010','DD/MM/YYYY'),'INS',2242052,1003701,'9003/10','BON',1600);
INSERT INTO JOURNAL VALUES (TO_DATE('07/01/2010','DD/MM/YYYY'),'UPD',2242058,1003701,'8998/10','PET',200);
Теперь мне нужно извлечь полный набор данных из этой таблицы журнала, но в несколько другом формате. Основное требование - мы не хотим, чтобы таблица журнала больше записывала BOP_DOMAIN - это просто не требуется.
Мне нужно создать историю общей суммы для каждой записи BOND_PAYMENT. Я не могу использовать саму таблицу BOND_PAYMENT, потому что она показывает только последний статус каждой записи.Мне нужно извлечь эту информацию из журнала.
Я не могу просто взять СУММ (сумма) за (разделение по payment_id, bond_number)
, потому что отдельный BOP_ID может обновляться несколько раз; поэтому в любой момент времени должна использоваться только последняя сумма, записанная для этого BOP_ID.
Учитывая приведенные выше примерные данные, вот иллюстрация того, что я ожидал бы получить:
SELECT jn_date_time,
jn_operation,
bop_id,
payment_id,
bond_number,
bop_domain,
amount,
? as running_total
FROM JOURNAL
ORDER BY jn_date_time;
Здесь я воспроизвел слева примерные данные для двух выборочных платежей. Справа у меня есть «Промежуточный итог», который является ожидаемым результатом. Рядом с ним (красным) показана логика расчета промежуточной суммы для каждой строки.
«Промежуточная сумма» - это моментальный снимок на момент записи журнала общей суммы для этой комбинации PAYMENT_ID и BOND_NUMBER. Помните, что конкретный BOP_ID может обновляться несколько раз; общая сумма должна учитывать только самую последнюю запись для этого BOP_ID.
Любое работающее решение будет приемлемым, но я подозреваю, что аналитическая функция (или комбинация аналитических функций) будет лучшим способом решить эту проблему.