HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

What's the best way to list groups of values that are contained in other groups

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thegroupswhatarewaycontainedthatvalueslistother

Problem

For example, starting with a table like this:

create table t as 
select 'A' as x, level as y from dual connect by level<=5
union all 
select 'B' as x, level+2 as y from dual connect by level<=5
union all 
select 'C' as x, level as y from dual connect by level<=3
union all 
select 'D' as x, level+2 as y from dual connect by level<=3;

alter table t add primary key (x, y);

select * from t;

X Y                      
- -
A 1                      
A 2                      
A 3                      
A 4                      
A 5                      
B 3                      
B 4                      
B 5                      
B 6                      
B 7                      
C 1                      
C 2                      
C 3                      
D 3                      
D 4                      
D 5


How do I get this:

SUBSET_X SUPERSET_X 
-------- ---------- 
D        A          
C        A          
D        B


I'm posting my own effort as an answer but wondering if there is some other fancy way, perhaps with analytics or a set operator I don't know about

--edit

My test data unintentionally implies that the sets always consist of consecutive integers - unfortunately this is not true with my real data.

Solution

This does the trick but is not terribly fast:

select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);

Code Snippets

select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);

Context

StackExchange Database Administrators Q#7014, answer score: 3

Revisions (0)

No revisions yet.