mysql - SQL 结构通过随时间的变化来计算队列的大小

Question

当项目不断地从一个队列移动到另一个队列时，我如何设计一个 SQL 数据结构，让我可以查询随着时间的推移队列有多大(有多少条记录)(这样我就可以绘制它)。

表格:

Queue
--------------------------------------
item_id      int
status       enum('new','ready','old')

Log
--------------------------------------
item_id      int
old_status   enum('new','ready','old')  --perhaps record new_status instead?--
change_date  timestamp

每次项目更改状态(即新队列)时，我都会将其记录在日志中。

我如何编写一个查询来告诉我“在 10:00 有 X 条记录在‘新’中，Y 条记录在‘就绪’中，等等”。全天都这样做，这样我就可以绘制图表。

我不想每 5 分钟制作一个包含总计的表格，因为我无法提前知道我的计时粒度。

我非常愿意更改数据库结构，并添加新字段来完成这项工作。

Answer 1

如果日志同时记录了新状态和旧状态，您可以向表中添加新的伪列，作为每个状态的递增和递减计数器:

SELECT 
    l.change_date
    CASE WHEN l.new_status = 'new'   THEN 1 WHEN l.old_status = 'new'   THEN -1 ELSE 0 END AS new_count
    CASE WHEN l.new_status = 'ready' THEN 1 WHEN l.old_status = 'ready' THEN -1 ELSE 0 END AS ready_count
    CASE WHEN l.new_status = 'old'   THEN 1 WHEN l.old_status = 'old'   THEN -1 ELSE 0 END AS old_count
  FROM log l
  ORDER BY l.change_date

将伪列汇总到特定日期应该让您知道该日期的事件内容。分析 SUM 函数听起来像是合适的工具。这假设旧状态和新状态永远不相同。也许你可以应用类似的东西。