У меня есть две таблицы в моей базе данных MySQL:
users_metrics
id | user_id | login_datetime | logout_datetime |
--------------------------------------------------.-----|
1 | 1 |2022-05-04 00:25:16 |2022-05-04 00:30:16|
-----------------------------------------------.--------|
2 | 3 |2022-05-04 10:29:16 |2022-05-04 10:40:16|
--------------------------------------------------------|
3 | 1 |2022-05-04 11:48:16 |2022-05-04 11:49:56|
--------------------------------------------------------|
4 | 3 |2022-05-04 11:58:16 |2022-05-04 12:20:16|
--------------------------------------------------------|
5 | 1 |2022-05-04 16:28:16 |2022-05-04 17:29:56|
--------------------------------------------------------|
files_uploaded
id | type | file_upload_datetime | user_id |
-----------------------------------------------
1 | csv |2022-05-04 00:29:16 | 1 |
-----------------------------------------------
2 | csv |2022-05-04 10:39:16 | 3 |
-----------------------------------------------
3 | txt |2022-05-04 11:49:16 | 1 |
-----------------------------------------------
4 | txt |2022-05-04 11:59:16 | 3 |
-----------------------------------------------
5 | csv |2022-05-04 12:09:16 | 3 |
-----------------------------------------------
6 | txt |2022-05-04 12:19:16 | 3 |
-----------------------------------------------
7 | txt |2022-05-04 16:29:16 | 1 |
-----------------------------------------------
8 | csv |2022-05-04 16:39:16 | 1 |
-----------------------------------------------
9 | txt |2022-05-04 16:49:16 | 1 |
-----------------------------------------------
10 | csv |2022-05-04 17:29:16 | 1 |
-----------------------------------------------
Таблица users_metrics
сохраняет время входа и выхода определенного пользователя, а таблица files_uploaded
записывает записи файлов, загруженные пользователями.
Я ищу запрос, который позволяет получить количество загруженных файлов в пределах диапазона даты и времени, установленного первой таблицей.
Это будет представление о результате, который я ищу.
id | user_id | login_datetime | logout_datetime | total_files_uploaded
--------------------------------------------------.-----|---------------------|
1 | 1 |2022-05-04 00:25:16 |2022-05-04 00:30:16| 1
-----------------------------------------------.--------|---------------------|
2 | 3 |2022-05-04 10:29:16 |2022-05-04 10:40:16| 1
--------------------------------------------------------|---------------------|
3 | 1 |2022-05-04 11:48:16 |2022-05-04 11:49:56| 1
--------------------------------------------------------|---------------------|
4 | 3 |2022-05-04 11:58:16 |2022-05-04 12:20:16| 3
--------------------------------------------------------|---------------------|
5 | 1 |2022-05-04 16:28:16 |2022-05-04 17:29:56| 3
--------------------------------------------------------|---------------------|
Вы можете присоединиться к таблицам на основе идентификатора пользователя и времени входа, а затем подсчитать загрузки:
SELECT u.user_id,
u.login_datetime,
u.logout_datetime,
coalesce(count(f.id), 0) total_files_uploaded
FROM user_metrics u
LEFT JOIN files_uploaded f
ON u.user_id = f.user_id
AND f.file_uploaded_datetime BETWEEN u.login_datetime AND u.logout_datetime
GROUP BY u.user_id, u.login_datetime, u.logout_datetime;
Выход:
Логин пользователя | login_datetime | logout_datetime | total_files_uploaded |
---|---|---|---|
1 | 2022-05-04 00:25:16 | 2022-05-04 00:30:16 | 1 |
3 | 2022-05-04 10:29:16 | 2022-05-04 10:40:16 | 1 |
1 | 2022-05-04 11:48:16 | 2022-05-04 11:49:56 | 1 |
3 | 2022-05-04 11:58:16 | 2022-05-04 12:20:16 | 3 |
1 | 2022-05-04 16:28:16 | 2022-05-04 17:29:56 | 4 |
Если пользователь не загрузил никаких файлов во время входа в систему, это покажет 0. Если вы не хотите показывать записи для пользователей, которые не загрузили никаких файлов, вы можете заменить левое соединение внутренним соединением и удалить coalesce
.
Большое спасибо, это отличное решение. Я не знал о функции объединения.
Извините, последняя запись нужной таблицы должна быть 4 в total_files_uploaded