SQL Задача со временем

Question

Есть сущность TimeTracking (записи прихода пользователей)

state 1 - пришел
state 2 - вышел

Пользователь не может перейти в состояние 1, если он находится в нем же.

Как составить таблицу, где будет время каждого посещения?

Пробывал решить через создание двух таблиц (state1 - только состояние 1, state2)

WITH state1 AS (
    SELECT *
    FROM timetracking as t1
    WHERE t1.state = 1
    ), state2 AS (
        SELECT *
        FROM timetracking as t1
        WHERE t1.state = 2
    ), max_time_peer AS (
        SELECT *
        FROM state1 AS s1
        JOIN state2 AS s2 ON s1.peer = s2.peer
    )

SELECT *
FROM max_time_peer

Но не понимаю, как убрать не нужные записи

Answer 1

 userid int PK not null,
 timeIn int8 not null,
 timeLeave int8, 
....

//Получить всех кто внутри:
select * from leave_in where timeLeave is null
//Проверить внутри пользователь или нет (true - внутри) 
select timeLeave is null from leave_in where userid={1}

Можно добавить ограничение прям в БД, чтоб не могло быть двух записей с одинаковым UserId и нулевым временем "выхода" :)

Answer 2

Просто нумеруем записи входа и выхода для каждого peer. Если данные не содержат косяков:

имеется две записи с одинаковым состоянием (например, два входа) подряд
самая первая по дате-времени запись - выход

то вот такой запрос выведет пары вход-выход.

WITH
cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY peer, state ORDER BY date, time) rn
    FROM timetracking 
)
SELECT *
FROM cte t1
JOIN cte t2 USING (peer, rn)
WHERE t1.state = 1 AND t2.state = 2

И теперь вместо SELECT * считай и выводи всё, что требуется.

БЛОГ НА HUSL

SQL Задача со временем

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