mysql：计算venn图集幂的有效方法

yyyllmsg 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(281)

给出4张表，每个表包含项目并代表一组，如何获得绘制维恩图所需的每个隔间中的项目计数，如下所示。计算应该在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    |
+------+    +------+    +------+    +------+
| ...  |    | ...  |    | ...  |    | ...  |

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

另一个查询是2。

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

以此类推，产生15个查询。

mysql venn-diagram set-intersection

来源：https://stackoverflow.com/questions/53234943/mysql-efficient-way-computing-set-powers-of-venn-diagram

3条答案

按热度按时间

ar7v8xwq1#

这个问题有点复杂，所以答案很简单。让我解释一下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

这个 universe 结果是所有表的并集（消除了重复项），类似于

+------+
| 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个区域。

赞(0）回复(0）举报 2021-06-18

ny6fqffe2#

以下程序：
创建了一个存储过程，该过程创建包含集合的临时内存表。
请注意，mysql不允许您在查询中多次引用内存中的临时表。
如前所述，mysql没有 INTERSECT 或者 EXCEPT . 但你可以模仿他们。通过从原始数据/原始集中删除重复项，仿真可以更加简化。
决定将计算出的值存储到一个变量中，每个变量输出一个表，表中包含与组件对应的所有15个值。
我现在想到的是https://gist.github.com/rillke/c2da0921f8f2a047615f41fab8781c11

赞(0）回复(0）举报 2021-06-18

wgmfuz8q3#

尝试以下操作：

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中测试过这个。你可能需要 SET sql_mode='PIPES_AS_CONCAT' 对于在mysql中工作的ansi字符串连接，或者使用 concat 而不是功能。这个 WITH 只有mysql的8.0版本才支持语法，但是您可以适当地使用临时表或嵌套查询。
如果集合非常大，您可能需要索引 item 列，以防sql优化器无法自己找到它。

赞(0）回复(0）举报 2021-06-18

我来回答

mysql：计算venn图集幂的有效方法

3条答案

相关问题

热门标签

最新问答