按“权重”排序SQL记录

Question

我们有一个按照表格中的“优先级”数字处理记录的系统。我们通过表格内容来定义优先级，例如：

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'

(请忽略优先级之间可能存在的“重叠”：))

这个工作得很好 - 表格按照优先级顺序处理，因此所有列“value”为'blah'的行都是首先被处理的。

我被赋予了通过定义的“权重”对记录进行排序的任务。例如，我们希望50％的处理是优先级1，25％的处理是优先级2，25％的处理是优先级3。因此，从上面的例子中，每100个记录中有50个记录的“value”为'blah'，25个记录的“balance”大于50等等。

我正在尝试弄清楚如何做到这一点：一些加权递增值似乎是最好的方式，但我无法理解如何编写代码。有人能帮忙吗？

编辑：抱歉，应该说：这是在MSSQL 2008上运行。

- KenD

---

你使用的是哪个版本的SQL？ - Curtis

我不确定我理解了。您需要按照您描述的顺序检索记录还是在表中更新它们？ - Lieven Keersmaekers

你如何确定优先级2（平衡>50）可能会被改为1？ - Lieven Keersmaekers

我仍然在努力解决边缘情况的问题。如果您的表中只有4个优先级为3的记录，那么应该将哪一个切换到2，哪一个切换到1呢？ - Lieven Keersmaekers

记录的数量是否在处理过程中经常变化？进行处理的是存储过程还是外部进程？ - StevieG

Answer 1

一般的想法是将任务收集到桶中，按整数边界划分：

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

在从0.0到0.(9)的范围内，我们使用优先级3的前50个记录、优先级2的前25个记录和优先级1的前25个记录来构建了100条记录。

接下来从1.0到1.(9)的范围代表了下一个记录桶。

如果没有更多带有某个优先级值的任务，则剩余的任务将按照相同比例放置在桶中。例如，如果没有足够的优先级为3的任务，则剩余的任务将按照50/50的比例排列。

task_id-用于任务识别的一些替代键。

P.S. 抱歉，我现在无法测试此查询，因此非常感谢任何语法纠正。

更新：根据评论纠正了查询语法。

- ThinkJet

---

我在CASE语句中将priority_density改为了task_priority_density，并将row_number更改为row_number() over (partition by t.priority ORDER BY t.priority) -1 as task_priority_order——不确定是否正确，但如果我不这样做，它会报错。现在它似乎在测试表中运行得很好；我需要对它进行修改以使其适用于生产系统，但这让我完成了90%的工作。非常感谢！ - KenD

Answer 2

给定的测试脚本提供了以下输出。如果您能制定一些关于最终结果的规则，我愿意再次查看它。

结果

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

测试脚本

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

- Lieven Keersmaekers