在列中生成值的组合

Question

我有一张三栏的桌子。我需要生成基于特定的combinations执行的表的值(在“测试”列中)的(总是在3的中)。关于下表，对于每个cust_id，都有一个"diagnosis"列，根据该列执行"tests"。现在，对于每个诊断值组，我需要在“test”列中生成相应值的唯一组合。注意，组合应该始终有3个值。

病人：

pat_id | diagnosis | tests

1001 | Thyroid | CAT
1001 | Thyroid | MRI
1001 | Thyroid | Blood

1001 | Tonsil  | CAT
1001 | Tonsil  | MRI
1001 | Tonsil  | Blood
1001 | Tonsil  | RAPID

1002 | Pneumonia | MRI
1002 | Pneumonia | Eliza

因此，对于pat_id = '1001'和diagnosis = 'Thyroid'，我们看到“测试”有3个不同的值。因此，只有1唯一组合是可能的，即{CAT, MRI, Blood}。同样，对于pat_id = '1001'和diagnosis = 'Tonsil'，我们看到"test“列中有4个不同的值。因此，将有4个组合，即{CAT, MRI, Blood}、{CAT, MRI, RAPID}、{MRI, Blood, RAPID}和{CAT, blood, RAPID}。对于pat_id = '1002'，只有两个唯一的值。因此，组合将只有1，也就是像这样的{MRI, Eliza}，我需要为一个组中的所有诊断值生成类似的组合，并输出唯一的组合，该组合的最大次数要比该表中的其他组合多。

注意，在任何时候都应该使用3个值进行组合。

这个表中有关于25 Mil记录的内容。那么，在不影响性能的情况下，有什么方法可以在MySQL中有效地实现这一点呢？

PS:如果需要，我们有一个python环境，如果需要的话，可以通过从csv文件读取数据来实现这个环境。

Answer 1

您可以使用自连接：

select p1.pat_id, p1.diagnosis, p1.tests, p2.tests, p3.tests
from patient p1 join
     patient p2
     on p1.pat_id = p2.pat_id and p1.diagnosis = p2.diagnosis and
        p1.tests < p2.tests join
     patient p3
     on p2.pat_id = p3.pat_id and p2.diagnosis = p3.diagnosis and
        p2.tests < p3.tests ;

即使在(pat_id, diagnosis, tests)上使用推荐的索引，由于查询将产生的数据量很大，这也会很慢。