如何在 Oracle 中获取排除最小值和最大值的行的平均值?

Question

有五个列。 如何获取行(不是列(的平均值和 平均值应排除最小值和最大值。

如果有重复的最大值或(和(最小值，如何将它们全部排除在外？

我的数据的结果应该是这样的。

Average_MIN_MAX_excluded
-------------------------
3.33333333
5.33333333

我的数据集如下;

WITH DATAA AS
(SELECT 3 c1,5 c2,4 c3,3 c4 ,1 c5 FROM DUAL
UNION
SELECT  1 c1,3 c2,6 c3,9 c4 ,7 c5 FROM DUAL)
SELECT c1, c2, c3, c4, c5 FROM DATAA;

Answer 1

select ((c1 + c2 + c3 + c4 + c5) -
greatest( c1, c2, c3, c4, c5 ) -
least( c1, c2, c3, c4, c5 ))/ 3
from DATAA 

将是一种方式。 这是一个liveSQL链接

Answer 2

这是一个使用LATERAL JOIN又名CROSS APPLY的好地方：

SELECT *
FROM t
CROSS APPLY (
SELECT AVG(c) AS Average_MIN_MAX_excluded
FROM (
SELECT c, ROW_NUMBER() OVER(ORDER BY c) rn
FROM (
SELECT c1 c FROM dual UNION ALL
SELECT c2 FROM dual UNION ALL
SELECT c3 FROM dual UNION ALL
SELECT c4 FROM dual UNION ALL
SELECT c5 FROM dual) 
)
WHERE rn NOT IN (1,5)
) s;

如有必要，此方法可以轻松排除 1,2,3 个最高/最低值。

数据库<>小提琴演示

Answer 3

我认为您可以将unpivot和analytical function与group by一起使用，并average聚合函数，如下所示：

WITH DATAA AS
(
SELECT 3 c1,5 c2,4 c3,3 c4 ,1 c5 FROM DUAL
UNION
-- case with same value at min for two columns
SELECT 1 c1,5 c2,4 c3,3 c4 ,1 c5 FROM DUAL
UNION
SELECT  1 c1,3 c2,6 c3,9 c4 ,7 c5 FROM DUAL)
-- your query starts from here
select rn, avg(val) from
(select rn, val, 
max(val) over (partition by rn) maxval,
min(val) over (partition by rn) minval
from
(SELECT rownum rn, c1, c2, c3, c4, c5
FROM DATAA)
unpivot
(val for vals in (c1,c2,c3,c4,c5)))
where val not in (maxval, minval)
group by rn

请参阅 db<>fiddle demo。

干杯！！