Как посчитать общую длительность времени между строками определенной выборки в MySQL?

Question

[long_skinny_boy]

Добрый день, подскажите пожалуйста, куда нужно копать, имеет такого рода таблица

user ; datetime ; event
user1 ; 23.03.2022 00:00:00 ; 1
user1 ; 23.03.2022 00:10:00 ; 2
user1 ; 23.03.2022 00:15:00 ; 1
user2 ; 23.03.2022 00:20:00 ; 1
user1 ; 23.03.2022 00:30:00 ; 2
user2 ; 23.03.2022 00:30:00 ; 1

в данном случае event 1 - это вход пользователя, 2 - это выход
задача такая, что нужно посчитать общее время работы пользователей.
Т.е. если посмотреть на пример выше то за март месяц первый пользователь проработал 25 минут в целом, а второй пользователь 10 минут

Каким то образом нужно сгруппировать и с агрегировать информацию по длительности между парными строками по каждому пользователю и чтобы event шли в определенной последовательности, т.е. только между 1 и 2 (не между 2 и 1)

Comments

[Akina]

1. Укажите ТОЧНУЮ версию MySQL.
   
2. Покажите требуемый ответ для именно этих данных. Уточните: если два входа подряд - считать от первого или от последнего? а если два выхода подряд?
   
3. Замените (либо добавьте) табличный вид примера данных на скрипты CREATE TABLE + INSERT INTO. Ещё лучше - создайте online fiddle и дайте ссылку. Пример данных должен включать все вышеперечисленные "некорректности данных".
   

[long_skinny_boy]

1) MariaDB 10.7
2) Ожидаю увидеть (количество в минутах, число)
user ; duration
user1 ; 25
user2 ; 10
3) sqlfiddle.com/#!9/56c0346/1

[Akina]

2) Ожидаю увидеть (количество в минутах, число)
user ; duration
user1 ; 25
user2 ; 10

Поясните, откуда у user2 взялись 10 минут. Я вижу у него 2 входа и ни одного выхода.

Answer 1

[Akina]

WITH cte AS ( SELECT *, 
                     LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime, 
                     LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event 
              FROM history )
SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
FROM cte
WHERE (event, lag_event) = (2,1)
GROUP BY login;

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=398... (исходные данные подправлены).

Comments

[long_skinny_boy]

Большое спасибо и низкий поклон, мой уровень до такого бы не додумался, боюсь нужно время, чтобы изучить что здесь происходит =)
Спасибо!