Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 Задача с интервалами  [new]
demon1992
Member

Откуда:
Сообщений: 147
Приветствую.
Прошу тухлыми помидорами не кидать, если задаю глупый вопрос.
Задача: дано n количество интервалов
17.11.2021 08:01:37 - 17.11.2021 08:03:56
17.11.2021 08:03:02 - 17.11.2021 08:06:01
17.11.2021 08:03:33 - 17.11.2021 08:06:50
17.11.2021 08:03:49 - 17.11.2021 08:06:30
....

Я хочу получить периоды, в зависимости от количества пересечений.
То есть
17.11.2021 08:03:02 - 17.11.2021 08:03:33 - 2 пересечения
17.11.2021 08:03:33 - 17.11.2021 08:03:49 - 3 пересечения
17.11.2021 08:03:49 - 17.11.2021 08:03:56 - 4 пересечения
17.11.2021 08:03:56 - 17.11.2021 08:06:01 - 3 пересечения
17.11.2021 08:06:01 - 17.11.2021 08:06:30 - 2 пересечения
Один период это время работы оператора, а я хочу знать, сколько по времени были заняты 1,2,3...n операторов.
Можно это как то решить на чистом sql в один запрос?
19 ноя 21, 15:53    [22398013]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
sysdba22
Member

Откуда:
Сообщений: 308
SELECT a.id, a.start, a.end, COUNT(*)
FROM
  table a JOIN table b ON ((b.start >= a.start AND b.start <= a.end)
    OR (b.end >= a.start AND b.end <= a.end)) AND (a.id <> b.id)
GROUP BY
  1, 2, 3


Сообщение было отредактировано: 19 ноя 21, 17:28
19 ноя 21, 17:27    [22398062]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
m7m
Member

Откуда: Украина, Мариуполь
Сообщений: 1460
demon1992,

Если я правильно понимаю
то периоды
17.11.2021 08:01:37 - 17.11.2021 08:03:02
17.11.2021 08:06:30 - 17.11.2021 08:06:50
тоже должны быть, или как?
19 ноя 21, 18:02    [22398074]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
GJ
Member

Откуда:
Сообщений: 50
Если интервалы хранятся в таблице, содержащей два поля: начало интервала и окончание интервала, то...
монстра можно попробовать слепить
select
  l.p as beg_p,
  (select min(pt) from (select timestamp1 as pt from intervals union select timestamp2 from intervals) where pt > l.p) as end_p,
  (select count(*)
   from intervals
   where timestamp1 <= l.p and
         timestamp2 >= (select min(pt) from (select timestamp1 as pt from intervals union select timestamp2 from intervals) where pt > l.p)
  ) as interval_count
from
  (select timestamp1 as p from intervals union select timestamp2 from intervals order by 1) l
where
  (select min(pt) from (select timestamp1 as pt from intervals union select timestamp2 from intervals) where pt > l.p) is not null

здесь таблица intervals содержит два поля: timestamp1 -- начало интервала, timestamp2 -- окончание интервала.
Но пользоваться таким убежищем я бы не стал. Либо структуру БД менять специально под эту задачу, либо делать ХП.
19 ноя 21, 18:40    [22398097]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
Шавлюк Евгений
Member

Откуда: Одесса
Сообщений: 610
demon1992,

Создаем такую служебную процедуру
+ GET_TIMES
create or alter procedure GET_TIMES (
    T1 timestamp,
    T2 timestamp)
returns (
    T timestamp,
    N integer)
as
begin
  if (t1 is not null) then
  begin
    t = t1;
    n = 1;
    suspend;
  end

  if (t2 is not null) then
  begin
    t = t2;
    n = 2;
    suspend;
  end
end

with
dat as (select timestamp '17.11.2021 08:01:37' t1, timestamp '17.11.2021 08:03:56' t2, 1 id from rdb$database union all
        select timestamp '17.11.2021 08:03:02' t1, timestamp '17.11.2021 08:06:01' t2, 2 id from rdb$database union all
        select timestamp '17.11.2021 08:03:33' t1, timestamp '17.11.2021 08:06:50' t2, 3 id from rdb$database union all
        select timestamp '17.11.2021 08:03:49' t1, timestamp '17.11.2021 08:06:30' t2, 4 id from rdb$database),
dat2 as (select r.t,
                iif(max(r.n) = 1, count(*) + 1 , 0) +
                iif(min(r.n) = 2, count(*), 0) cnt
         from dat a
         join dat b on b.id <> a.id
         join GET_TIMES(iif(a.t1 between b.t1 and b.t2, a.t1, null), iif(a.t2 between b.t1 and b.t2, a.t2, null)) r on 1=1
         where a.t1 between b.t1 and b.t2 or a.t2 between b.t1 and b.t2
         group by 1),
dat3 as (select t t1, lead(t) over () t2, cnt from dat2)
select t1, t2, cnt from dat3 where cnt > 1


Сообщение было отредактировано: 19 ноя 21, 18:53
19 ноя 21, 18:49    [22398101]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1119
demon1992,

искать пересечения - наверно неправильный подход. Например, один работал с 08:00 до 09:00, другой - с 08:10 до 08:50, третий - с 08:20 до 08:40. Пересечений нет. Хотя с 08:20 до 08:40 работали трое. Я бы строил график по времени и количеству работающих.
19 ноя 21, 19:38    [22398131]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
Ivan_Pisarevsky
Member

Откуда: НН
Сообщений: 8877
Сдается мне самым быстрым будет подсчитать сколько открытых смен попадало в каждую минуту суток.
получаешь список пар
"номер минуты" "кол-во"
0 0
1 2
2 2
и тп
далее этот список группируешь, рисуешь графики и прочее.
19 ноя 21, 19:48    [22398135]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
Шавлюк Евгений
Member

Откуда: Одесса
Сообщений: 610
Вот еще один вариант, на оконных функциях.
Мой предыдущий не при всех данных дает правильный результат
Это должен всегда показывать хороший результат
+ Дополнительная процедура
create or alter procedure GET_SECONDS (
    T1 timestamp,
    T2 timestamp)
returns (
    T timestamp)
as
begin
  t = t1;
  while (t < t2) do
  begin
    suspend;
    t = dateadd(second, 1, t);
  end
end


with
dat as (select timestamp '17.11.2021 08:01:37' t1, timestamp '17.11.2021 08:03:56' t2 from rdb$database union all
        select timestamp '17.11.2021 08:03:02' t1, timestamp '17.11.2021 08:06:01' t2 from rdb$database union all
        select timestamp '17.11.2021 08:03:33' t1, timestamp '17.11.2021 08:06:50' t2 from rdb$database union all
        select timestamp '17.11.2021 08:03:49' t1, timestamp '17.11.2021 08:06:30' t2 from rdb$database)
select t, dateadd(second, 1, t2) t2, cnt
from (select t,
             lead(t) over (order by t) t2,
             cnt, cnt2
      from (select t,
                   cnt,
                   lag(cnt) over (order by t) cnt1,
                   lead(cnt) over (order by t) cnt2
            from (select t, count(*) cnt
                  from dat
                  join get_seconds(t1, t2)  on 1=1
                  group by 1))
      where cnt1 is distinct from cnt or cnt2 is distinct from cnt)
where cnt = cnt2


Отличная пятничная задачка

Сообщение было отредактировано: 19 ноя 21, 20:54
19 ноя 21, 20:51    [22398166]     Ответить | Цитировать Сообщить модератору
 Re: Задача с интервалами  [new]
GJ
Member

Откуда:
Сообщений: 50
Шавлюк Евгений
Создаем такую служебную процедуру <...>

Непонятно... Если уж все равно создавать процедуру, то почему все сразу не выполнить в процедуре или EXECUTE BLOCK? Будет проще и понятнее. Как-то так:
EXECUTE BLOCK
RETURNS (
  BEG_P TIMESTAMP,
  END_P TIMESTAMP,
  INTERVAL_COUNT INTEGER)
AS
BEGIN
  beg_p = NULL;
  FOR
    SELECT timestamp1 FROM intervals UNION SELECT timestamp2 FROM intervals ORDER BY 1 INTO :end_p
  DO
  BEGIN
    IF (beg_p IS NOT NULL) THEN
    BEGIN
      SELECT COUNT(*) FROM intervals WHERE timestamp1 <= :beg_p AND timestamp2 >= :end_p INTO :interval_count;
      SUSPEND;
    END
    beg_p = end_p;
  END
END
20 ноя 21, 18:36    [22398368]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить