sql - SQL唯一组合

Question

我有一个包含三列的表，其中包含一个ID，一个治疗类以及一个通用名称。治疗类别可以映射到多个通用名称。

ID     therapeutic_class       generic_name
1           YG4                    insulin
1           CJ6                    maleate
1           MG9                    glargine
2           C4C                    diaoxy
2           KR3                    supplies
3           YG4                    insuilin
3           CJ6                    maleate
3           MG9                    glargine

我需要先查看治疗类别和通用名称的各个组合，然后再计算有多少患者具有相同的组合。我希望输出包含三列:一列是通用名称的组合，治疗类别的组合以及具有这种组合的患者数量的计数:

Count          Combination_generic                   combination_therapeutic
2              insulin, maleate, glargine                 YG4, CJ6, MG9
1              supplies, diaoxy                           C4C, KR3

Answer 1

通过(therapeutic_class, generic_name)对对匹配患者的一种方法是在所需的输出中创建用逗号分隔的字符串，然后对它们进行分组和计数。为此，您需要一种识别对的方法。请参阅原始问题下的我的评论和我对戈登答案的评论，以了解其中的一些问题。

我在以下解决方案的一些初步工作中进行了此识别。正如我在评论中提到的那样，如果数据模型中已经存在对和唯一ID，那会更好。我即时创建它们。

重要说明:这是假定用逗号分隔的列表不会太长。如果超过4000个字符(或在Oracle 12中大约32000个字符，并且启用了某些选项)，则可以将字符串聚合到CLOB中，但是不能GROUP BY CLOB(通常，在这种情况下不仅如此)，因此方法将失败。一种更可靠的方法是匹配对的集合，而不是它们的某些集合。解决方案更加复杂，除非您的问题需要它，否则我将不介绍它。

with
         -- Begin simulated data (not part of the solution)
         test_data ( id, therapeutic_class, generic_name ) as (
           select 1, 'GY6', 'insulin'  from dual union all
           select 1, 'MH4', 'maleate'  from dual union all
           select 1, 'KJ*', 'glargine' from dual union all
           select 2, 'GY6', 'supplies' from dual union all
           select 2, 'C4C', 'diaoxy'   from dual union all
           select 3, 'GY6', 'insulin'  from dual union all
           select 3, 'MH4', 'maleate'  from dual union all
           select 3, 'KJ*', 'glargine' from dual
         ),
         -- End of simulated data (for testing purposes only).
         -- SQL query solution continues BELOW THIS LINE
     valid_pairs ( pair_id, therapeutic_class, generic_name ) as (
       select rownum, therapeutic_class, generic_name
       from   (
                select distinct therapeutic_class, generic_name
                from   test_data
              )
     ),
     first_agg ( id, tc_list, gn_list ) as (
       select t.id, 
              listagg(p.therapeutic_class, ',') within group (order by p.pair_id),
              listagg(p.generic_name     , ',') within group (order by p.pair_id)
       from   test_data t join valid_pairs p
                           on t.therapeutic_class = p.therapeutic_class
                          and t.generic_name      = p.generic_name
       group by t.id
     )
select   count(*) as cnt, tc_list, gn_list
from     first_agg
group by tc_list, gn_list
;

输出:

CNT TC_LIST            GN_LIST                      
--- ------------------ ------------------------------
  1 GY6,C4C            supplies,diaoxy               
  2 GY6,KJ*,MH4        insulin,glargine,maleate