Количество заданий в пересекающихся временных диапазонах

Question

Есть таблица:

sr.number - номер задания
sr.open - дата создания задания
sr.log_open - дата, когда сотрудник начал выполнение задания
sr.log_close - дата, когда сотрудник закончил работу над заданием
sr.login - логин сотрудника

Необходимо написать запрос, в котором будет:
время работы над заданием в виде временных промежутков;
количество заданий, которые были обработаны в этом диапазоне.

Пробовал через конкатенацию, но получается, что диапазоны пересекаются.
Приведу пример моей попытки, чтобы было понятнее:

 select t1.time|| ' - ' || t1.time + 3 as time,
 count(t1.num)
        from(
        select sr.log_close-sr.log_open as time,
        sr.number as num
        from prod) t1
    group by time

Пример входных данных:

sr.number   sr.open         sr.log_open     sr.log_close    sr.login
1           31.03.20 09:00  31.03.20 09:10  31.03.20 09:18  e.valich    
2           01.04.20 10:00  31.03.20 10:10  31.03.20 10:16  e.valich    
3           02.04.20 11:00  31.03.20 11:10  31.03.20 11:12  e.valich    
4           02.04.20 12:00  31.03.20 12:15  31.03.20 12:16  e.valich
5           02.04.20 13:00  31.03.20 13:15  31.03.20 13:20  e.valich

Пример выходных данных:

Answer 1

CREATE TABLE testtable(
    id INTEGER primary key auto_increment,
    number INTEGER NOT NULL,
    open DATE NOT NULL,
    close DATE NOT NULL
);

INSERT INTO testtable
(number, open, close)
VALUES
    (1, '2000-01-01', '2004-01-01'),
    (1, '2001-01-01', '2005-01-01'),
    (1, '2002-01-01', '2003-01-01'),
    (2, '2000-01-01', '2010-01-01'),
    (2, '2004-01-01', '2008-01-01');

SELECT 
    t1.id, t1.open, t1.close, COUNT(*)
FROM
    testtable as t1
JOIN
    testtable as t2
ON
    (t2.open between t1.open AND t1.close)
    AND
    (t2.close between t1.open AND t1.close)
    AND
    t1.id <> t2.id
GROUP BY t1.id;

Если я правильно понял задачу(считать то, чей диапазон полностью находится в текущем диапазоне).
Вывод:

Все остальные диапазоны - нули. Можно, конечно, и их присобачить:

SELECT
    id, open, close, MAX(count) as count
FROM
(SELECT 
    t1.id, t1.open, t1.close, COUNT(*) as count
FROM
    testtable as t1
JOIN
    testtable as t2
ON
    (t2.open between t1.open AND t1.close)
    AND
    (t2.close between t1.open AND t1.close)
    AND
    t1.id <> t2.id
GROUP BY t1.id

UNION

SELECT
    id, open, close, 0 as count
FROM
    testtable as t2) as t
GROUP BY id;

Вывод:

БЛОГ НА HUSL

Количество заданий в пересекающихся временных диапазонах

Ответы (1 шт):