SQLize Online / PHPize Online  /  SQLtest Online

create table auth_logs ( id bigint unsigned auto_increment primary key, admin_id bigint unsigned not null, type tinyint unsigned not null, timestamp timestamp default CURRENT_TIMESTAMP not null ); -- юзер 1 зашел в 8 утра, а вышел в 10 дня, -- потом зашел в 11 и вышел в 17 -- далее зашел в 19 и вышел в 19:30 -- всего он работал 8 часов 30 минут, как мне получить это число, пускай даже в минутах? insert into auth_logs (admin_id, type, timestamp) values (1, 1, '2023-12-20 08:00:00'); insert into auth_logs (admin_id, type, timestamp) values (2, 1, '2023-12-20 08:00:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 2, '2023-12-20 10:00:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 1, '2023-12-20 11:00:00'); insert into auth_logs (admin_id, type, timestamp) values (2, 2, '2023-12-20 12:00:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 2, '2023-12-20 17:00:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 1, '2023-12-20 19:00:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 2, '2023-12-20 19:30:00'); insert into auth_logs (admin_id, type, timestamp) values (1, 2, '2023-12-20 19:30:00'); -- Calculate the total time user 1 worked in minutes SELECT *, TIMESTAMPDIFF(MINUTE, login.login_timestamp, IFNULL(logout.logout_timestamp, NOW())) AS total_work_minutes FROM ( SELECT id, admin_id, timestamp AS login_timestamp FROM auth_logs WHERE type = 1 ) AS login LEFT JOIN ( SELECT id, admin_id, timestamp AS logout_timestamp FROM auth_logs WHERE type = 2 ) AS logout ON login.id = ( SELECT MAX(id) FROM auth_logs WHERE type = 1 AND timestamp <= logout.logout_timestamp ); -- 1. В этом SQL-запросе мы хотим вычислить общее время работы пользователя с admin_id = 1 в минутах. -- 2. Сначала мы создаем два подзапроса (subquery): -- - Первый подзапрос (login) выбирает все входные (type = 1) моменты пользователя 1 и сохраняет их id и timestamp в алиас login. -- - Второй подзапрос (logout) выбирает все выходные (type = 2) моменты пользователя 1 и сохраняет их id и timestamp в алиас logout. -- 3. После того, как у нас есть эти два набора данных с метками времени входа и выхода, мы хотим вычислить разницу во времени между каждой парой входа и выхода в минутах. Мы используем функцию TIMESTAMPDIFF(MINUTE, ...) для этого. Это позволяет нам получить разницу во времени в минутах между двумя моментами времени. -- 4. Затем мы объединяем (join) эти два набора данных (login и logout) по id, чтобы каждый момент выхода соответствовал соответствующему моменту входа (используя login.id = ...). Это позволяет нам вычислить время между каждым входом и соответствующим ему выходом. -- 5. Мы использовали подзапрос, чтобы найти максимальный id входа, который произошел до момента выхода (SELECT MAX(id) ...). Это гарантирует, что мы соединяем момент выхода с последним моментом входа до него. -- 6. Затем мы используем SUM(...) для суммирования всех полученных разниц во времени в минутах. Это дает нам общее время работы пользователя 1 в минутах. -- 7. Мы используем GROUP BY login.admin_id для группировки результатов по admin_id, чтобы получить итоговое время работы для каждого пользователя с учетом их admin_id. -- Итак, этот SQL-запрос позволяет нам вычислить общее время работы пользователя 1 в минутах, используя данные о входах и выходах из таблицы auth_logs.