Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH set_sizes AS (
- SELECT x, COUNT(*) AS set_size -- "size" is a reserved keyword in Oracle
- FROM t
- GROUP BY x
- )
- , intersection_sizes AS (
- SELECT
- sub.x sub_x
- , super.x super_x
- , COUNT(*) intersection_size
- FROM
- t sub
- INNER JOIN t super
- ON sub.y = super.y
- AND sub.x < super.x
- GROUP BY
- sub.x
- , super.x
- )
- SELECT ss.x AS sub_x, decode(ss.x,xs.sub_x,xs.super_x,xs.sub_x) AS super_x
- FROM
- set_sizes ss
- INNER JOIN intersection_sizes xs
- ON (ss.x = xs.sub_x OR ss.x = xs.super_x)
- AND ss.set_size = xs.intersection_size
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement