Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 подсчет среднего без отклонений  [new]
losaped
Member

Откуда:
Сообщений: 44
Подскажите как посчитать в oracle среднее без отклонений, например, мне нужно посчитать среднее время нахождения документа в подтипе и при этом не учитывать 5% самых долгих и самых коротких документов

пробовал:
avg(doc_quantity) OVER (PARTITION BY SUBTYPE_ID ORDER BY HOUR_SUBTYPE ROWS between 5 FOLLOWING AnD 5 PRECEDING)


это я пытаюсь убрать первые и последние 5 строк, но не получается
25 окт 15, 22:58    [18324557]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 50841
а где в твоём примере 5%?
25 окт 15, 23:30    [18324654]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
losaped
Member

Откуда:
Сообщений: 44
andreymx
а где в твоём примере 5%?

nu мне хотя бы со строками разоюраться)
26 окт 15, 01:06    [18324844]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28823
losaped
среднее время нахождения документа в подтипе и при этом не учитывать 5% самых долгих и самых коротких документов
Ранжируешь, фильтруешь, усредняешь. Никакой высшей атематики. Но методом тыка не выйдет.
26 окт 15, 07:30    [18324990]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 50841
в реальной жизни вопросов будет много

что делать, если самых долгих/коротких документов - 6%?
а если документов всего 2?
26 окт 15, 07:56    [18325031]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5929
losaped
Подскажите как посчитать в oracle среднее без отклонений, например, мне нужно посчитать среднее время нахождения документа в подтипе и при этом не учитывать 5% самых долгих и самых коротких документов

пробовал:
avg(doc_quantity) OVER (PARTITION BY SUBTYPE_ID ORDER BY HOUR_SUBTYPE ROWS between 5 FOLLOWING AnD 5 PRECEDING)


это я пытаюсь убрать первые и последние 5 строк, но не получается


FOLLOWING ... PRECEDING ведут отсчет от текущей позиции, а не от начала "окна". а тебе от начала нужно сколько то отсчитать и от конца. сортируешь по убыванию, убираешь первые строки, потом по возрастанию и снова убираешь первые строки.
26 окт 15, 08:52    [18325121]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28823
mcureenab
сортируешь по убыванию, убираешь первые строки, потом по возрастанию и снова убираешь первые строки.
Сортировать-то два раза зачем?
+
RTFM RATIO_TO_REPORT (FAQ)
26 окт 15, 08:57    [18325131]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5929
Elic
mcureenab
сортируешь по убыванию, убираешь первые строки, потом по возрастанию и снова убираешь первые строки.
Сортировать-то два раза зачем?
+
RTFM RATIO_TO_REPORT (FAQ)
думается, автору надо отбросить 5% и 5% от количества записей, а не от времени ожидания.
26 окт 15, 09:31    [18325251]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
гипотет
Guest
select avg(max(x))
from t
group by rownum
having rownum/nullif(count(*),0) between 0.05 and 0.95;
26 окт 15, 10:16    [18325547]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: подсчет среднего без отклонений  [new]
Alex_SPD
Member

Откуда:
Сообщений: 14
Привет!
Искал такую же штуку, но чтобы можно было бы группировать.

В итоге пришлось разбираться с пользовательскими агрегатными функциями. Привожу код, для тех кто так же поиском сюда выйдет.

-- создаем тип в котором будем копить значения
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%.
Но думаю смысл понятен, можно дорабатывать под себя.
5 апр 19, 15:42    [21854159]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
123йй
Member

Откуда:
Сообщений: 1501
Alex_SPD
Если данных мало, то работать будет плохо.

да и если много, то не очень
Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 
SQL> select text from all_source where owner='SYS' and name = 'ODCINUMBERLIST'
  2   order by line;
TEXT
--------------------------------------------------------------------------------
TYPE ODCINumberList AS VARRAY(32767) of NUMBER

SQL> 
5 апр 19, 15:57    [21854195]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
Alex_SPD
Member

Откуда:
Сообщений: 14
123йй, действительно. Но у меня диапазоны типа 2-3 тысячи, говорю же - частное решение.
Можно все закинуть в arr_float_type() и написать алгоритмическую сортировку, мне побыстрее хотелось. )

В общем можно под себя дорабатывать.
5 апр 19, 16:37    [21854253]     Ответить | Цитировать Сообщить модератору
 Re: подсчет среднего без отклонений  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17182
with t as (select mod(rownum,2) g, rownum val from dual connect by level <= 100)
select g, avg(val) val_avg, count(val) val_cnt 
from (
        select t.*
             , percent_rank() over(partition by g order by val) val_rnk
             , row_number() over(partition by g order by val) 
             / count(*) over(partition by g) row_rnk
        from t)
--where val_rnk between .05 and .95 -- Отбрасываем крайние значения
where row_rnk between .05 and .95 -- Отбрасываем крайние строки
group by g
5 апр 19, 18:02    [21854324]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить