如何获得组中“中间”值的平均值？ [英] How to get average of the 'middle' values in a group?

问题描述

我有一个包含值和组ID的表（简化示例）。我需要获取中间3个值的每组的平均值。因此，如果有1个，2个或3个值，则仅为平均值。但是，如果有4个值，它将排除最高的值，最高和最低的5个值，等等。我在考虑某种窗口函数，但不确定是否可行。

I have a table that has values and group ids (simplified example). I need to get the average for each group of the middle 3 values. So, if there are 1, 2, or 3 values it's just the average. But if there are 4 values, it would exclude the highest, 5 values the highest and lowest, etc. I was thinking some sort of window function, but I'm not sure if it's possible.

http://www.sqlfiddle.com/#!11/af5e0 / 1

此数据：

TEST_ID TEST_VALUE  GROUP_ID
1       5           1
2       10          1
3       15          1
4       25          2
5       35          2
6       5           2
7       15          2
8       25          3
9       45          3
10      55          3
11      15          3
12      5           3
13      25          3
14      45          4

我想要

GROUP_ID    AVG
1           10
2           15
3           21.6
4           45

推荐答案

Anoth er选项，使用分析函数；

Another option using analytic functions;

SELECT group_id,
       avg( test_value )
FROM (
  select t.*,
         row_number() over (partition by group_id order by test_value ) rn,
         count(*) over (partition by group_id  ) cnt
  from test t
) alias 
where 
   cnt <= 3
   or 
   rn between floor( cnt / 2 )-1 and ceil( cnt/ 2 ) +1
group by group_id
;

演示-> http://www.sqlfiddle.com/#!11/af5e0/59

这篇关于如何获得组中“中间”值的平均值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！