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');
Нужно составить запрос, который вернет начало и конец первого диапазона с максимальным количеством одновременных процессов.
Для этой таблицы это будет:
{start: "2021-01-01 03:00:00", end: "2021-01-01 06:00:00", count: 3}
SQL
Задача по SQL
Начни с превращения этой фиговины в нормальные интервалы:
Дальше надо думать, как посчитать одновременность - чой-то я в субботу с утра не могу-с.
WITH
STARTENDS
AS
(SELECT E1."date" START_TIME, E2."date" END_TIME, E1."cid" CID
FROM "events" E1 INNER JOIN "events" E2 ON E1."cid" = E2."cid"
WHERE E1."type" = 'start' AND E2."type" = 'end'),
Дальше надо думать, как посчитать одновременность - чой-то я в субботу с утра не могу-с.
WITH events_cte AS (
SELECT date, SUM(CASE WHEN type = 'start' THEN 1 ELSE -1 END) AS change
FROM events
GROUP BY date
),
cumulative_cte AS (
SELECT date, SUM(change) OVER (ORDER BY date) AS cumulative
FROM events_cte
),
max_cte AS (
SELECT MAX(cumulative) AS max_cumulative
FROM cumulative_cte
),
start_cte AS (
SELECT MIN(date) AS start_date
FROM cumulative_cte
WHERE cumulative = (SELECT max_cumulative FROM max_cte)
),
end_cte AS (
SELECT MIN(date) AS end_date
FROM cumulative_cte
WHERE date > (SELECT start_date FROM start_cte)
)
SELECT start_date AS start, end_date AS end, max_cumulative AS count
FROM start_cte, end_cte, max_cte;
Этот запрос использует общие выражения таблиц (CTE), чтобы вычислить изменение количества процессов на каждом событии, накопительное количество процессов на каждом событии, максимальное количество одновременных процессов, начало первого диапазона с максимальным количеством одновременных процессов и конец этого диапазона. Окончательный оператор SELECT объединяет эти CTE, чтобы вернуть желаемый результат.
Bijo Wardiashwili
Спасибо, но этот вариант не работает, выдавая ошибку: TypeError: Cannot read properties of undefined (reading 'substring')
with i as (select e.cid, e.date start, e1.date end from events e left join events e1 on e.cid=e1.cid and e.type='start' and e1.type='end' where e.type='start')
select i.cid,i.start,i.end,count(1) c from i left join i i1 on not (i.start>i1.end or i.end<i1.start) group by i.cid,i.start,i.end order by i.end
Возможны опечатки
select i.cid,i.start,i.end,count(1) c from i left join i i1 on not (i.start>i1.end or i.end<i1.start) group by i.cid,i.start,i.end order by i.end
Возможны опечатки
Похожие вопросы
- Простая задача на SQL
- SQL. Выводятся не все данные. 4 задача.
- Вопрос по SQL. Помогите понять решение задачи
- SQL задача. Помогите решить
- Чем отличаются разные SQL?
- Не очень понятен синтаксис запросов SQL
- Помогите написать sql запросы
- Вопрос по SQL + Python
- Что делает кусок коды, выделенный синим? SQL
- Подскажите, есть ли такое понятие чистый SQL???