Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Июнь 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : снова аналитические функции


Информационный Канал Subscribe.Ru

Выпуск 40

Уважаемые подписчики рассылки!

Да, рекламная площадка из моей рассылки получилась отличная - предложений нет. "Графа Монте-Кристо из меня не поучилось - придется переквалифицироваться в управдомы..." Есть о чем подумать. В связи с этим сообщаю вам, что после этого "юбилейного" выпуска ведущий рассылки уходит в отпуск до августа.

Напоследок предлагаю небольшой, но, как мне кажется, интересный выпуск, посвященный практическому использованию некоторых аналитических функций. По мотивам ответа Тома Кайта на вопрос, заданный в мае 2003 года. И небольшой стишок Егора Летова, так, для медитации.

Балансировка нагрузки на сотрудников - снова аналитические функции

Том,

У меня возникла следующая проблема с оператором update:

Есть таблица кредитов (loan):

 
state_cd, 
user_id, 
status_cd, 
 ...

И таблица user_state_served:

 
user_id, 
state_cd, 
last_asgnmt_dt

Хотелось бы распределить кредиты равномерно среди сотрудников, имеющих право работать в соответствующем штате. Для этого я изменяю last_asgnmt_dt = sysdate и беру в качестве следующего ответственного за кредит user_id с min(last_asgnmt_dt).

Однако после первоначального распределения нагрузка сотрудников может оказаться разной, в зависимости от количества кредитов, выданных в штате. Как можно было бы взять среднее количество кредитов в штате и поровну распределить их между сотрудниками? Например, в штате TX выдано 400 кредитов. Этим штатом сейчас занимается 4 сотрудника, нагрузка среди которых распределена так:

 
emp1:             150 
emp2:              50 
emp3:              75 
emp4:               0 
нераспределенные: 125.

Я хочу поровну поделить все активные кредиты между 4 сотрудниками.

Ответ Тома Кайта

Как вам такое решение:

 
ops$tkyte@ORA920LAP> create table user_state_served 
  2  as 
  3  select rownum user_id, 'TX' state_cd from all_objects where rownum <= 4 
  4  union all 
  5  select rownum+4 user_id, 'VA' state_cd from all_objects where rownum <= 3; 
Table created. 
 
ops$tkyte@ORA920LAP> create table loan_table 
  2  as 
  3  select 'TX' state_cd, 1 user_id from all_objects where rownum <= 150 
  4  union all 
  5  select 'TX', 2 from all_objects where rownum <= 50 
  6  union all 
  7  select 'TX', 3 from all_objects where rownum <= 75 
  8  union all 
  9  select 'TX', null from all_objects where rownum <= 125; 
Table created. 
 
ops$tkyte@ORA920LAP> insert into loan_table 
  2  select 'VA', 5 from loan_table 
  3  / 
400 rows created. 
 
ops$tkyte@ORA920LAP> select user_id, count(*) from loan_table group by user_id 
  2  / 
   USER_ID   COUNT(*) 
---------- ---------- 
         1        150 
         2         50 
         3         75 
         5        400 
                  125 
 
ops$tkyte@ORA920LAP> create or replace procedure redistribute_state(p_state_cd in varchar2) 
  2  as 
  3      type rowidArray is table of rowid INDEX BY BINARY_INTEGER; 
  4 
  5      l_user_id  dbms_sql.number_table; 
  6      l_rowids   rowidArray; 
  7  begin 
  8 
  9  select b.user_id, a.rid BULK COLLECT into l_user_id, l_rowids 
 10    from ( 
 11  select state_cd, 
 12         ntile(buckets) over ( partition by buckets, state_cd order by state_cd ) idx, 
 13         loan_table.rowid rid 
 14    from loan_table, 
 15         (select count(*) BUCKETS 
 16            from user_state_served 
 17           where state_cd = p_state_cd) 
 18   where state_cd = p_state_cd 
 19         ) A, 
 20         (select user_id, row_number() over ( order by user_id ) rn 
 21            from user_state_served 
 22           where state_cd = p_state_cd ) b 
 23   where a.idx = b.rn; 
 24 
 25   forall i in 1 .. l_rowids.count 
 26       update loan_table set user_id = l_user_id(i) where rowid = l_rowids(i); 
 27  end; 
 28  / 
Procedure created. 
 
ops$tkyte@ORA920LAP> exec redistribute_state( 'TX' ); 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920LAP> exec redistribute_state( 'VA' ); 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920LAP> select user_id, count(*) from loan_table group by user_id 
  2  / 
   USER_ID   COUNT(*) 
---------- ---------- 
         1        100 
         2        100 
         3        100 
         4        100 
         5        134 
         6        133 
         7        133 
7 rows selected.

Поскольку в версии 8i функцию ntile в plsql использовать нельзя, вот как можно изменить это решение для версии Oracle 8i:

 
ops$tkyte@ORA920LAP> rollback; 
Rollback complete. 
 
ops$tkyte@ORA920LAP> select user_id, count(*) from loan_table group by user_id 
  2  / 
   USER_ID   COUNT(*) 
---------- ---------- 
         1        150 
         2         50 
         3         75 
         5        400 
                  125 
 
ops$tkyte@ORA920LAP> create or replace view V 
  2  as 
  3  select b.user_id, a.rid 
  4    from ( 
  5  select state_cd, 
  6         ntile(buckets) over ( partition by buckets, state_cd order by state_cd ) idx, 
  7         loan_table.rowid rid 
  8    from loan_table, 
  9         (select count(*) BUCKETS 
 10            from user_state_served 
 11           where state_cd = userenv('client_info')) 
 12   where state_cd = userenv('client_info') 
 13         ) A, 
 14         (select user_id, row_number() over ( order by user_id ) rn 
 15            from user_state_served 
 16           where state_cd = userenv('client_info') ) b 
 17   where a.idx = b.rn; 
View created. 
 
ops$tkyte@ORA920LAP> create or replace procedure redistribute_state(p_state_cd in varchar2) 
  2  as 
  3      type rowidArray is table of rowid index by binary_integer; 
  4 
  5      l_user_id  dbms_sql.number_table; 
  6      l_rowids   rowidArray; 
  7  begin 
  8      dbms_application_info.set_client_info(p_state_cd); 
  9      select user_id, rid BULK COLLECT into l_user_id, l_rowids 
 10        from v; 
 11 
 12      forall i in 1 .. l_rowids.count 
 13          update loan_table set user_id = l_user_id(i) where rowid = l_rowids(i); 
 14  end; 
 15  / 
Procedure created. 
 
ops$tkyte@ORA920LAP> exec redistribute_state( 'TX' ); 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920LAP> exec redistribute_state( 'VA' ); 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920LAP> select user_id, count(*) from loan_table group by user_id 
  2  / 
   USER_ID   COUNT(*) 
---------- ---------- 
         1        100 
         2        100 
         3        100 
         4        100 
         5        134 
         6        133 
         7        133 
7 rows selected.

Комментарий читателя от 24 июня 2003 года

Том!

Хранимая процедура работает не совсем так, как мне хотелось бы. Например, у меня есть 6 кредитов для в штате TX:

 
user_id         loan_id 
2               1 
3               2 
3               3 
3               4 
3               5 
                6

При выполнении твоей хранимой процедуры, она равномерно распределяет 6 кредитов среди 2 пользователей в группе, но меняет уже выделенные сотруднику задания. Например, в данном случае она может взять кредит с идентификатором 1 и передать его для работы сотруднику с user_id 3. Хотя этого не надо делать - надо только забрать кредиты у перегруженных сотрудников и передать для управления менее загруженным, не отбирая у них те кредиты, с которыми они уже работают.

Ответ Тома Кайта

Ну, идею вы поняли, так почему бы не попытаться решить самому?

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2003 Oracle Corporation

В следующем выпуске

Реструктуризация сайта проекта Open Oracle. Дальнейшее развитие рассылки и, возможно, новые ответы от Тома Кайта. Этот выпуск выйдет, вероятно, уже в начале августа. Я ухожу в отпуск. В свободное время займусь подготовкой новых материалов и этой самой реструктуризацией сайта.

С 6 по 19 июля я буду находиться в урочище Лисья бухта в Крыму. Такой дядька с бородой и в бандане. Можете спросить у Черного полковника, где стоит группа людей из Вышгорода (Киева) и Питера, с детьми. Найдете, если что. Хотя, кому я нужен...

Напоследок обещанная цитата из Егора Летова ("Зря вы это все", альбом "Сто лет одиночества"):

 
Что бы ни случилось - все к лучшему 
Мертвые пчелы оглушительно гудят 
Скошенные травы встают стеной 
Съеденные птички упоительно поют 
 
        А только хорошо смеется тот, кто смеется последним 
        А только хорошо смеется тот, кто смеется последним 
 
Что бы ни случилось - все к лучшему 
Срубленные головы стремительно умнеют 
Реки подо льдом кипят светло и зло 
Выбитые зубы ослепительно скрипят 
 
        А только хорошо смеется тот, кто смеется последним 
        А только хорошо смеется тот, кто смеется последним 
 
Что бы ни случилось - все к лучшему 
Вырванные корни прорастают в небо 
Пойманные рыбы ныряют ввысь 
Срубленные головы пронзительно гудят 
 
        А только хорошо смеется тот, кто смеется последним 
        А только хорошо смеется тот, кто смеется последним 
        А только хорошо смеется тот, кто смеется последним 
        А только хорошо смеется тот, кто смеется последним

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное