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이 있는 방이 3개 있고 금요일마다 청소해야 하는 카테고리 2가 있는 방이 2개 있다고 합시다.만약 오늘이 수요일이라면, 나는 쿼리가 카테고리 1에 대한 3개의 이벤트와 금요일에 발생할 예정인 카테고리 2에 대한 2개의 이벤트를 반환하기를 원합니다(행은 a로 표시됨).*.) 금요일이면 쿼리는 금요일에 예정된 5개의 이벤트를 모두 반환합니다.

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-view가 검색하는 이벤트는 순차적으로 생성됩니다.

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

일반적으로 효율적인 옵션 중 하나는 하위 쿼리로 필터링하는 것입니다.

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

제대로 이해하고 있다면 오늘 이후의 첫 번째 "scheduled_at" 날짜를 룸, 사용자 및 카테고리별로 그룹화하기를 원하십니다.

ROWNUMBER() OVER(파티션 BY)를 사용하여 서브쿼리에 넣어 필터링할 수 있도록 하겠습니다.

예를 들어,

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

언급URL : https://stackoverflow.com/questions/62176180/find-first-occuring-events-in-sql