Знову аналітичні функції, Інші СУБД, Бази даних, статті

Балансування навантаження на співробітників – знову аналітичні функції


Том,

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


Відповідь Тома Кайта


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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*