在SQL中查找最先发生的事件

Question

问题所在

在我们的应用程序中，我们存储重复发生的事件，以便在什么时候打扫房间。任务(事件)被分配给用户，并被赋予一个类别。我们使用SQL-view ( cleaning_tasks )获取特定一天的所有计划任务。视图将返回如下内容。

SELECT * FROM cleaning_tasks WHERE scheduled_at = current_date();

> room_id, user_id, category, scheduled_at
>       1,       1,        3,   2020-06-04

现在的问题是，我们希望得到第一个即将到来的事件分组由room_id，user_id和category。

例如，我们有三个房间，第一类必须每天打扫，两个房间，第二类，每周五打扫。如果今天是星期三，那么我希望查询返回类别1的三个事件和类别2的两个事件，这两个事件预定在星期五发生(用*标记的行)。如果是星期五，那么查询将返回预定在星期五的所有五个事件。

room_id, user_id, category, scheduled_at
----------------------------------------
     10,       1,        1,   2020-06-03 * # Wednesday 
     20,       2,        1,   2020-06-03 *
     30,       3,        1,   2020-06-03 *
     10,       1,        1,   2020-06-04   # Thursday
     20,       2,        1,   2020-06-04
     30,       3,        1,   2020-06-04
     10,       1,        1,   2020-06-05   # Friday
     20,       2,        1,   2020-06-05
     30,       3,        1,   2020-06-05
     40,       4,        2,   2020-06-05 *         
     50,       5,        2,   2020-06-05 *

我的尝试

我尝试了以下查询，并获得了正确的结果，但由于GROUP BY，我不确定结果是否总是正确的。SQL视图正在检索的事件是按顺序创建的。

SELECT room_id, user_id, category, scheduled_at
FROM room_cleaning_tasks
WHERE scheduled_at >= current_date()
GROUP BY room_id, user_id, category

我第一次尝试使用MIN，但注意到我得到了错误的结果。可能是GROUP BY造成的。

SELECT room_id, user_id, category, scheduled_at, MIN(scheduled_at)
FROM cleaning_tasks
WHERE scheduled_at >= current_date()
GROUP_BY room_id, user_id, category

我还试图在子查询中使用MIN，但这不起作用。我非常肯定，由于子查询中的MIN，内部联接失败了。

SELECT t.room_id, t.user_id, t.category, t.scheduled_at
FROM (
  SELECT room_id, user_id, category, MIN(scheduled_at) scheduled_at
  FROM cleaning_tasks
  GROUP BY room_id, user_id, category
) upcoming
INNER JOIN cleaning_tasks
  ON t.room_id = upcoming.room_id
  AND t.user_id = upcoming.user_id
  AND t.category = upcoming.category
  AND t.category >= current_date()

Answer 1

一个通常有效的选项是使用子查询进行筛选：

select ct.* 
from cleaning_tasks ct
where ct.scheduled_at = (
    select min(ct1.scheduled_at)
    from cleaning_tasks ct1
    where ct1.room_id = ct.room_id and ct1.scheduled_at >= current_date
)

为了提高性能，请考虑(room_id, scheduled_at)上的索引。

Answer 2

如果我对您的理解是正确的，您希望在今天或之后按房间、用户和类别分组第一个"scheduled_at“日期。

我将使用ROWNUMBER() OVER (分区)，并将其放入子查询中，以便进行筛选。

例如：

SELECT SUB.*
FROM (
      SELECT ROOM_ID
      , USER_ID
      , CATEGORY
      , SCHEDULED_AT
      , ROW_NUMBER() OVER (PARTITION BY ROOM_ID, USER_ID, CATEGORY 
                           ORDER BY SCHEDULED_AT ASC) AS ITEM_NUMBER
      FROM CLEANING_TASKS
      WHERE SCHEDULED_AT >= CURRENT_DATE
      ) SUB
WHERE ITEM_NUMBER = 1