RaqForum 41 No.
20 View  • 
     ...

From SQL to SPL: Find the superset from the relationship table

 

The MySQL database has two tables: bag table and bag item relationship table. Some bags are supersets of other bags.

bag

id

name

A

Bag A

B

Bag B

C

Bag C

D

Bag D

bag_item

id

bag_id

item_id

1

A

1

2

A

2

3

B

1

4

B

2

5

B

3

6

B

4

7

C

1

8

C

4

9

D

1

10

D

2

11

D

3

Now we need to find the superset for each bag:

base_bag_id

superset_bag_id

A

B

A

D

C

B

D

B

SQL：

SELECT
  base.id AS base_bag_id,
  s.id AS superset_bag_id
FROM bag base
JOIN bag s
   ON s.id <> base.id
  AND NOT EXiSTS (SELECT 1
    FROM bag_item bi
    WHERE bi.bag_id = base.id
      AND NOT EXISTS (SELECT 1
        FROM bag_item si
        WHERE si.item_id = bi.item_id
          AND si.bag_id = s.id
    )
);

Two layers of loop traversal are required here, and SQL requires three layers of nesting combined with JOIN and difficult to understand EXISTS to implement set operations, which is difficult to understand. SPL can easily implement it using two-layer loops combined with intuitive set operations. https://try.esproc.com/splx?471

　A

1

$select * from bag_item.txt

2

=A1.group(bag_id; ~.(item_id):items)

3

=A2.news((b=bag_id, i=items, A2.select( b!=bag_id && i \ items==[]));

b:base_bag_id, bag_id:superset_bag)

A2： Group by bag, but do not aggregate, each group is a set of items including a bag and its contents.

A3： Calculate the target result through a two-layer loop. \ means difference set operation.

Question source：https://stackoverflow.com/questions/78222276/find-supersets-of-association-database-table


 
 •  20 View  •