VPF::Вычисление медианы в Oracle 8i - Форум программистов

Sqlninja

Дата 25.12.2007, 15:27 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 353
Регистрация: 15.5.2006
Где: San Francisco, CA

Репутация: 13
Всего: 13

Недавно столкнулся с проблемой. Так как в 8i нет ничего похожего на median() или percentile_cont(), пришлось порядочно извратиться чтобы получить нужный результат.

Итак, задача. Существует некая таблица с данными, вида

Код


create table median_data(f int, d varchar2(2));

d - абстрактный признак партиционирования, в разрезе которого нужно посчитать min(), медиану и max().

По определению:
Медиана (50-й процентиль, квантиль 0,5) — возможное значение признака, которое делит ранжированную совокупность (вариационный ряд выборки) на две равные части: 50 % «нижних» единиц ряда данных будут иметь значение признака не больше, чем медиана, а «верхние» 50 % — значения признака не меньше, чем медиана.

По нормальному:
В отсортированным по возрастанию наборе с нечетным числом элементов {1,2,3,4,5} медиана = 3, в четном наборе {1,2,3,4,5,6} медиана = AVG(3,4)

Заполняем таблицу данными:

Код


begin
   for i in 1 .. 3 loop
      insert into median_data  values (i, 'a');
   end loop;
   for i in 4 .. 6 loop
      insert into median_data  values (i, 'b');
   end loop;
   for i in 7 .. 9 loop
      insert into median_data  values (i, 'c');
   end loop;

   commit;
end;

Результат нужно сохранить в таблице вида:

Код


create table median_res(min number, med number, max number, d varchar2(2));

Вот код который делает то что нужно, ничего лучше придумать не смог:

Код


declare
   l_median   number;
   l_min      number;
   l_max      number;
   l_sql      varchar2 (1000);

   cursor c_partition
   is
      select distinct d
      from            median_data;
begin
   for i in c_partition
   loop
      -- Get median
      l_sql    :=
            'select avg (f)
                 from   (select   f,
                       ROWNUM rn,
                       count (*) over () cnt
              from     median_data
              where    d = '''
         || i.d
         || '''
              order by f)
      where  rn between ROUND (cnt / 2) and DECODE (mod (cnt, 2), 0, cnt / 2 + 1, ROUND (cnt / 2))';

      execute immediate l_sql
      into              l_median;

      -- Get min/max
      select min (f),
             max (f)
      into   l_min,
             l_max
      from   median_data
      where  d = i.d;

      -- Populate results
      insert into median_res
           values (l_min, l_median, l_max, i.d);
   end loop;

   commit;
end;

Ну вот и все.
У кого есть альтернативы, валите всё в этот пост. )

--------------------

It's better to burn out than to fade away.

DimW

Дата 26.12.2007, 11:35 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Цитата(Sqlninja @ 25.12.2007, 15:27

)

d - абстрактный признак партиционирования, в разрезе которого нужно посчитать min(), медиану и max().

из того что я понял с учетом тестовых данных, результат будет такой:

    1    3    3
    4    3    6
    7    3    9
так?
можно немного подробнее описать как вычислить медиану?

Sqlninja

Дата 26.12.2007, 15:09 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 353
Регистрация: 15.5.2006
Где: San Francisco, CA

Репутация: 13
Всего: 13

Нет, результат должен быть другим.

Вот данные:

value | partition
--------------------
1             a
2             a
3              a
4    b
5       b
6            b
7             c
8            c
9             c
10         d

А вот результат:
min      | med |       max |     partition
----------------------------------------------
1    2          3       a
4        5           6         b
7         8           9             c
10       10         10         d

Цитата(Sqlninja @ 25.12.2007, 15:27

)

В отсортированным по возрастанию наборе с нечетным числом элементов {1,2,3,4,5} медиана = 3, в четном наборе {1,2,3,4,5,6} медиана = AVG(3,4)

Короче, медиана - средний элемент упорядоченного массива, либо среднее арифметическое 2-х средних элементов, если кол-во эл-тов нечетное.

--------------------

It's better to burn out than to fade away.

DimW

Дата 27.12.2007, 14:41 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

исходные данные:

Код

результат:

Код


1    2    3    a
4    5,5    7    b
8    10,5    13    c
14    14    14    x

решение:

Код


select min_
      ,avg(f)
      ,max_
      ,d
  from (select f
              ,d
              ,min(f) over(partition by d) min_
              ,(case
                 -- определяю первый - средний элемент
                 when count(*) over(partition by d) / 2 = row_number() over(partition by d order by f) then 'Y'
                 -- определяю второй - средний элемент
                 when count(*) over(partition by d) / 2 + 1 = row_number() over(partition by d order by f) then 'Y'
                 -- если кол-во элементов не четное, определяю средний элемент
                 when trunc(count(*) over(partition by d) / 2) + 1 = row_number() over(partition by d order by f) then 'Y'
                else
                  null
                end) y
              ,max(f) over(partition by d) max_
          from median_data)
 where y = 'Y'
 group by d
         ,min_
         ,max_

на 8i нет возможности проверить, и точно не знаю на сколько в нем развита аналитика...

сервер:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production

Это сообщение отредактировал(а) DimW - 27.12.2007, 18:24

Sqlninja

Дата 28.12.2007, 12:55 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 353
Регистрация: 15.5.2006
Где: San Francisco, CA

Репутация: 13
Всего: 13

отлично! +1

--------------------

It's better to burn out than to fade away.

DimW

Дата 28.12.2007, 13:06 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

сенкс!

если не секрет, для чего это нужно было? это просто задача или ситуация из жизни?

Это сообщение отредактировал(а) DimW - 28.12.2007, 13:09

Sqlninja

Дата 28.12.2007, 13:35 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 353
Регистрация: 15.5.2006
Где: San Francisco, CA

Репутация: 13
Всего: 13

Полностью реальная ситуация - Billing Quality Key Point Identification

--------------------

It's better to burn out than to fade away.

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »