同事有个需求,做个评分的功能,要求去掉两个最高分,去掉两个最低分,剩下的求平均分。
oracle有对应的max,min,last,first,可惜,这回是去掉两个,而不是去掉一个,或许人家国外就没有这样的评分方法,呵呵。
写了几种方法,原来是用row_number(),感觉麻烦,相比之下,认为这种方式最简单,借用了一下oracle的lag,lead函数。
SQL> select aname,ascore from a1;
ANAME ASCORE
---------- ----------
李四 7
李四 6
李四 6
李四 4
李四 3
李四 2
张三 8
张三 7
张三 6
张三 5
张三 4
ANAME ASCORE
---------- ----------
张三 3
张三 2
张三 1
李四 8
李四 9
王五 3
王五 4
王五 5
王五 6
王五 7
王五 8.5
ANAME ASCORE
---------- ----------
王五 9
王五 10
已选择24行。
SQL> select aname, avg(ascore)
2 from (select lag(ascore, 2, 0) over(partition by aname order by ascore de
sc) q,
3 lead(ascore, 2, 0) over(partition by aname order by ascore d
esc) h,
4 aid,
5 aname,
6 ascore
7 from a1) t
8 where t.q != 0
9 and t.h != 0
10 group by aname;
ANAME AVG(ASCORE)
---------- -----------
张三 4.5
李四 5.75
王五 6.625
SQL>