PL/SQL

April 2, 2007

Using Analytics to select by priority and weight

Filed under: example, PL/SQL, pl/sql code, SQL — Michael Moore @ 7:28 pm

This example is a bit contrived. I actually use this technique at work, but I don't want
to give away any company secrets, so I had to change the column names etc.
Here is the setting
You work at the corporate help-desk and it is your job to install new external disk drives on all of the computers that belong to bosses. You can't install the external drives on every computer because you have purchased one per boss for this month. Next month you are going to purchase another batch of hard drives and the month after that another batch and so on. You can only install the external drive on one computer per boss per month. But which computer? To decide this you let each boss prioritize his computers. Highest priority gets the new external drive. For example, Fred has 2 computers, #2000 and #4000. computer 2000 will always be selected and computer #4000 will never be selected given the data shown in the table below. Presumably, at some point, computer #2000 will have all of the external drives it can handle, and the record will be deleted from the table. This will allow Fred's #4000 to be selected. In Fred's case, COMP_WEIGHT is irrelevant. But what if the boss does not care exactly which of is computers gets the external drives first? For example, Mike has 4 computers, #1000,#2000,#3000 and #4000. #4000 is his desktop computer, so he wants to upgrade that one last. #1000,#2000, and #3000 are actually servers and he wants to make sure that they gets upgraded first. He does not care which of these servers get upgraded first, but he does care that they are finished being upgraded about the same time. This is where the COMP_WEIGHT comes in. Mike sets up the following weights for his servers: 29,44, and 72. This means that when a new external drive is available where is a 29 out of 145 chance that it will go to server #1000. There is a 44 out of 145 chance that it will go to server #2000 and there is a 72 out of 145 chance that it will go to #3000. So, weight determines the probability that a given computer will be selected for the current boss/priority.


CREATE TABLE BOSSTABLE
(
BOSS_ID VARCHAR2(10 BYTE),
COMPUTER_ID NUMBER(5),
COMP_PRIORITY NUMBER(5),
COMP_WEIGHT NUMBER(5)
)
;

SET DEFINE OFF;
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('MIKE', 1000, 5, 29);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('FRED', 2000, 5, 61);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('MIKE', 2000, 5, 44);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('MIKE', 3000, 5, 72);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('MIKE', 4000, 4, 100);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('RAVI', 1000, 10, 40);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('RAVI', 2000, 10, 40);
Insert into BOSSTABLE
(BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
Values
('FRED', 4000, 2, 99);
COMMIT;
BOSS_ID COMPUTER_ID COMP_PRIORITY COMP_WEIGHT
FRED 2000 5 61
FRED 4000 2 99
MIKE 1000 5 29
MIKE 2000 5 44
MIKE 3000 5 72
MIKE 4000 4 100
RAVI 1000 10 40
RAVI 2000 10 40



SELECT boss_id
, computer_id
, comp_priority
, comp_weight
FROM ( SELECT boss_id
, computer_id
, comp_priority
, comp_weight
, NVL
( SUM ( weight_total_per_rank ) OVER
( PARTITION BY boss_id, comp_priority
ORDER BY comp_weight
ROWS BETWEEN 1 PRECEDING AND
1 PRECEDING )
, 0 )
AS low_end_of_range
, weight_total_per_rank
, FIRST_VALUE ( rand ) OVER
( PARTITION BY boss_id, comp_priority )
AS the_random_num_we_will_use
FROM ( SELECT boss_id
, computer_id
, comp_priority
, comp_weight
, DENSE_RANK ( ) OVER
( PARTITION BY boss_id
ORDER BY comp_priority DESC )
AS priority_rank
, SUM ( comp_weight ) OVER
( PARTITION BY boss_id
, comp_priority
ORDER BY comp_weight
ROWS UNBOUNDED PRECEDING )
AS weight_total_per_rank
, DBMS_RANDOM.VALUE
( 0
, SUM ( comp_weight ) OVER
( PARTITION BY boss_id
, comp_priority
ORDER BY comp_weight
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING ) )
AS rand
FROM bosstable )
WHERE priority_rank = 1 )
WHERE ( the_random_num_we_will_use >= low_end_of_range
AND the_random_num_we_will_use

First run

BOSS_ID COMPUTER_ID COMP_PRIORITY COMP_WEIGHT
FRED 2000 5 61
MIKE 3000 5 72
RAVI 2000 10 40

Second run

BOSS_ID COMPUTER_ID COMP_PRIORITY COMP_WEIGHT
FRED 2000 5 61
MIKE 2000 5 44
RAVI 2000 10 40
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: