Интересная задача аналитических запросов Oracle

Question

У меня достаточно опыта работы с аналитическими функциями 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.

Вот таблица ЖУРНАЛ:

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, потому что она показывает только самый последний статус каждой записи. Мне нужно добыть эту информацию из журнала.

Я не могу просто взять SUM(amount) over(partition by 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.

Любое решение, которое работает, будет приемлемым, но я подозреваю, что аналитическая функция (или комбинация аналитических функций) будет лучшим способом решить эту проблему.

Answer 1

Попробуй это

WITH inner AS  
  (SELECT jn_date_time,
     jn_operation,
     bop_id,
     payment_id,
     bond_number,
     bop_domain,
     amount,
     amount - coalesce(lag(amount) over (partition by bop_id order by jn_date_time), 0)      
        as delta_bop_amount
  FROM   JOURNAL)
SELECT inner.*, 
sum(delta_bop_amount) 
  over (partition by payment_id, bond_number order by jn_date_time)  as running_total
FROM inner
ORDER BY bond_number, payment_id

Это вернет тот же ответ для ваших примеров.

Вам нужно два прохода - аналитическая функция во внутреннем запросе вычисляет, насколько каждая запись меняет общее количество для каждого BOP_ID. INS - прямое дополнение, UPD должен вычесть самое последнее значение и добавить новое.

Второй проход делает промежуточный итог по облигации / платежу.

Я предполагаю, что вы хотели рассматривать облигацию / платеж как естественный ключ для текущей суммы, и что для любой комбинации облигации / платежа может быть несколько BOP_ID.

Answer 2

SELECT a.*,   
lag(amount,1) over (PARTITION BY bond_number ORDER BY 
payment_id,jn_date_time)recent_amount,   
amount + nvl(lag(amount,1) over (PARTITION BY bond_number ORDER BY  
payment_id,jn_date_time),0) running_total  
FROM JOURNAL a  
ORDER BY payment_id,jn_date_time  

Это решение дает точный ответ, который вы ожидаете получить по указанному выше вопросу, и тот же ответ за один проход:).

Я только что использовал аналитическую функцию запаздывания, чтобы получить самое последнее значение суммы для комбинации bond_number/payment_id, а затем добавил это последнее значение суммы к сумме, чтобы получить промежуточный итог... ПРОСТОЙ!!!.. не так:)