时间戳

首页 > 解决方案 > 在 SQL 中查找第一个发生的事件

sql - 在 SQL 中查找第一个发生的事件

问题描述

问题

在我们的应用程序中，我们存储了何时应该清理房间的重复事件。任务（事件）被分配给用户并被赋予一个类别。我们使用 SQL 视图 ,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 类房间。如果今天是星期三，那么我希望查询返回类别 1 的三个事件和计划在星期五发生的类别 2 的两个事件（标有 a 的行*。）如果是星期五，那么查询将返回所有五个事件定于周五。

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()

标签： sqldateselectmariadbgreatest-n-per-group

解决方案

一种通常有效的选择是使用子查询进行过滤：

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).

推荐阅读