当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Distribute work evenly based on field value

问题描述:

I have a legacy system which uses a set of robots, (automated processes), to collect jobs which are placed in 'inboxes'. The work is distributed into 'outboxes' for other robots to process. Each job is represented by a row in the table. Each row has an id which represents the box. Each job has a priority which represents the order in which the output robot will perform the work.

Today the jobs in inbox 1 is distributed evenly to each of the outboxes using a cursor, inbox 2 is processed next again using a cursor and so on through each inbox, priority is not considered so one outbox may have many high priority and the other outboxes will not have any high priority jobs.

What I would like to find is a way to eliminate the cursor and distribute the jobs based on how many jobs are in the outbox which have the same priority as the inbox.

 Start

id box_name priority

1 in_10 0

2 in_10 0

3 in_10 0

4 in_10 0

5 in_10 0

6 in_10 0

7 in_10 0

8 in_10 0

9 in_10 0

10 in_25 0

11 in_25 0

12 in_25 0

13 in_25 0

14 in_25 0

15 out_1 10

16 out_2 10

17 out_2 10

18 out_2 25

The work is moved from "in" to out. The priority field is updated based on the number of the "in" box.

In the case above there are a total of 12 items which have a "10" priority. The items are id: (1,2,3,4,5,6,7,8,9,15,16,17). The 12 items, 9 of which are "in" and 3 are "out", will be distributed across two "out" boxes. The target count of items in the out box for priority 10 is 6. Since there is presently one item in box 1 we will move 5 items. Box 2 has 2 items so we will move 4 items.

The logic is repeated for priority 25

AFTER

id box_name priority

1 out_1 10

2 out_1 10

3 out_1 10

4 out_1 10

5 out_1 10

6 out_2 10

7 out_2 10

8 out_2 10

9 out_2 10

10 out_1 25

11 out_1 25

12 out_1 25

13 out_2 25

14 out_2 25

15 out_1 10

16 out_2 10

17 out_2 10

18 out_2 25

Here is the code which creates a sample table and what I have started with. i am stuck on how to write the update statements.

declare @start table(id int identity(1,1), box_name char(10), priority int)

Insert @start (box_name, priority)

VALUES

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 10),

('in', 25),

('in', 25),

('in', 25),

('in', 25),

('in', 25),

('out_1', 10),

('out_2', 10),

('out_2', 10),

('out_2', 25)

--select * from @start

select distinct

[box_name], [priority]

,COUNT([box_name]) OVER (PARTITION BY [box_name],[priority] ) AS [count_source_by_priority]

,count([priority])OVER (PARTITION BY [priority] ) AS [Total_by_priority]

from @start

网友答案:

I was able to accomplish this in a set based fashon. I used the "ntile" in TSQL SQLServer2K8R2.

declare @start table(id int identity(1,1), box_name char(10), priority int) 
Insert @start (box_name, priority) 
VALUES 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         10), 
('in',         25), 
('in',         25), 
('in',         25), 
('in',         25), 
('in',         25), 
('out_1',         10), 
('out_2',         10), 
('out_2',         10), 
('out_2',         25) 

--select * from @start
--start of solution

--create a work table to hold the "out" box names
declare @out_box table (ob_id int identity(1,1), ob_name varchar(5))
--fill the work table
Insert @out_box
Select distinct box_name from @start where box_name != 'in'
--show the work table
select * from @out_box
--calculate the number of "out"boxes
declare @#ofOutBoxes int
select @#ofOutBoxes = MAX(ob_id) from @out_box
--show the calculated value
select @#ofOutBoxes
--reset all the work to "in" box
update @start set box_name = 'in' 
--create a table to hold the work with the newly associated "out" box
declare @start1 table(s1_id int identity(1,1), s1_s_id int, s1_box_name char(10), s1_priority int, s1_ntilevalue int, s1_targetRobot varchar(5)) 
Insert @start1 (s1_s_id,s1_box_name, s1_priority, s1_ntilevalue)
Select
id, box_name,priority,
NTILE(@#ofOutBoxes) OVER(PARTITION BY priority ORDER BY priority) AS 'ntilevalue' 
from @start 
--show the data
select * from @start1

---join the @start1 table and the @out_box table on the ntile value
UPDATE @Start1
SET s1_targetRobot = ob_name
from @start1 
inner join 
 @out_box 
 on s1_ntilevalue = ob_id

Select * from @Start1
--Use the joined table to update the @start table
UPDATE @Start
SET box_name = s1_targetRobot
FROM @Start
inner join 
 @Start1
 on id = s1_s_id

 Select * from @Start order by priority
分享给朋友:
您可能感兴趣的文章:
随机阅读: