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

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

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

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

Различия глобального административного доступа и доступа к рабочему пространству в Rational Focal Point.

Маленькие хитрости: автоматическое восстановление вида указателя курсора.

Немного про Deadlock.

API PHP в JavaScript. Краткий обзор PHP.JS.

Создание и удаление таблиц в MS SQL Server.

Главная » Sql