CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
KenD
KenD

Reputation: 5318

SQL Ordering records by "weight"

We have a system that processes records by a "priority" number in a table. We define the priority by the contents of the table, e.g. 

UPDATE table
SET priority=3
WHERE processed IS NULL

UPDATE table
SET priority=2
WHERE balance>50

UPDATE table
SET priority=1
WHERE value='blah'

(please ignore the fact that there could be 'overlaps' between priorities :) )

This works fine - the table is processed in priority order, so all the rows where the column "value" is 'blah' are worked first.

I've been given the task of adding an option to order the records by a definable "weight". For example, we'd like 50% of the processing to be priority 1, 25% priority 2 and 25% priority 3. Therefore, from the above, in every 100 records 50 of them would be ones where "value" is 'blah", 25 of them would be where "balance" is greater than 50 etc.

I'm trying to figure out how to do this: some kind of weighted incrementing value for "priority" would seem to be the best way, but I can't get my head around how to code this. Can anyone help please?

EDIT: Apologies, should have said: this is running on MSSQL 2008

Upvotes: 5

Views: 3900

Answers (2)

ThinkJet
ThinkJet

Reputation: 6735

General idea is to collect tasks into buckets, divided on border of whole numbers:

select
  task_id
from (  
  select 
    task_id, 
    ((task_priority_order - 1) / task_priority_density) as task_processing_order
  from (
    select
      t.task_id                                            as task_id, 
      t.priority                                           as task_priority, 
      row_number() 
        over (partition by t.priority order by t.priority) as task_priority_order,
      case
        when t.priority = 3 then 50
        when t.priority = 2 then 25
        when t.priority = 1 then 25
      end                                                  as task_priority_density
    from
      table t
  )
)
order by task_processing_order

In the diapason from 0.0 to 0.(9) we got 100 records constructed from first 50 records with priority 3, first 25 records with priority 2 and first 25 records with priority 1.

The next diapason from 1.0 to 1.(9) represents next bucket of records.

If no more tasks with some value of priority then remaining tasks will be placed in buckets in same ratio. E.g. if not enough tasks with priority 3 then remaining tasks will be arranged with ratio of 50/50.

task_id - some surrogate key for task identification.

P.S. Sorry, I can't test this query now, so any syntax correction very appreciated.

Update: Query syntax corrected according to comments.

Upvotes: 5

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

Given test script provides the following output. If you would lay out some rules about what the end result should be, I'm willing to take another look at it.

Results

Priority    Processed       Balance Value
3           NULL            NULL    NULL
NULL        0               49      NULL
NULL        1               49      NULL
NULL        0               50      NULL
NULL        1               50      NULL
2           0               51      NULL
2           1               51      NULL
2           0               51      Notblah
1           1               51      blah

Test script

DECLARE @Table TABLE (Priority INTEGER, Processed BIT, Balance INTEGER, Value VARCHAR(32))

INSERT INTO @Table VALUES 
  (NULL, NULL, NULL, NULL)
  , (NULL, 0, 49, NULL)
  , (NULL, 1, 49, NULL)
  , (NULL, 0, 50, NULL)
  , (NULL, 1, 50, NULL)
  , (NULL, 0, 51, NULL)
  , (NULL, 1, 51, NULL)
  , (NULL, 0, 51, 'Notblah')
  , (NULL, 1, 51, 'blah')

UPDATE @table SET priority=3 WHERE processed IS NULL
UPDATE @table SET priority=2 WHERE balance > 50
UPDATE @table SET priority=1 WHERE value = 'blah'

SELECT  *
FROM    @table

Upvotes: 0

Related Questions