Балансировка нагрузки на сотрудников - снова аналитические функции
Том,
У меня возникла следующая проблема с оператором 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. Хотя этого не надо делать - надо только забрать кредиты у перегруженных сотрудников и передать для управления менее загруженным, не отбирая у них те кредиты, с которыми они уже работают.
Ответ Тома Кайта
Ну, идею вы поняли, так почему бы не попытаться решить самому?