Joomla 3.1 Templates by FatCow Coupon

Oracle SQL UNION, MINUS, INTERSECT - несложные, но эффективные приемы

Категория: Блог Опубликовано 23.05.2017

В этом небольшом материале речь пойдет о сравнении и сопоставлении некоторых одноформатных данных
С такой задачей я столкнулся во время работы над одним из проектов.
Предположим , что есть некий эталонный набор данных, одна или несколько таблиц со структурой заданного формата.
С определенной периодичностью из внешних источников поступают данные в таблицах такой же структуры, но сам данные могут незначительно различаться
Итак, вот несколько несложных , приемов которые помогут вам решить подобную задачу


-- Подготавливаем данные для нашего примера

-- Создаем таблицу t1 заполняем ее данными

create table t1(tid number,tname varchar2(80));
insert into t1 values (10,'Акулина');
insert into t1 values (20,'Бронислав');
insert into t1 values (30,'Богдан');
insert into t1 values (40,'Борислав');
insert into t1 values (50,'Божена');
insert into t1 values (60,'Ванда');
insert into t1 values (70,'Владислав');
insert into t1 values (80,'Вилен');
insert into t1 values (90,'Вера');
insert into t1 values (100,'Доля');
insert into t1 values (110,'Ждан');
insert into t1 values (120,'Лада');
insert into t1 values (130,'Любомила');
insert into t1 values (140,'Мартин');
insert into t1 values (150,'Милан');
insert into t1 values (160,'Мичлов');
insert into t1 values (170,'Мечеслав');
insert into t1 values (180,'Олеся');
Insert into t1 values (190,'Рада');
insert into t1 values (200,'Ростислав');
insert into t1 values (210,'Святослав');
insert into t1 values (220,'Станислав');
insert into t1 values (230,'Томила');

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

insert into t1 values (30,'Богдан');
insert into t1 values (210,'Святослав');



-- создадим вторую таблицу

create table t2 as select tid, tname from t1;



-- сознательно удалим из второй таблицы строки 50, 70 , 90

delete t2 where tid in (50, 70 , 90);



-- и добавим уникальные строки которых в таблице t1 нет

insert into t2 values (240,'Борис');
insert into t2 values (250,'Елисей');
insert into t2 values (270,'Мирослав');



-- ищем те данные которые есть в таблице t2 и которых нет в таблице t1

select * from t2 where t2.tid not in (select tid from t1)



-- или так же с оператором exist

select * from t2 where not exists (select tid from t1 where t2.tid = t1.tid )



-- результат

-- 240 Борис
-- 250 Елисей
-- 270 Мирослав



-- но сложность в том, что набор данных t1 может быть весьма значителен от 100000 строк и более
-- тогда разумнее всего слить две таблицы и отсеять ненужные записи

select t2.* from t2,t1 where t1.tid(+)= t2.tid and t1.tid is null



-- результат
-- 240 Борис
-- 250 Елисей
-- 270 Мирослав




-- а как быть , если нам необходимо выполнить сравнение по всем полям таблицы
-- а этих полей в таблице не 2 как в нашем примере а 30 и более
-- для этих целей существует удобный реляционный оператор для работы с множествами
-- MINUS который как бы вычитает из множества А множество Б
-- итак посмотрим на наш пример с оператором MINUS

select * from t2 minus select * from t1



-- итак мы получаем тот же

-- результат
-- 240 Борис
-- 250 Елисей
-- 270 Мирослав



-- операторы Minus так же можно сочитать с предикатом whrere поэтому запись вида
-- вполне правомочна

select * from t2 where t2.tid < 250 minus select * from t1


-- результат
-- 240 Борис

-- попробуем только те данные которые есть в таблице t1 и в таблице t2
-- сразу напрашивается решение связать две таблицы, но мы пойдем другим путем
-- так как это иногда эффективнее чем перечислений множества ключевых полей таблицы

select * from t2 intersect select * from t1



-- Результат
-- 10 Акулина
-- 20 Бронислав
-- 30 Богдан
-- 40 Борислав

-- ..........



-- в данном случае мы воспользовались оператором intersect который возвращает нам
-- точное совпадение подмножеств t1 и t2
-- предположим что в таблице t2 в позициях 10 , 30 и 40 русские буквы были заменены английскими
-- как же нам поправить эти строки а так же еще и те строки где произошла похожая помена-замена
-- ответ - с помощью функции translate где первый параметр выражение , второй русский набор символов
-- третий латинский набор символов

update t2 set t2.name = translate(ltrim(rtrim(lower(name))), 'etyopadhkxcbm', 'етуораднкхсвм')



-- аналогичным образом отображаем те данные которые есть в таблице t1 и которых нет в таблице t2

select * from t1 minus select * from t2



-- мы помним что у нас в таблице 1 были дубли - давайте найдем из а затем удалим
-- отображаем дубли c использованием операторов группировки

select * from t1 tt where tt.rowid in (
select min(rowid) from t1 group by tid having count(tid)>1)



-- результат
--30 Богдан 2
--210 Святослав 2

-- воспользуемся аналитическим sql
-- результат

SELECT * FROM
(
SELECT
rowid ri, t1.* , row_number() over(PARTITION BY
tid
, tname
ORDER BY tname) rn FROM t1)
where rn > 1


-- AAAVloAAEAAAAScAAX 30 Богдан 2
-- AAAVloAAEAAAAScAAY 210 Святослав 2


-- удаляем дубли

Delete t1 where t1.rowid in

(
SELECT
rowid ri, t1.* , row_number() over(PARTITION BY
tid
, tname
ORDER BY tname) rn
FROM t1)
where rn > 1)




Просмотров: 500