SQL统计24小时重复登录

Question

通过SQL获得如下图所示结果，计算重复登录的相关信息，登录流水系统自动生成不重复。

C

Answer 1

假设T1表的结构如下：

login_id	login_time	用户名
1	2023-02-16 08:00:00	用户1
2	2023-02-16 09:00:00	用户
3	2023-02-15 23:30:00	用户1
4	2023-02-15 22:00:00	用户3
5	2023-02-15 15:00:00	用户4
6	2023-02-14 18:00:00	用户2
7	2023-02-14 12:00:00	用户3

下面是SQL代码：

SELECT 
  T1.login_id, 
  T1.login_time, 
  T1.username
FROM 
  T1 
  INNER JOIN (
    SELECT 
      username, 
      MAX(login_time) as latest_login_time 
    FROM 
      T1 
    GROUP BY 
      username 
  ) T2 
  ON T1.username = T2.username 
  AND T1.login_time = T2.latest_login_time 
  AND T1.login_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

以上SQL语句通过内部查询T1表中每个用户名的最新登录时间，然后将T1表和T2表连接，以获取最近登录的所有记录，且登录时间在最近的24小时内。这个查询将返回以下结果：

login_id	login_time	用户名
1	2023-02-16 08:00:00	用户1
2	2023-02-16 09:00:00	用户2

接下来，我们可以使用以下SQL语句来计算24小时内的重复登录信息：

SELECT 
  T1.username, 
  COUNT(*) AS repeat_login_count, 
  GROUP_CONCAT(DISTINCT T1.login_id SEPARATOR ',') AS repeat_login_ids 
FROM 
  T1 
  INNER JOIN (
    SELECT 
      username, 
      MAX(login_time) as latest_login_time 
    FROM 
      T1 
    GROUP BY 
      username 
  ) T2 
  ON T1.username = T2.username 
  AND T1.login_time = T2.latest_login_time 
  AND T1.login_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY 
  T1.username 
HAVING 
  COUNT(*) > 1

这个查询会返回在24小时内至少重复登录了一次的用户信息，其中包括用户名，重复登录次数以及登录的流水号，例如：

用户名	repeat_login_count	repeat_login_ids
用户1	2	1,3
用户2	2	2,6

在这个例子中，user1和user2在最近的24小时内重复登录了2次，分别是1，3和2，6。