生成组合并确定哪些是最常见的

Question

我有一张三栏的桌子。我需要生成基于特定“诊断”执行的表的值(在“测试”列中)的组合(总是在3中)。然而，一个特定的诊断有可能有两个或更小的测试，在这种情况下，逻辑仍然会输出该组合，尽管有两个值。关于下表，每个cust_id都有一个“诊断”列，根据该列执行“测试”。现在，对于每个诊断值组，我需要在“test”列中生成相应值的唯一组合。注意，组合应该始终带有3个值(其中值为>= 3)，但是对于小于3个值(1或2)的诊断，相应的组合仍然应该是输出(可用的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个不同的值。因此，只有一个唯一的组合是可能的，即{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'只有一个值，即X射线，因此输出应该是'1003'的{X-Ray}。

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

到目前为止，下面的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 ;

此外，请说明我如何能够识别哪个组合发生最多。谢谢。

Answer 1

您可以使用左联接的查询来允许第二次和第三次测试为空。但是，对于具有两个或多个测试的组，则需要删除带有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的看法

Answer 2

我想你想要left join和group by

select p1.tests, p2.tests, p3.tests, count(*)
from patient p1 left join
     patient p2
     on p1.pat_id = p2.pat_id and p1.diagnosis = p2.diagnosis and
        p1.tests < p2.tests left join
     patient p3
     on p2.pat_id = p3.pat_id and p2.diagnosis = p3.diagnosis and
        p2.tests < p3.tests
group by p1.tests, p2.tests, p3.tests
order by count(*) desc;

我不确定diagnosis是否也应该是结果集的一部分。正如你描述的结果，这似乎不是，但对我来说，这是有意义的。