获取MySQL中不同DateTime范围值的计数

Question

我的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表记录文件文件记录由用户上传。 我正在寻找一个查询，该查询允许在第一表确定的DateTime范围内获取上载文件计数。 这将是我要寻找的结果的看法。

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
--------------------------------------------------------|---------------------|

Answer 1

您可以根据用户ID和登录时间加入表格，然后计算上传：

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;

输出：

user_id	login_datetime	logout_datetime	total_files_files_uploaded
1	2022-05-04 004 00:25:16	2022-2022-055-05-04 00:30:16	1
3	20222-1 3 20222-1 3 20222-1 3 20222-13 20222-04 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。如果您不想为未上传任何文件的用户显示条目，则可以用内部加入左JOIN并删除coalesce。