制定查询优先级队列表的 SQL

Question

我正在实现一个小队列来处理哪个进程首先运行。 我正在使用数据库中的表来执行此操作。 这是表的结构（我在 SQLite 中模拟它）：

"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"identifier" VARCHAR NOT NULL ,
"priority_number" INTEGER DEFAULT 15,
"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
"description" VARCHAR

我正在尝试编写 SQL 来给出接下来可以运行哪个进程的行。 这是一些示例数据：

id  identifier  priority_number timestamp             description
1   test1       15              2009-01-20 17:14:49   NULL
2   test2       15              2009-01-20 17:14:56   NULL
3   test3       10              2009-01-20 17:15:03   NULL
4   test4       15              2009-01-20 17:15:08   NULL
5   test5       15              2009-01-20 17:32:23   NULL
6   test6       14              2009-01-20 17:32:30   NULL
7   test7       7               2009-01-20 17:32:38   NULL
8   test8       20              2009-01-20 17:32:57   NULL
9   test9       7               2009-01-21 13:47:30   NULL
10  test10      15              2009-01-21 13:50:52   NULL

如果我使用此 SQL，我可以按正确的顺序获取数据：

select * from queue_manager order by priority_number, timestamp;

这将为我提供顶部优先级编号最低（最重要）的项目，并且在这些优先级编号中，最早进入的项目顶部的队列（按时间戳）。

我可以运行这个查询，并且只获取第一行，但我宁愿使用 SQL 查询来执行此操作，该查询将为我提供位于队列顶部的进程的一行（在上面的示例数据中，该行id=7）。

我尝试进行自连接和子查询，但我一定有心理障碍 - 我似乎无法做到正确。

提前致谢！

编辑

我忘了提及我正在寻找独立于数据库的查询。 我在 SQlite 中模拟了这一点，但我很有可能在 DB2 或 Oracle 中实现它。 我曾想过在查询中使用“limit 1”类型运算符，但这在不同的数据库引擎之间是不同的。

Answer 1

看看这是否有效：

select * from queue_manager where priority_number = 
(select min(priority_number) from queue_manager) and  
timestamp = (select min(timestamp) 
from queue_manager qm2 
where qm2.priority_number = queue_manager.priority_number)

Answer 2

select * from queue_manager order by priority_number, timestamp LIMIT 1;

至于所谓的“数据库独立性”，对于大多数现实世界的任务来说这是一个神话。 通常，您甚至无法以独立于数据库的方式创建模式。

Answer 3

如果你希望它在 InnoDB 之类的东西上是“并发安全”的，请执行以下操作：

1）添加一个“in_progress”字段。

2) 关闭 AUTOCommit

3) SELECT * FROM queue_manager where in_progress = 0 order bypriority_number, timestamp LIMIT 1 FOR UDPATE;

4）更新queue_manager SET in_progress = 1，其中id = X；

5) 承诺

6) 完成工作。 然后在完成满意后删除该行。 有一个“主进程”处理/重新委托/清理旧的“进行中”作业。

Answer 4

做到这一点的最佳方法是依赖于数据库； 与游标或其他结构的所有开销相比，为不同的目标 DBMS 使用不同的检索过程要简单得多。

Answer 5

在不同类型的 SQL 中，选择有限数量的行的方式有所不同，因此根据您使用的 SQL 类型，可能有内置的方法来执行此操作。 例如，在 MS SQL Server 中：

SELECT TOP 1
     identifier,
     priority_number,
     timestamp,
     description
FROM
     dbo.Queue_Manager
ORDER BY
     priority_number,
     timestamp

要在 ANSI 兼容 SQL 中执行此操作，应使用以下方法：

    SELECT
         QM1.identifier,
         QM1.priority_number,
         QM1.timestamp,
         QM1.description
    FROM
         Queue_Manager QM1
    LEFT OUTER JOIN Queue_Manager QM2 ON
         QM2.priority_number < QM1.priority_number OR
         (QM2.priority_number = QM1.priority_number AND QM2.timestamp < QM1.timestamp)
    /* If you're concerned that there might be an exact match by priority_number
and timestamp then you might want to add a bit more to the join */
    WHERE
         QM2.identifier IS NULL

或者您可以尝试：

SELECT
     QM1.identifier,
     QM1.priority_number,
     QM1.timestamp,
     QM1.description
FROM
     Queue_Manager QM1
INNER JOIN
     (
          SELECT
               priority_number
               MIN(timestamp) AS timestamp,
          FROM
               Queue_Manager
          WHERE
               priority_number = 
                    (
                         SELECT
                              MIN(priority_number)
                         FROM
                              Queue_Manager
                    )
          GROUP BY
               priority_number
     ) SQ1 ON
          SQ1.priority_number = QM1.priority_number AND
          SQ1.timestamp = QM1.timestamp

这两种方法都不能解释priority_number 和时间戳中的精确匹配，因此，如果您认为这是可能的（甚至可能您不需要）您需要添加一两行以使用标识符或其他保证唯一性的内容进入一个更高的级别。 或者只是编写前端来处理偶尔返回两行的情况（也许只是忽略第二行 - 下次你会得到它）。

测试每种方法，看看哪种方法更适合您。

另外，您预计队列有多大？ 仅使用 ORDER BY 进行查询并且仅让前端检索第一行可能是合理的。

Answer 6

阅读此部分并选择为您提供最合适兼容性的变体。 游标的使用可能是唯一或多或少普遍兼容的方式，但会带来一些性能损失，可能不值得（配置文件！）。

Answer 7

关系数据库不擅长管理队列。

尝试查看 Windows 世界中的 MSMQ、Java 世界中的 ActiveMQ 或商业世界中的 Websphere MQ。

这些产品只做一件事情，管理队列，但它们做得很好。