MySQL：计算维恩图集合幂的高效方法

Question

假设有4个表，每个表都包含项目并表示一个集合，如何获得每个区域所需的项目计数，以绘制下面显示的 Venn 图。 计算应在 MySQL 服务器上进行，避免将项目传输到应用程序服务器。

示例表格：

s1:         s2:         s3:         s4:
+------+    +------+    +------+    +------+
| item |    | item |    | item |    | item |
+------+    +------+    +------+    +------+
| a    |    | a    |    | a    |    | a    |
+------+    +------+    +------+    +------+
| b    |    | b    |    | b    |    | c    |
+------+    +------+    +------+    +------+
| c    |    | c    |    | d    |    | d    |
+------+    +------+    +------+    +------+
| d    |    | e    |    | e    |    | e    |
+------+    +------+    +------+    +------+
| ...  |    | ...  |    | ...  |    | ...  |

现在，我想计算一些集合的幂。以下是一些示例，其中I对应于s1，II对应于s2，III对应于s3，IV对应于s4：

如果我将sx重新解释为集合，则会写成：

1. |s1 ∩ s2 ∩ s3 ∩ s4| - 中间的白色25
2. |(s1 ∩ s2 ∩ s4) \ s3| - 关于中心右下角的白色15
3. |(s1 ∩ s4) \ (s2 ∪ s3)| - 底部的白色5
4. |s1 \ (s2 ∪ s3 ∪ s4)| - 蓝色背景上的深蓝色60
5. ... 直到15。

如何在MySQL服务器上高效地计算这些幂？ MySQL是否提供有助于计算的函数？
一种朴素的方法是运行一个查询来计算1。

SELECT count(*) FROM(
SELECT item FROM s1
INTERSECT
SELECT item FROM s2
INTERSECT
SELECT item FROM s3
INTERSECT
SELECT item FROM s4);

还有一个查询需要处理。

SELECT count(*) FROM(
SELECT item FROM s1
INTERSECT
SELECT item FROM s2
INTERSECT
SELECT item FROM s4
EXCEPT
SELECT item FROM s3);

等等，导致了15个查询。

- Rainer Rillke

---

如果有人能令我信服地告诉我使用Postgres会更容易，我会相应地修改问题。它可能应该写成“开源DBMS：...”，但这对于SO来说太广泛了。 - Rainer Rillke

MySQL中没有INTERSECT和EXCEPT。因此，您可以使用其他提供这些功能的关系型数据库管理系统。 - Madhur Bhaiya

@MadhurBhaiya 不知道这个。MariaDB 10.3引入了集合操作。 - Rainer Rillke

当前解决方案：https://gist.github.com/Rillke/c2da0921f8f2a047615f41fab8781c11 - Rainer Rillke

Answer 1

尝试像这样做：

试着这样做：

with universe as (
    select * from s1 
    union
    select * from s2
    union
    select * from s3
    union
    select * from s4
),
regions as (
    select
        case when s1.item is null then '0' else '1' end
        ||
        case when s2.item is null then '0' else '1' end
        ||
        case when s3.item is null then '0' else '1' end
        ||
        case when s4.item is null then '0' else '1' end as Region
    from universe u
    left join s1 on u.item = s1.item
    left join s2 on u.item = s2.item
    left join s3 on u.item = s3.item
    left join s4 on u.item = s4.item
)
select Region, count(*) from regions group by Region

免责声明：我只在SQLite中测试过此方法。如果您想要在MySQL中使用ANSI字符串连接，可能需要执行SET sql_mode='PIPES_AS_CONCAT'命令，或者改用concat函数。MySQL 8.0版本才开始支持WITH语法，但您可以适当使用临时表或嵌套查询代替。如果数据集非常大，建议在查询之前为item列创建索引，以便SQL优化器能够自动处理。

- KT.

Answer 2

这个问题有些复杂，所以答案也是。让我解释一下K.T.的回答。

with universe as (
    select * from s1 
    union
    select * from s2
    union
    select * from s3
    union
    select * from s4
),
regions as (
    select
        case when s1.item is null then '0' else '1' end
        ||
        case when s2.item is null then '0' else '1' end
        ||
        case when s3.item is null then '0' else '1' end
        ||
        case when s4.item is null then '0' else '1' end as Region
    from universe u
    left join s1 on u.item = s1.item
    left join s2 on u.item = s2.item
    left join s3 on u.item = s3.item
    left join s4 on u.item = s4.item
)
select Region, count(*) from regions group by Region

宇宙是所有表的联合结果（消除重复项），类似于：

+------+
| item |
+------+
| a    |
+------+
| b    |
+------+
| c    |
+------+
| d    |
+------+
| e    |
+------+
| ...  |
+------+

然后，s1、s2、s3和s4被连接起来。

+------+---------+---------+---------+---------+
| item | s1.item | s2.item | s3.item | s4.item |
+------+---------+---------+---------+---------+
| a    | a       | a       | a       | a       |
+------+---------+---------+---------+---------+
| b    | b       | b       | b       | NULL    |
+------+---------+---------+---------+---------+
| c    | c       | c       | NULL    | c       |
+------+---------+---------+---------+---------+
| d    | d       | NULL    | d       | d       |
+------+---------+---------+---------+---------+
| e    | NULL    | e       | e       | e       |
+------+---------+---------+---------+---------+
| ...  | ...     | ...     | ...     | ...     |
+------+---------+---------+---------+---------+

将其转换为二进制字符串（0：如果单元格为空；1：否则），称为Region，其中第一个数字对应于s1，第二个数字对应于s2等。

+------+--------+
| item | Region |
+------+--------+
| a    | 1111   |
+------+--------+
| b    | 1110   |
+------+--------+
| c    | 1101   |
+------+--------+
| d    | 1011   |
+------+--------+
| e    | 0111   |
+------+--------+
| ...  | ...    |
+------+--------+

最后按地区进行聚合和分组。

+--------+-------+
| Region | count |
+--------+-------+
| 1111   | 1     |
+--------+-------+
| 1110   | 1     |
+--------+-------+
| 1101   | 1     |
+--------+-------+
| 1011   | 1     |
+--------+-------+
| 0111   | 1     |
+--------+-------+
| ...    |       |
+--------+-------+

请注意，其中元素为0的区域不会出现在结果中，而0000永远不会出现（表示不属于任何集合s1、s2、s3、s4），因此共有15个区域。

- Rainer Rillke

Answer 3

以下是步骤：

1. 创建了一个存储过程，该过程创建包含集合的临时内存表。
2. 请注意，MySQL不允许您在查询中多次引用临时内存表。
3. 正如所指出的那样，MySQL没有INTERSECT或EXCEPT。但是您可以模拟它们。通过从原始数据/原始集合中删除重复项，模拟甚至可以更简化。
4. 决定将计算出的值存储到变量中，并输出由所有15个与组件对应的值组成的表。

我想到的目前是https://gist.github.com/Rillke/c2da0921f8f2a047615f41fab8781c11

- Rainer Rillke