Снова аналитические функции

Источник: ln

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

Том,

У меня возникла следующая проблема с оператором 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. Хотя этого не надо делать - надо только забрать кредиты у перегруженных сотрудников и передать для управления менее загруженным, не отбирая у них те кредиты, с которыми они уже работают.

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

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


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=23849