sql - Oracle ListaGG，前 3 个最常见的值，在一列中给出，按 ID 分组

Question

我有一个关于 SQL 查询的问题，它可以在“普通”SQL 中完成，但我确信我需要使用一些组连接(不能使用 MySQL)所以第二个选项是 ORACLE 方言，因为那里会是 Oracle 数据库。假设我们有以下实体:

表: vert 就诊

Visit_Id, 
Animal_id, 
Veterinarian_id, 
Sickness_code

假设有 100 次访问 (100 visit_id)，每个 animal_id 访问了大约 20 次。

我需要创建一个 SELECT ，按 Animal_id 分组，有 3 列

• 动物编号
• 第二个显示该特定动物的流感就诊总数(假设流感，疾病代码 = 5)
• 第 3 列显示每只动物的前三种疾病代码(针对该特定动物_id 的前 3 种最常见代码)

怎么做？第一列和第二列很容易，但是第三列呢？我知道我需要使用来自 Oracle 的 LISTAGG、OVER PARTITION BY、COUNT 和 RANK，我试图将它们结合在一起，但没有按我预期的那样工作:(这个查询应该是什么样子？

Answer 1

这里是示例数据

create table VET as
select 
rownum+1 Visit_Id, 
mod(rownum+1,5) Animal_id, 
cast(NULL as number)  Veterinarian_id, 
trunc(10*dbms_random.value)+1 Sickness_code
from dual
connect by level <=100;

查询

基本上，子查询执行以下操作:

汇总计数并计算流感计数(在动物的所有记录中)

计算排名(如果您只需要 3 条记录，请使用 ROW_NUMBER - 请参阅下面的讨论)

过滤前 3 个 RANK

LISTAG聚合结果

with agg as (
select Animal_id, Sickness_code, count(*) cnt,
sum(case when SICKNESS_CODE = 5 then 1 else 0 end) over (partition by animal_id) as cnt_flu
from vet
group by Animal_id, Sickness_code
), agg2 as (
select ANIMAL_ID, SICKNESS_CODE, CNT, cnt_flu,
rank() OVER (PARTITION BY ANIMAL_ID ORDER BY cnt DESC) rnk
from agg
), agg3 as (
select ANIMAL_ID, SICKNESS_CODE, CNT, CNT_FLU, RNK
from agg2
where rnk <= 3
)
select 
ANIMAL_ID, max(CNT_FLU) CNT_FLU,
LISTAGG(SICKNESS_CODE||'('||CNT||')', ', ') WITHIN GROUP (ORDER BY rnk)  as   cnt_lts
from agg3
group by ANIMAL_ID 
order by 1;

给予

 ANIMAL_ID    CNT_FLU CNT_LTS                                     
---------- ---------- ---------------------------------------------
         0          1 6(5), 1(4), 9(3)                              
         1          1 1(5), 3(4), 2(3), 8(3)                        
         2          0 1(5), 10(3), 4(3), 6(3), 7(3)                 
         3          1 5(4), 2(3), 4(3), 7(3)                        
         4          1 2(5), 10(4), 1(2), 3(2), 5(2), 7(2), 8(2) 

我故意向 demonstarte 显示 Sickness_code(计数访问)，前 3 名可能有您应该处理的关系。检查 RANK 函数。在这种情况下，使用 ROW_NUMBER 不是确定性的。