时间戳

首页 > 解决方案 > 生成组合并确定出现最多的组合

mysql - 生成组合并确定出现最多的组合

问题描述

我有一个有 3 列的表。我需要生成基于特定“诊断”执行的表的值（在“测试”列中）的组合（总是在 3 中）。然而，一个特定的诊断可能有 2 个或更少的测试，在这种情况下，逻辑仍然会输出该组合，尽管有 2 个值。参考下表，对于每个 cust_id 都有一个“诊断”列，基于该列执行“测试”。现在对于每个诊断值组，我需要在“测试”列中生成相应值的唯一组合。请注意，组合应始终具有 3 个值（其中值 >= 3），但对于少于 3 个值（1 或 2）的诊断，

病人：

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

1003 | Bronchitis | X-Ray

因此，pat_id = '1001'我们diagnosis = 'Thyroid'看到“测试”有 3 个不同的值。因此，只有 1 个独特的组合是可能的，即{CAT, MRI, Blood}. 同样，对于pat_id = '1001' and diagnosis = 'Tonsil'，我们看到“test”列中有 4 个不同的值。因此，将有 4 种组合，即{CAT, MRI, Blood}, {CAT, MRI, RAPID}, {MRI, Blood, RAPID} & {CAT, blood, RAPID}. 因为pat_id = '1002'只有两个唯一值存在。因此，组合将只有 1 即{MRI, Eliza}。同样，对于，pat_id = '1003'只有 1 个值，即 X 射线，因此输出应该是{X-Ray}对于'1003'。

像这样，我需要为组中的所有诊断值生成相似的组合，最后，确定在该表中出现最大次数的唯一组合。输出应该是表格中出现次数最多的组合。

到目前为止，下面的 sql 返回所有具有 3 个或更多值的组合。但它无法输出小于 3 个值的值。意思1002 & 1003是，没有被输出，因为它们的值少于 3 个，但需要输出。解决方案也需要处理此类情况。

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 ;

另请说明我如何确定哪种组合出现最多。谢谢。

标签： mysqlsql

解决方案

您可以将查询与左连接一起使用，以允许第二个和第三个测试为 NULL。但是，您将需要为具有两个或多个测试的组删除带有 NULL 的行。您可以使用相关（依赖）COUNT(*)子查询来实现：

select
  p1.pat_id,
  p1.diagnosis,
  p1.tests as test1,
  p2.tests as test2,
  p3.tests as test3
from patient p1
left join patient p2
  on  p2.diagnosis = p1.diagnosis
  and p2.pat_id = p1.pat_id
  and p2.tests > p1.tests
left join patient p3
  on  p3.diagnosis = p1.diagnosis
  and p3.pat_id = p1.pat_id
  and p3.tests > p2.tests
where
  case (
    select count(*)
    from patient p
    where p.diagnosis = p1.diagnosis
      and p.pat_id = p1.pat_id
  )
    when 1 then true
    when 2 then p2.tests is not null
    else        p3.tests is not null
  end
order by p1.pat_id, p1.diagnosis

结果：

| pat_id | diagnosis  | test1 | test2 | test3 |
| ------ | ---------- | ----- | ----- | ----- |
| 1001   | Thyroid    | Blood | CAT   | MRI   |
| 1001   | Tonsil     | CAT   | MRI   | RAPID |
| 1001   | Tonsil     | Blood | MRI   | RAPID |
| 1001   | Tonsil     | Blood | CAT   | MRI   |
| 1001   | Tonsil     | Blood | CAT   | RAPID |
| 1002   | Pneumonia  | Eliza | MRI   |       |
| 1003   | Bronchitis | X-Ray |       |       |

在 DB Fiddle 上查看

要按出现次数对不同组合进行排序，只需将其修改为GROUP BY ... ORDER BY COUNT(*)查询：

select 
  p1.tests as test1,
  p2.tests as test2,
  p3.tests as test3,
  count(*) as cnt
from patient p1
left join patient p2
  on  p2.diagnosis = p1.diagnosis
  and p2.pat_id = p1.pat_id
  and p2.tests > p1.tests
left join patient p3
  on  p3.diagnosis = p1.diagnosis
  and p3.pat_id = p1.pat_id
  and p3.tests > p2.tests
where
  case (
    select count(*)
    from patient p
    where p.diagnosis = p1.diagnosis
      and p.pat_id = p1.pat_id
  )
    when 1 then true
    when 2 then p2.tests is not null
    else        p3.tests is not null
  end
group by p1.tests, p2.tests, p3.tests
order by cnt desc

结果：

| test1 | test2 | test3 | cnt |
| ----- | ----- | ----- | --- |
| Blood | CAT   | MRI   | 2   |
| CAT   | MRI   | RAPID | 1   |
| Blood | MRI   | RAPID | 1   |
| Eliza | MRI   |       | 1   |
| X-Ray |       |       | 1   |
| Blood | CAT   | RAPID | 1   |

在 DB Fiddle 上查看

推荐阅读