Как получить первую и последнюю запись каждого пользователя в MySQL (для посещаемости)

У меня есть следующая таблица:

CREATE TABLE IF NOT EXISTS `access_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL DEFAULT 0,
    `room_id` INT(11) NOT NULL DEFAULT 0,
    `created` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Новая запись добавляется каждый раз, когда пользователь входит в комнату room_id. Я хотел бы выбрать первую и последнюю запись каждого пользователя для каждой комнаты.

В настоящее время у меня есть следующие запросы, которые, похоже, не дают правильных записей:

Для записи первый каждой комнаты для каждого пользователя:

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'

Для записи прошлой каждой комнаты для каждого пользователя:

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'

Вот демонстрация SQLFiddle, включающая пример данных http://www.sqlfiddle.com/#!9/fc5f8b/2. Вы можете видеть, что запрос отображает непреднамеренные результаты. В них не указаны разные дни, хотя указаны разные комнаты. Также различается количество строк для первого и последнего запросов.

DDL того же:

CREATE TABLE IF NOT EXISTS `access_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL DEFAULT 0,
    `room_id` INT(11) NOT NULL DEFAULT 0,
    `created` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `access_log` (`id`, `user_id`, `room_id`, `created`) VALUES
(1, 90000017, 6, '2019-06-10 01:15:00'),
(2, 90000017, 6, '2019-06-10 01:25:00'),
(3, 90000018, 6, '2019-06-10 02:15:00'),
(4, 90000018, 6, '2019-06-10 02:25:00'),
(5, 90000019, 6, '2019-06-10 03:15:00'),
(6, 90000019, 6, '2019-06-10 03:25:00'),

(7, 90000017, 5, '2019-06-10 11:15:00'),
(8, 90000017, 5, '2019-06-10 11:25:00'),
(9, 90000018, 5, '2019-06-10 12:15:00'),
(10, 90000018, 5, '2019-06-10 12:25:00'),
(11, 90000019, 5, '2019-06-10 13:15:00'),
(12, 90000019, 5, '2019-06-10 13:25:00'),

(13, 90000017, 6, '2019-06-11 04:10:00'),
(14, 90000017, 6, '2019-06-11 04:20:00'),
(15, 90000018, 6, '2019-06-11 05:10:00'),
(16, 90000018, 6, '2019-06-11 05:20:00'),
(17, 90000019, 6, '2019-06-11 06:10:00'),
(18, 90000019, 6, '2019-06-11 06:20:00'),

(19, 90000017, 5, '2019-06-11 14:10:00'),
(20, 90000017, 5, '2019-06-11 14:20:00'),
(21, 90000018, 5, '2019-06-11 15:10:00'),
(22, 90000018, 5, '2019-06-11 15:20:00'),
(23, 90000019, 5, '2019-06-11 16:20:00'),
(24, 90000019, 5, '2019-06-11 16:20:00');

Ожидаемые результаты должны быть примерно такими:

First per user per room per day
+------+-----------+---------+---------------------+
|  id  |  user_id  | room_id |       created       |
+------+-----------+---------+---------------------+
|  1   | 90000017  |    6    | 2019-06-10 01:15:00 |
|  3   | 90000018  |    6    | 2019-06-10 02:15:00 |
|  5   | 90000019  |    6    | 2019-06-10 03:15:00 |
|  7   | 90000017  |    5    | 2019-06-10 11:15:00 |
|  9   | 90000018  |    5    | 2019-06-10 12:15:00 |
|  11  | 90000019  |    5    | 2019-06-10 13:15:00 |
|  13  | 90000017  |    6    | 2019-06-11 04:10:00 |
|  15  | 90000018  |    6    | 2019-06-11 05:10:00 |
|  17  | 90000019  |    6    | 2019-06-11 06:10:00 |
|  19  | 90000017  |    5    | 2019-06-11 14:10:00 |
|  21  | 90000018  |    5    | 2019-06-11 15:10:00 |
|  23  | 90000019  |    5    | 2019-06-11 16:20:00 |
+------+-----------+---------+---------------------+

Last per user per room per day
+------+-----------+---------+---------------------+
|  id  |  user_id  | room_id |       created       |
+------+-----------+---------+---------------------+
|  2   | 90000017  |    6    | 2019-06-10 01:25:00 |
|  4   | 90000018  |    6    | 2019-06-10 02:25:00 |
|  6   | 90000019  |    6    | 2019-06-10 03:25:00 |
|  8   | 90000017  |    5    | 2019-06-10 11:25:00 |
|  10  | 90000018  |    5    | 2019-06-10 12:25:00 |
|  12  | 90000019  |    5    | 2019-06-10 13:25:00 |
|  14  | 90000017  |    6    | 2019-06-11 04:20:00 |
|  16  | 90000018  |    6    | 2019-06-11 05:20:00 |
|  18  | 90000019  |    6    | 2019-06-11 06:20:00 |
|  20  | 90000017  |    5    | 2019-06-11 14:20:00 |
|  22  | 90000018  |    5    | 2019-06-11 15:20:00 |
|  24  | 90000019  |    5    | 2019-06-11 16:20:00 |
+------+-----------+---------+---------------------+

Напишите запрос, чтобы получить первый, другой запрос, чтобы получить последний, а затем объедините их с UNION.

— 11.06.2019 22:38

Я уже сделал это, но это не сработало, поэтому я разместил вопрос.

— 11.06.2019 23:01

Почему вы присоединились к pacc_leapaccess_rooms? Вы никогда не используете ничего из этой таблицы. И подзапрос должен использовать access_log, а не pacc_leapaccess_access_log.

— 11.06.2019 23:15

MyISAM все еще используется? Почему?

— 12.06.2019 02:59

Каким тогда будет правильный результат?

— 12.06.2019 03:19

Я добавил контрольный запрос в вашу скрипку, не могли бы вы проверить, возвращает ли результат что-либо близкое к ожидаемому результату? sqlfiddle.com/#!9/fc5f8b/7 спасибо

— 12.06.2019 03:32

@tcadidot0 правильный результат - показать первую/последнюю запись каждого пользователя для каждой комнаты за каждый день.

— 12.06.2019 05:35

@ tcadidot0 ваш запрос не показывает разные дни для минимума и максимума, все они относятся к одному дню.

— 12.06.2019 05:37

так? sqlfiddle.com/#!9/fc5f8b/9

— 12.06.2019 05:51

@tcadidot0 да, точно! ага, я только что отредактировал свой вопрос, чтобы включить ожидаемые результаты. Можете ли вы объяснить свой запрос? Мой фактический запрос включает в себя множество других соединений, и в таблице access_log есть другие поля, к которым применяются условия. Я не уверен, как я могу включить ваш запрос в остальные мои.

— 12.06.2019 05:59

mysql group-by

11.06.2019 22:30

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

261

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Подзапрос должен выбирать из той же таблицы, что и основной запрос, поэтому он должен выбирать из access_log, а не pacc_leapaccess_access_log.

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `pacc_leapaccess_rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59';

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `pacc_leapaccess_rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59';

Я сделал опечатку и исправил. Мой код точно такой же, как тот, который вы предоставили. Я добавил сюда sqlfiddle sqlfiddle.com/#!9/fc5f8b/2

— 12.06.2019 00:47

11.06.2019 23:19

Ответ принят как подходящий

Я предлагаю с этим единственным запросом перепроверить ожидаемый результат:

SELECT 
   GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' ') all_id, 
   -- this return all id present in the group
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',1) min_id_in,
   -- this part is taking the first value from the GROUP_CONCAT operation above
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',-1) max_id_in,
   -- this part is taking the last value from the first GROUP_CONCAT operation
   user_id,room_id,
   MIN(created), 
   MAX(created) -- Min/max value are both shown in same query
FROM access_log 
GROUP BY user_id,room_id,
date(created); -- the missing condition where OP's asks results to return by each date.

Я добавил date(created) в условие GROUP BY ...

В исходном запросе из скрипки:

SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- I've added the condition here 
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;


SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- and here
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;

Красиво получилось, спасибо. Для всех, кто ищет подобное решение, пожалуйста, прочитайте ветку комментариев к вопросу.

— 12.06.2019 06:23

12.06.2019 06:11