Есть електрический счетчик к-во цыфр 2(максимальное значение 99.99)
есть 2 таблички:
список существующих показаний счетчика - test_exists
список новых показаний (для загрузки в существующую табличку) - test_load
скрипт:
-- dropping
drop table TEST_EXISTS;
drop table TEST_LOAD;
-- creation
CREATE TABLE TEST_EXISTS
(
DATE_VAL DATE NOT NULL,
VALUE NUMBER NOT NULL,
TYPE VARCHAR2(100 CHAR),
DIFF NUMBER
);
CREATE TABLE TEST_LOAD
(
DATE_VAL DATE NOT NULL,
VALUE NUMBER NOT NULL,
TYPE VARCHAR2(100 CHAR)
);
-- inserting into main table
INSERT INTO TEST_EXISTS(DATE_VAL, VALUE,TYPE,DIFF ) VALUES(to_date('01-01-2000','DD-MM-YYYY'),90,'Normal',0);
INSERT INTO TEST_EXISTS(DATE_VAL, VALUE,TYPE,DIFF ) VALUES(to_date('01-03-2000','DD-MM-YYYY'),20,'Overflow',20);
INSERT INTO TEST_EXISTS(DATE_VAL, VALUE,TYPE,DIFF ) VALUES(to_date('01-05-2000','DD-MM-YYYY'),95,'Rollback',-15);
-- inserting into temporary table
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('01-04-2000','DD-MM-YYYY'),10);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('02-04-2000','DD-MM-YYYY'),50);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('03-04-2000','DD-MM-YYYY'),70);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('04-04-2000','DD-MM-YYYY'),40);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('05-04-2000','DD-MM-YYYY'),55);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('09-04-2000','DD-MM-YYYY'),30);
INSERT INTO TEST_LOAD(DATE_VAL, VALUE) VALUES(to_date('01-06-2000','DD-MM-YYYY'),44);
commit;
тип показания автоматически определяется сл. образом:
- если разница между текущим и предыдущим значением меньше 1/3 максимально возможного значения (100) или
- если разница между текущим и предыдущим значением больше 2/3 максимально возможного значения (100) - то
дополнительная логика (одно из (rollback, normal, overflow))
- если разница между текущим и предыдущим значением между 1/3 и 2/3 (33 и 66 в нашем случае) => ошибка, запись не должна инсертится.
Существующие записи в табличке test_exists удалятся не должны.
Проблема в том , что если между 2 существующими записями инсертится датасет из нескольких показаний,
то я не могу одним проходом найти все невалидные записи:
Запрос:
select
z.is_exist,
z.date_val,
z.value,
z.type,
abs(value - lag(case when is_exist = 1 then value else null end ignore nulls )over( order by date_val )) as difference_prev_exist,
abs(value - lag(value) over(order by date_val)) as difference,
case
when
abs(value - lag(value) over( order by date_val)) between 33 and 66 and is_exist = 0 then 'yes'
when
lead(z.type) over (order by z.date_val) = 'error' and lead(z.is_exist) over (order by z.date_val) = 1 then 'yes'
else 'no'
end to_delete
from
(select
t.is_exist,
t.date_val,
t.value,
case
when value - (lag(t.value) over (order by t.date_val)) is null then
'normal'
-- if previous measurement < new measurement
when value - (lag(t.value) over (order by t.date_val)) >= 0 then
case
when abs(value - (lag(value) over (order by t.date_val)))/100 > 2/3 then
'rollback' -- rollback
when abs(t.value - (lag(t.value) over(order by t.date_val)))/100 < 1/3 then
'normal' -- default
when abs(t.value - (lag(t.value) over(order by t.date_val))) = 0 then
'normal' -- default
else
'error'
end
-- if previous measurement > new measurement
when (lag(t.value) over ( order by t.date_val)) - value > 0 then
case
when abs(lag(value) over ( order by t.date_val)- value )/100 < 1/3 then
'rollback' -- rollback
when abs(lag(value) over ( order by t.date_val) - value)/100 > 2/3 then
'overflow' -- overflow
when abs(lag(value) over ( order by t.date_val)- value) = 0 then
'normal' -- default
else
'error'
end
end type
from
(select
l.date_val,l.value ,0 as is_exist
from
test_load l
--where l.value not in (50,30,44)
--where l.value not in (50,30,70,55,44)
--where l.value not in (50,30,70,55,40,44)
union all
select
e.date_val,e.value, 1 as is_exist
from
test_exists e
order by 1 asc) t
)z
шаг1: Я нахожу все записи, которые не удовлетворяют выше упомянутым условиям. удаляю 3 записи (закоментированная строка --where l.value not in (50,30,44) )
шаг2: выполняю запрос еще раз - в результате невалидными становятся новые записи велью ин (70,55) (--where l.value not in (50,30,70,55,44))
шаг3: - аналогично..
шаг4 - все ок..
Можно откоментировать по очереди строки с (-- where l.value not in ) в сабселекте и запускать запрос для наглядности...
Единственное что придумал, загнать все в колекцию и перебором гонять все в цыкле , пока все будет ок.;
Данных может быть много...
Вопрос, возможно ли одним проходом найти все невалидные записи???