ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Задача с интервалами
9 сообщений из 9, страница 1 из 1
Задача с интервалами
    #40113158
demon1992
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
demon1992
Гость
Приветствую.
Прошу тухлыми помидорами не кидать, если задаю глупый вопрос.
Задача: дано 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 в один запрос?
...
Рейтинг: 0 / 0
19.11.2021, 15:53
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113191
sysdba22
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sysdba22
Гость
Код: sql
1.
2.
3.
4.
5.
6.
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
...
Рейтинг: 0 / 0
19.11.2021, 17:27
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113200
m7m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
m7m
Участник
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
тоже должны быть, или как?
...
Рейтинг: 0 / 0
19.11.2021, 18:02
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113207
GJ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
GJ
Гость
Если интервалы хранятся в таблице, содержащей два поля: начало интервала и окончание интервала, то...
монстра можно попробовать слепить
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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 -- окончание интервала.
Но пользоваться таким убежищем я бы не стал. Либо структуру БД менять специально под эту задачу, либо делать ХП.
...
Рейтинг: 0 / 0
19.11.2021, 18:40
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113208
Шавлюк Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шавлюк Евгений
Участник
demon1992,

Создаем такую служебную процедуру
 
GET_TIMES
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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
...
Рейтинг: 0 / 0
19.11.2021, 18:49
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113226
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
demon1992,

искать пересечения - наверно неправильный подход. Например, один работал с 08:00 до 09:00, другой - с 08:10 до 08:50, третий - с 08:20 до 08:40. Пересечений нет. Хотя с 08:20 до 08:40 работали трое. Я бы строил график по времени и количеству работающих.
...
Рейтинг: 0 / 0
19.11.2021, 19:38
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113228
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Сдается мне самым быстрым будет подсчитать сколько открытых смен попадало в каждую минуту суток.
получаешь список пар
"номер минуты" "кол-во"
0 0
1 2
2 2
и тп
далее этот список группируешь, рисуешь графики и прочее.
...
Рейтинг: 0 / 0
19.11.2021, 19:48
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113244
Шавлюк Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шавлюк Евгений
Участник
Вот еще один вариант, на оконных функциях.
Мой предыдущий не при всех данных дает правильный результат
Это должен всегда показывать хороший результат
 
Дополнительная процедура
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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



Отличная пятничная задачка
...
Рейтинг: 0 / 0
19.11.2021, 20:51
| Ответить | Цитировать | Написать  
Задача с интервалами
    #40113366
GJ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
GJ
Гость
Шавлюк Евгений
Создаем такую служебную процедуру <...>

Непонятно... Если уж все равно создавать процедуру, то почему все сразу не выполнить в процедуре или EXECUTE BLOCK? Будет проще и понятнее. Как-то так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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
...
Рейтинг: 0 / 0
20.11.2021, 18:36
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Задача с интервалами
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=40113191&tid=1559885]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 176ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 14ms
total:  288ms
созд. / загр.: 288ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]