MariaDB: 사례를 사용하여 수행된 작업과 요청 일치(합계 > 0)

MariaDB: 사례를 사용하여 수행된 작업과 요청 일치(합계 > 0)

저는 요청 테이블과 작업 테이블을 가지고 있습니다.요청 유형 1, 2, 4 또는 5의 경우, 6이 효과적으로 +1을 나타내고 7이 -1을 나타내는 유형 6 또는 7의 작업을 합계해야 합니다.요청의 작업 합계가 <=0이거나 가장 최근 요청 이전에 작업이 완료된 요청은 제외합니다.

쿼리 세부 정보는 다음과 같습니다.

• 요청 찾기는 createDate를 기준으로 (1, 2, 4, 5)를 입력합니다.
• 발견된 각 요청 날짜에 대해 다음 요청 createDate까지 (6, 7)에 +1 또는 -1로 합계 작업 유형을 입력합니다.
• 다음 요청 전에 작업 합계가 0보다 큰 요청을 출력합니다.

샘플 표:

create table request
(
    Id             bigint not null,
    userId         bigint,
    type           bigint not null,
    creationDate   timestamp not null
);
create table work
(
    Id             bigint not null,
    type           bigint not null,
    creationDate   timestamp not null
);

표본 데이터:

insert into request (Id, userId, type, creationDate)
values  (4, 45, 2, '2022-12-12 11:02:17'),
        (9, 64, 2, '2022-12-12 01:01:18'),
        (2, 92, 2, '2022-12-11 21:36:36'),
        (2, 21, 2, '2022-12-11 21:25:54'),
        (1, 3, 2, '2022-12-11 21:13:58'),
        (7, 243, 2, '2022-12-11 21:04:05'),
        (8, 24, 2, '2022-12-11 21:01:23');
insert into work (Id, type, creationDate)
values  (3, 7, '2022-12-11 00:00:00'),
        (6, 7, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-11 00:00:00'),
        (6, 7, '2022-12-11 00:00:00'),
        (1, 6, '2022-12-11 00:00:00'),
        (2, 6, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-11 00:00:00'),
        (5, 7, '2022-12-11 00:00:00'),
        (1, 6, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-12 00:00:00'),
        (4, 6, '2022-12-12 00:00:00'),
        (8, 7, '2022-12-12 00:00:00');

시도된 쿼리:

select id, sum(total), type, creationDate from (
  select id, 0 as total, type, creationDate from request
  union
  select id, case type when 6 then 1 when 7 then -1 end as total, type, creationDate from work
) a where total > 0 group by id

실시간 데이터에서는 너무 오래 걸리지만 이 바이올린과 같은 작은 세트에서는 작동합니다.

데이터에 문제가 있습니다. 요청 타임스탬프에는 시간이 포함되지만 작업에는 타임스탬프가 없는 날짜만 있습니다.

Fiddle 보고서:

이드	합계(합계)	유형	작성 날짜
1	1	6	2022-12-11 00:00:00
2	1	6	2022-12-11 00:00:00
4	1	6	2022-12-13 00:00:00

그러나 요청의 타임스탬프가 기술적으로 작업보다 크기 때문에 1과 2는 모두 무효화됩니다.예상 출력은 다음과 같아야 합니다.

이드	합계(합계)	유형	작성 날짜
4	1	6	2022-12-13 00:00:00

id = 4의 경우 작업 날짜는 2022-12-13 00:00이며 요청 시간은 2022-12-12 11:02:17입니다.

이 작업을 수행하는 한 가지 방법은 하위 쿼리를 사용하여 두 테이블을 함께 결합한 다음 결과를 그룹화하는 것입니다.

다음은 예입니다.

SELECT r.Id, SUM(CASE w.type WHEN 6 THEN 1 WHEN 7 THEN -1 END) as total, r.type, r.creationDate
FROM request r
JOIN (
  SELECT Id, type, creationDate
  FROM work
  WHERE type IN (6,7)
) w ON w.creationDate >= r.creationDate
WHERE r.type IN (1,2,4,5)
GROUP BY r.Id, r.type, r.creationDate
HAVING total > 0

언급URL : https://stackoverflow.com/questions/75006017/mariadb-match-requests-with-work-performed-where-total-0-using-a-case