Расчёт в виртуальном столбце с использованием LAG

Question

Есть большая таблица данных с: Время начала - Конечное время - ID

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

Ничего лучше, чем сосчитать разницу во времени между каждой записью, я не придумал.

По моей задумке, datediff это разница во времени между FinishDateTime текущей строки и StartDateTime следующей. Если разница больше 5 минут, то к предыдущему значению disc прибавить 1. Таким образом, у меня должны все записи между перерывами как в минимум в 5 минут будут иметь одинаковое значение disc. Затем, не сложно посчитать количество записей в каждом disc.

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

Я понимаю логику, почему так происходит, но не знаю, как решить эту проблему. Гугл особо не помог. Много чего перепробовал, ничего не вышло.

Помогите пожалуйста найти правильный подход. Может быть решение моей задачи изначально может более простым.

Вот код, который есть:

 SELECT main.ID, main.StartTime, main.FinishTime, main.datediff, main.disc AS disc,
    CASE 
    WHEN main.datediff > 5 THEN LAG(main.disc,1,0) OVER (ORDER BY main.DealerId) ELSE (LAG(main.disc,1,0) OVER (ORDER BY main.DealerId))+1 END AS disc
FROM(
    SELECT dbo.Rounds.ID AS ID, 
    dbo.Rounds.StartTime AS StartTime, 
    dbo.Rounds.FinishTime AS FinishTime,
    LEAD(dbo.Rounds.StartDateTime, 1,Null) OVER (ORDER BY dbo.Rounds.DealerId) AS PreviousQuota,
    '0' as disc,
    DATEDIFF(minute,dbo.Rounds.FinishDateTime,LEAD(dbo.Rounds.StartDateTime, 1,Null) OVER (ORDER BY dbo.Rounds.DealerId)) AS datediff
        FROM dbo.Rounds
        WHERE dbo.Rounds.CreateDateTime >= DATEADD(hh, 0, '12/29/2020')
            AND dbo.Rounds.GameType = 2 
    ) AS main
ORDER BY main.ID, main.StartTime

Обновление 1:

 SELECT main.ID, main.StartTime, main.FinishTime, main.datediff, main.disc AS disc, main.delta,

 SUM(CASE WHEN main.datediff > 5
                THEN 1
                ELSE 0
                END) OVER (ORDER BY main.ID, main.StartTime) group_num
FROM(
    SELECT dbo.Rounds.DealerId AS ID, 
    dbo.Rounds.StartDateTime AS StartTime, 
    dbo.Rounds.FinishDateTime AS FinishTime,
    LEAD(dbo.Rounds.StartDateTime, 1,Null) OVER (ORDER BY dbo.Rounds.DealerId) AS PreviousQuota,
    '0' as disc,
    DATEDIFF(minute,dbo.Rounds.FinishDateTime,LEAD(dbo.Rounds.StartDateTime, 1,Null) OVER (ORDER BY dbo.Rounds.DealerId)) AS datediff,
    
     DATEDIFF(minute,dbo.Rounds.StartDateTime,COALESCE(LAG(FinishDateTime,1,Null) OVER (ORDER BY dbo.Rounds.DealerId), Null)) AS delta
     
        FROM dbo.Rounds
        WHERE dbo.Rounds.CreateDateTime >= DATEADD(hh, 0, '12/29/2020')
            AND dbo.Rounds.GameType = 2 
    ) AS main

Answer 1

Судя по тексту попыток, DBMS == SQL Server.

WITH cte AS (
    SELECT start, 
           finish, 
           start - COALESCE(LAG(finish) OVER (ORDER BY start ASC), 
                            MIN(start) OVER ()) delta
    FROM test
)
SELECT start, 
       finish, 
       SUM(CASE WHEN delta > 5
                THEN 1
                ELSE 0
                END) OVER (ORDER BY start ASC) group_num
FROM cte

fiddle

PS. В модели использованы INT - вот лень мне создавать кучу дат, а автор не озаботился. Соответственно вместо вычитания в CTE следует использовать DATEDIFF(MINUTE, ...)

БЛОГ НА HUSL

Расчёт в виртуальном столбце с использованием LAG

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