Oracle ListaGG，一列中给出的前3个最常用的值，按ID分组 [英] Oracle ListaGG, Top 3 most frequent values, given in one column, grouped by ID

问题描述

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

I have a problem regarding SQL query , it can be done in "plain" SQL, but as I am sure that I need to use some group concatenation (can't use MySQL) so second option is ORACLE dialect as there will be Oracle database. Let's say we have following entities:

表:兽医来访

Visit_Id, 
Animal_id, 
Veterinarian_id, 
Sickness_code

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

Let's say there is 100 visits (100 visit_id) and each animal_id visits around 20 times.

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

I need to create a SELECT , grouped by Animal_id with 3 columns

• animal_id
• 秒显示了该特定动物的总流感访问量(比如说，流感，病码= 5)
• 第3列显示每只动物的前三种疾病代码(此特定animal_id的前三项是最常见的代码)

该怎么做?第一和第二列很容易，但是第三列呢?我知道我需要使用Oracle的LISTAGG，OVER PARTITION BY，COUNT和RANK，我试图将其绑在一起，但没有按我的预期进行工作:(此查询的外观如何?

How to do it? First and second columns are easy, but third? I know that I need to use LISTAGG from Oracle, OVER PARTITION BY, COUNT and RANK, I tried to tie it together but didn't work out as I expected :( How should this query look like?

推荐答案

我认为最自然的方法是使用两个级别的聚合，以及在此处和此处的一些窗口函数:

I think the most natural way uses two levels of aggregation, along with a dash of window functions here and there:

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值的事实.

This uses the fact that listagg() ignores NULL values.

这篇关于Oracle ListaGG，一列中给出的前3个最常用的值，按ID分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！