mysql - 用户在同一天的 30 分钟内执行的最大连续操作数

Question

我有一个包含用户、操作和时间的表。我正在尝试获取当天的用户总操作数以及用户在同一天的 30 分钟内执行的最大连续操作数。我是 sql 的新手，我发现了总操作数按用户但如何在 30 分钟内找到最大连续操作。我尝试了以下查询

I SELECT e.user, COUNT(e.user) AS 计数FROM audit_log e按电子用户分组

SELECT  e.user, COUNT(e.user) AS count
FROM audit_log e
GROUP BY e.user

例如，假设用户“userA”在以下时间戳中执行了操作:

     time        action            user
* 08:05:10       edit                A
* 08:05:40       create              B
* 08:06:50       insert              A
* 08:30:20       ...                 .
* 08:31:50
* 08:35:10
* 10:00:50
* 12:34:32       call                A
* 12:43:23       delete              A
* 12:44:00       listen              A
* 12:45:52
* 12:45:59
* 12:46:59
* 13:04:33
* 16:30:21
* 18:04:47
* 18:05:02
* 20:20:20
* 23:39:21

这一天会有一个条目，如下所示:

| username | total action count | number of consecutive actions |
|  userA   |                 19 |                             6 |

这里是sql数据

CREATE TABLE `audit_log` (
  `id` bigint(20) NOT NULL,
  `action_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `change_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_log`
--

INSERT INTO `audit_log` (`id`, `action_name`, `user`, `change_time`) VALUES
(1, 'deploy', 'Cristina Fitzgerald', '2018-05-15 06:38:00'),
(2, 'delete', 'Kobie Tillman', '2018-05-15 06:53:00'),
(3, 'modify', 'Willa Sharpe', '2018-05-15 07:08:00'),
(4, 'delete', 'Aimee Walter', '2018-05-15 07:14:00'),
(5, 'delete', 'Willa Sharpe', '2018-05-15 07:28:00'),
(6, 'modify', 'Cristina Fitzgerald', '2018-05-15 07:38:00'),
(7, 'deploy', 'Willa Sharpe', '2018-05-15 08:28:00'),
(8, 'modify', 'Aimee Walter', '2018-05-15 09:14:00'),
(9, 'deploy', 'Kobie Tillman', '2018-05-15 09:53:00'),
(10, 'delete', 'Aimee Walter', '2018-05-15 10:09:00'),
(11, 'modify', 'Aimee Walter', '2018-05-15 10:29:00'),
(12, 'modify', 'Cristina Fitzgerald', '2018-05-15 10:38:00'),
(13, 'delete', 'Cristina Fitzgerald', '2018-05-15 11:06:00'),
(14, 'create', 'Aimee Walter', '2018-05-15 11:23:00'),
(15, 'create', 'Cristina Fitzgerald', '2018-05-15 11:44:00'),
(16, 'deploy', 'Aimee Walter', '2018-05-15 11:45:00'),
(17, 'create', 'Aimee Walter', '2018-05-15 11:58:00'),
(18, 'delete', 'Cristina Fitzgerald', '2018-05-15 12:19:00'),
(19, 'delete', 'Cristina Fitzgerald', '2018-05-15 12:23:00'),
(20, 'modify', 'Aimee Walter', '2018-05-15 12:31:00'),
(21, 'delete', 'Cristina Fitzgerald', '2018-05-15 13:14:00'),
(22, 'deploy', 'Aimee Walter', '2018-05-15 13:21:00'),
(23, 'create', 'Aimee Walter', '2018-05-15 13:24:00'),
(24, 'deploy', 'Willa Sharpe', '2018-05-15 13:28:00'),
(25, 'modify', 'Kobie Tillman', '2018-05-15 13:53:00'),
(26, 'delete', 'Kobie Tillman', '2018-05-15 14:23:00'),
(27, 'delete', 'Kobie Tillman', '2018-05-15 14:30:00'),
(28, 'create', 'Kobie Tillman', '2018-05-15 14:39:00'),
(29, 'deploy', 'Kobie Tillman', '2018-05-15 15:13:00'),
(30, 'modify', 'Willa Sharpe', '2018-05-15 15:17:00'),
(31, 'modify', 'Willa Sharpe', '2018-05-15 15:27:00'),
(32, 'create', 'Kobie Tillman', '2018-05-15 15:32:00'),
(33, 'modify', 'Kobie Tillman', '2018-05-15 15:53:00'),
(34, 'deploy', 'Kobie Tillman', '2018-05-15 16:00:00'),
(35, 'delete', 'Kobie Tillman', '2018-05-15 16:59:00'),
(36, 'deploy', 'Kobie Tillman', '2018-05-15 18:59:00'),
(37, 'modify', 'Aimee Walter', '2018-05-15 19:24:00'),
(38, 'delete', 'Willa Sharpe', '2018-05-15 19:27:00'),
(39, 'modify', 'Kobie Tillman', '2018-05-15 19:34:00'),
(40, 'delete', 'Aimee Walter', '2018-05-15 20:02:00'),
(41, 'delete', 'Willa Sharpe', '2018-05-15 20:09:00'),
(42, 'deploy', 'Aimee Walter', '2018-05-15 20:18:00'),
(43, 'delete', 'Willa Sharpe', '2018-05-15 20:40:00'),
(44, 'modify', 'Willa Sharpe', '2018-05-15 20:59:00'),
(45, 'create', 'Cristina Fitzgerald', '2018-05-15 21:14:00'),
(46, 'deploy', 'Kobie Tillman', '2018-05-15 21:34:00'),
(47, 'modify', 'Kobie Tillman', '2018-05-15 21:40:00'),
(48, 'create', 'Willa Sharpe', '2018-05-15 21:59:00'),
(49, 'delete', 'Willa Sharpe', '2018-05-15 22:51:00'),
(50, 'create', 'Willa Sharpe', '2018-05-15 23:12:00');

Answer 1

更新一:

• 这个新查询减少了连接的数量，
• 使用新方法进行范围计数和简单连接来进行计算。
• 索引 user 和 change_time 字段可以在此处使用以使其更快。

注意:要选择特定时间的记录，请在两个表的 change_time 字段上使用 WHERE 条件。

SELECT 
    `user`,
    COUNT(0) as Total_Count,
    MAX(range_count) Max_Range_Count
FROM (
    SELECT
        a.`user`, 
        a.change_time, 
        COUNT(0) range_count
    FROM audit_log a
    INNER JOIN audit_log b ON a.`user` = b.`user` 
    WHERE b.change_time BETWEEN a.change_time AND a.change_time + INTERVAL 30 MINUTE
    GROUP BY a.`user`, a.change_time
) AS user_range_count
GROUP BY `user`;

旧查询:

目前，如果我们不考虑性能作为一个因素。这是为您提供所需结果的查询。

• 首先，创建所有 30 分钟的范围，该范围可以包含用户的最大连续操作。

• 其次，获取每位用户和每 30 分钟范围内的计数。

• 第三，直接从表中获取总计数，并从上一步中获取最大(30 分钟)范围计数。

SELECT audit_log.user, COUNT(DISTINCT id), MAX(b.time_range_count)
FROM audit_log 
INNER JOIN (    
    SELECT audit_log.user, a.time_range, COUNT(0) as time_range_count
    FROM audit_log
    INNER JOIN (
        SELECT 
            CONCAT_WS(' - ',change_time, change_time + INTERVAL 30 MINUTE) AS time_range, 
            change_time, 
            change_time + INTERVAL 30 MINUTE change_time_30
        FROM audit_log
        ORDER BY change_time
    ) AS a ON audit_log.change_time BETWEEN a.change_time AND a.change_time_30
    GROUP BY audit_log.user, a.time_range
) AS b ON audit_log.user = b.user
GROUP BY audit_log.user;