Oracle ListaGG，按ID分组的一个列中出现最频繁的前三个值

Question

我有一个关于 SQL 查询的问题，它可以在“普通”SQL中完成，但我确定需要使用一些组合连接（无法使用 MySQL），所以第二个选项是 ORACLE 方言，因为将使用 Oracle 数据库。假设我们有以下实体：

表：兽医访问

Visit_Id, 
Animal_id, 
Veterinarian_id, 
Sickness_code

假设有100次访问（100个visit_id），每个animal_id大约访问20次。我需要创建一个按Animal_id分组的SELECT查询，其中包含3列：

1. animal_id 2. 第二列显示特定动物的流感访问总数（假设流感，疾病代码=5） 3. 第三列显示每个动物的前三个疾病代码（针对该特定animal_id最常见的3个代码）

如何实现？第一和第二列很容易，但是第三列呢？我知道我需要使用Oracle中的LISTAGG、OVER PARTITION BY、COUNT和RANK，我尝试将它们结合在一起，但结果并不如我所预期的那样:(这个查询应该长什么样呢？

- helloWorldHelloUniverse

---

你好！欢迎来到StackOverflow！您能够发布一小组样本数据以及您想要输出的示例吗？ - dvsoukup

请阅读如何提问这里是一个很好的起点，可以学习如何提高问题质量并获得更好的答案。开始 - Juan Carlos Oropeza

如何创建一个最小、完整和可验证的示例例如，不要使用100个访问，而是使用10个。有两个动物，每个动物都有5次访问。并显示前2个结果的结果。 - Juan Carlos Oropeza

在出现并列的情况下，期望的输出是什么？例如，如果第三和第四最常见的代码都出现了11次，应该选择哪个代码？另外，如果有两个代码并列第一名，应该先显示哪个？ - user5683823

Answer 1

我认为最自然的方法是使用两个级别的聚合，加上一些窗口函数：

select vas.animal,
       sum(case when sickness_code = 5 then cnt else 0 end) as numflu,
       listagg(case when seqnum <= 3 then sickness_code end, ',') within group (order by seqnum) as top3sicknesses
from (select animal, sickness_code, count(*) as cnt,
             row_number() over (partition by animal order by count(*) desc) as seqnum
      from visits
      group by animal, sickness_code
     ) vas
group by vas.animal;

这是利用listagg()忽略NULL值的事实。

- Gordon Linoff

---

优雅的解决方案，忽略NULL并保存子查询（+1）。请在row_number中修复order by count(*)。（cnt未定义） - Marmite Bomber

Answer 2

这是一个样本数据。

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;

查询

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

聚合计数并计算流感计数（在动物的所有记录中）

计算RANK（如果您只需要3条记录，请使用ROW_NUMBER-请参见下面的讨论）

过滤前3个RANK

LISTAGG聚合结果

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) 

我故意展示 Sickness_code（访问次数）以证明前三名可能会并列，您需要处理这种情况。 请检查 RANK 函数。在这种情况下使用 ROW_NUMBER 不是确定性的。

- Marmite Bomber