выборка из базы данных строк с нарушением последовательности

#sql #база_данных #oracle #plsql

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

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

Т.е., баланс не соответствует нужному числу с учетом суммы операций.

И еще найти такие строки, по которым дата операции с меньшим номером больше даты
операции с большим номером. 
    

Ответы

Ответ 1

Вы не привели структуру таблиц и данные. Применим экстрасенсорные способности. Структура
таблицы такая:

create table table1(
 usr_id int,  -- Клиент
 opnum int,   -- Порядковый номер операции, начиная с 1
 opval int,   -- Сумма операции
 balance int, -- Текущий баланс
 dt date      -- Дата операции
);


Баланс в любой строке равен сумме всех операций по данному клиенту с первой по текущую.
Номера операций идут подряд +1 к предыдущей записи данного клиента, начиная с 1.

Тестовые данные:

insert into table1 values(1,1,100,100,sysdate-12);
insert into table1 values(1,2,100,200,sysdate-11);
insert into table1 values(1,3,-30,170,sysdate-9);   -- Ошибка даты
insert into table1 values(1,4,300,470,sysdate-10);  -- Ошибка номера, ошибка суммы
insert into table1 values(2,1,100,100,sysdate-12);
insert into table1 values(2,3,100,200,sysdate-11);  -- Пропущен номер операции "2"
insert into table1 values(2,4,100,300,sysdate-10);
insert into table1 values(2,5,50,350, sysdate-9);
insert into table1 values(2,6,-150,200,sysdate-8);
insert into table1 values(2,7,100,250,sysdate-7);   -- Ошибка в сумме


Запрос:

select * from
 (
  select a.*,
     (lag(opnum,1,0) over(partition by usr_id order by dt))+1 n_opnum, -- Ожидаемый номер
     sum(opval) over(partition by usr_id order by dt) n_balance        -- Ожидаемая сумма
    from table1 a
   order by usr_id,dt    -- Для наглядности, на правильность работы не влияет
 )
where n_opnum!=opnum     -- Номер операции не соответствует
   or n_balance!=balance -- Сумма не соответствует


Результат:

USR_ID  OPNUM   OPVAL   BALANCE DT                  N_OPNUM N_BALANCE
1       4       300     470     28.01.2016 18:27:37 3       500
1       3       -30     170     29.01.2016 18:27:37 5       470
2       3       100     200     27.01.2016 18:27:37 2       200
2       7       100     250     31.01.2016 18:27:37 7       300


Вот примерно так. Используются оконные функции, незаменимые в таких случаях. lag(opnum,1,0)
дает значение поля opnum из предыдущей записи в окне, 0 - для первой записи в окне.
sum(opval) over(order by) - нарастающая сумма opval в окне.

Окно в таких функциях можно задавать различными способами. В данном случае мы используем
partition by что бы окно было в пределах одного клиента и применяем order by для указания
порядка операций в пределах клиента.