sql - 如何获取组中 'middle' 值的平均值？

Question

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

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

Answer 1

使用解析函数的另一种选择；

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