Sql запрос для нахождения одновременных процессов

Question

Sql запрос для нахождения одновременных процессов

Рейтинг: 0Ответов: 1Опубликовано: 25.07.2023

Есть таблица

CREATE TABLE IF NOT EXISTS "events" (
  "id"  INTEGER,
  "cid"  INTEGER NOT NULL,
  "type"  TEXT NOT NULL,
  "date"  DATETIME NOT NULL,
  PRIMARY KEY("id")
);

Значения таблицы

INSERT INTO "events" ("id","cid","type","date") VALUES (1,0,'start','2021-01-01 00:00:00'),
(2,2,'start','2021-01-01 02:00:00'),
(3,1,'start','2021-01-01 03:00:00'),
(4,0,'end','2021-01-01 06:00:00'),
(5,1,'end','2021-01-01 07:00:00'),
(6,3,'start','2021-01-01 08:00:00'),
(7,3,'end','2021-01-01 08:30:00'),
(8,4,'start','2021-01-01 08:45:00'),
(9,2,'end','2021-01-01 09:00:00'),
(10,5,'start','2021-01-01 10:00:00'),
(11,6,'start','2021-01-01 11:00:00'),
(12,4,'end','2021-01-01 12:00:00'),
(13,5,'end','2021-01-01 13:00:00'),
(14,7,'start','2021-01-01 14:00:00'),
(15,7,'end','2021-01-01 15:00:00'),
(16,6,'end','2021-01-01 16:00:00'),
(17,8,'start','2021-01-01 15:30:00'),
(18,9,'start','2021-01-01 16:30:00'),
(19,10,'start','2021-01-01 17:00:00'),
(20,8,'end','2021-01-01 18:00:00'),
(21,10,'end','2021-01-01 19:00:00'),
(22,11,'start','2021-01-01 19:30:00'),
(23,11,'end','2021-01-01 19:45:00'),
(24,9,'end','2021-01-01 19:59:00');

Нужно написать запрос который выведет промежутки времени с количеством одновременных процессов

Пробовал

select
  min(date) start,
  max(date) end,
  (max(id) - min(id)) count
from events
group by cid

Но не сходится с правильным ответом первой записи ответа (start: 03:00:00, end: 06:00:00, count: 3)

Как оказалось я искал кол-во процессов между началом-концом процессов, yужен именно диапазон, а диапазон никак не связан с началом-концом процессов. Любой промежуток между двумя отметками времени - диапазон. Процесс 0 идёт с 00:00 до 06:00. Процесс 2 идет с 02:00 до 09:00. Процесс 1 идет с 03:00 до 07:00. Соответственно, как раз с 03:00 до 06:00 все эти 3 процесса (0, 2, 1) идут одновременно.

Для понимания сделал мапу процессов, где видно процессы, которые идут одновременно:

sql

Источник: Stack Overflow на русском

Answer 1

▲ 0

Предлагаю просто посчитать количество start (счетчик +1) и end (счетчик -1) на каждый момент времени.

select *
  ,sum(case when type='start' then 1 when type='end' then -1 end)
      over(order by date) cnt
from events

Что делать, если в один момент 2 записи - смотрите по задаче. Можно сгруппировать по моментам времени результат запроса и взять максимальный за этот момент.

select date,max(cnt) cnt
from(
   select *
     ,sum(case when type='start' then 1 when type='end' then -1 end)
        over(order by date) cnt
   from events
  )t
group by date

Sql запрос для нахождения одновременных процессов

Ответы