分组并从结果中排除最小值和最大值 [英] Group by and exclude minimum and maximum from results

问题描述

我有类似以下的数据：

Date        ID          Amount
10-Jun-14   978500302   163005350
17-Jun-14   978500302   159947117
24-Jun-14   978500302   159142342
1-Jul-14    978500302   159623201
8-Jul-14    978500302   143066033
14-Jul-14   978500302   145852027
15-Jul-14   978500302   148595751

我可以得到这个数据的平均值，不包括最高和最低值？我可以通过执行 GROUP BY ID ，然后 AVG（Amount）获得总体平均值。但是我怎么能这样做而排除最小和最大？

Is there a way in oracle that I can get an average of this data, which excludes the highest and lowest value? I can get the overall average by doing a GROUP BY ID, and then AVG(Amount). But how can I do this while excluding min and max?

推荐答案

最简单的方法是使用分析函数来获得最小和汇总前的最大值：

The easiest way is to use analytic functions to get the minimum and maximum values before aggregating:

select id, avg(amount)
from (select d.*,
             min(amount) over (partition by id) as mina,
             max(amount) over (partition by id) as maxa
      from data d
     ) d
where amount > mina and amount < maxa
group by id;

这篇关于分组并从结果中排除最小值和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！