Как сравнить группы кортежей в SQL

Question

Как сравнить группы кортежей в SQL: рассмотрим следующий пример:

TABLE T1
--------
GROUP     VALUE
-----     -----
A         FOO
A         BAR
X         HHH
X         ZOO

TABLE T2
--------
GROUP     VALUE
-----     -----
B         ZOO
C         FOO
C         BAR

Я хочу написать SQL-запрос, который сравнивает группы значений в обеих таблицах и сообщает о различиях. В проиллюстрированном примере группа в таблице a: ((A,FOO),(A,BAR)) такая же, как группа ((C,FOO),(C,BAR)), даже если имя группы отличается, Считается, что содержание группы одинаково. Наконец, запрос сообщит, что есть разница: это кортеж (B,ZOO).

RESULT
------
GROUP     VALUE
-----     -----
B         ZOO
X         HHH
X         ZOO

Хотя группа X, содержащая ZOO в T1, имеет совпадающее значение в T2: (B,ZOO) она все равно не совпадает, поскольку группа также имеет значение (X, HHH), которое не является частью группы (B,ZOO) в Т2

Answer 1

Что-то вроде этого

create table t1 (group_id varchar2(20), value varchar2(20));
create table t2 (group_id varchar2(20), value varchar2(20));

insert into t1 values ('A','FOO');
insert into t1 values ('A','BAR');
insert into t1 values ('X','HHH');
insert into t1 values ('X','ZOO');
insert into t2 values ('C','FOO');
insert into t2 values ('C','BAR');
insert into t2 values ('B','ZOO');


select t1.group_id t1_group,t2.group_id t2_group, 
      --t1.all_val, t2.all_val, 
       case when t1.all_val = t2.all_val then 'match' else 'no match' end coll_match
from 
  (select 'T1' tab_id, group_id, collect(value) all_val, 
          min(value) min_val, max(value) max_val, count(distinct value) cnt_val 
  from t1 group by group_id) t1
full outer join
  (select 'T2' tab_id, group_id, collect(value) all_val, 
          min(value) min_val, max(value) max_val, count(distinct value) cnt_val 
  from t2 group by group_id) t2
on t1.min_val = t2.min_val and t1.max_val = t2.max_val and t1.cnt_val = t2.cnt_val
/

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

Это говорит вам о совпадениях. Вам просто нужно выдвинуть дополнительный шаг, чтобы найти группы, у которых нет совпадений

select t1_group
from
(
  select t1.group_id t1_group,t2.group_id t2_group, 
        --t1.all_val, t2.all_val, 
         case when t1.all_val = t2.all_val then 'match' end coll_match
  from 
    (select 'T1' tab_id, group_id, collect(value) all_val
    from t1 group by group_id) t1
  cross join
    (select 'T2' tab_id, group_id, collect(value) all_val
    from t2 group by group_id) t2
)
group by t1_group
having min(coll_match) is null
/

select t2_group
from
(
  select t1.group_id t1_group,t2.group_id t2_group, 
        --t1.all_val, t2.all_val, 
         case when t1.all_val = t2.all_val then 'match' end coll_match
  from 
    (select 'T1' tab_id, group_id, collect(value) all_val
    from t1 group by group_id) t1
  cross join
    (select 'T2' tab_id, group_id, collect(value) all_val
    from t2 group by group_id) t2
)
group by t2_group
having min(coll_match) is null
/

Answer 2

Разница между T1 и T2 (две таблицы) может быть такой:

SELECT
   T1.GROUPNAME,
   T1.VALUE
FROM 
   T1
LEFT JOIN T2
ON T2.Value = T1.Value
WHERE T2.GROUPNAME IS NULL

Например, T1 имеет:

Foo 100 Bar 200 ZZZ 333

И T2 включает в себя: Foo 100 Bar 200

Результатом этого запроса является ZZZ 333, это единственная запись, которая не совпадает в обеих таблицах. Вы могли бы даже изменить название группы T2, чтобы сказать:

XYZ 100 ZXZ 200

И результат по-прежнему составляет ZZZ 333. Это соответствует тому, что вы просите, если вы хотите противоположного, вы можете либо UNION к нему, либо использовать RIGHT join.

Джон