Привет!
Искал такую же штуку, но чтобы можно было бы группировать.
В итоге пришлось разбираться с пользовательскими агрегатными функциями. Привожу код, для тех кто так же поиском сюда выйдет.
-- создаем тип в котором будем копить значения
CREATE OR REPLACE TYPE arr_float_type is table of float;
--это костыль, чтобы обойти ограничение на один параметр в агрегатной функции.
CREATE OR REPLACE TYPE avg_timur_param AS VARRAY (3) OF float;
--собственно объект с функцией
CREATE OR REPLACE TYPE t_avg_timur AS OBJECT
(
--type arr_type is table of varchar2(64) index by binary_integer;
arr arr_float_type,
lower_limit float,
upper_limit float,
STATIC FUNCTION ODCIAggregateInitialize(
sctx IN OUT t_avg_timur) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT t_avg_timur,
val IN avg_timur_param) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT t_avg_timur,
ctx2 IN t_avg_timur) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT t_avg_timur,
returnvalue OUT float,
flags IN NUMBER) RETURN NUMBER
)
\
CREATE OR REPLACE TYPE BODY t_avg_timur IS
STATIC FUNCTION ODCIAggregateInitialize(
sctx IN OUT t_avg_timur) RETURN NUMBER
IS
BEGIN
sctx := t_avg_timur(arr_float_type(),0,100);
RETURN ODCIConst.Success;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT t_avg_timur,
val IN avg_timur_param) RETURN NUMBER
IS
BEGIN
if val(1) is not null then --пустые не берем
self.arr.extend(1);
self.arr(arr.count):=val(1);
end if;
self.lower_limit:=val(2);
self.upper_limit:=val(3);
RETURN ODCIConst.Success;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT t_avg_timur,
ctx2 IN t_avg_timur) RETURN NUMBER
IS
BEGIN
RETURN ODCIConst.Success;
END ODCIAggregateMerge;
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT t_avg_timur,
returnvalue OUT float,
flags IN NUMBER) RETURN NUMBER
IS
tmp sys.OdciNumberList := sys.OdciNumberList();
prc arr_float_type;
i number;
res_sum number :=0;
res_cnt number:=0;
BEGIN
--сортируем массив
tmp.extend(self.arr.count);
prc:=arr_float_type();
prc.extend(self.arr.count);
for j in 1..self.arr.count loop
tmp(j) := self.arr(j);
prc(j):=j/self.arr.count;
end loop;
select *
bulk collect
into self.arr
from table(tmp)
order by column_value;
for j in 1..self.arr.count loop
if (prc(j)>=self.lower_limit/100 and prc(j)<=self.upper_limit/100) then --считаем только то, что в середине
res_cnt:=res_cnt+1;
res_sum:=res_sum+self.arr(j);
end if;
end loop;
if res_cnt=0 then
returnvalue:=null;
else
returnvalue := res_sum/res_cnt;
end if;
RETURN ODCIConst.Success;
END ODCIAggregateTerminate;
END;
-- функция которую будем юзать в запросе
CREATE OR REPLACE FUNCTION avg_timur(val avg_timur_param) RETURN float
AGGREGATE USING t_avg_timur;
Использовать примерно так:
где val - это столбец по которому считаем, попадут значения между 25 и 75 процентами. Т.е. отсекаем по 25%.
select name, avg_timur(avg_timur_param(val,25,75))
from test
group by name
На универсальность не претендует, создано для конкретной задачи.
Если данных мало, то работать будет плохо. Например если в наборе 2 значения - первая строка будет считаться 50%, вторая 100%.
Но думаю смысл понятен, можно дорабатывать под себя.