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

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

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

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

-- Создаем таблицу 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)

 

 

{jcomments on}