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

一尘不染

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

sql

我有包含用户，操作和时间的表。我试图获取用户在一天中的总操作以及用户在同一天的30分钟内执行的最大连续操作数。如何在30分钟内找到最大的连续动作。我尝试了以下查询

我选择了一个e.user，COUNT（e.user）个AS数来自audit_log e GROUP BY e.user

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');

阅读 117

2021-05-16

共1个答案

一尘不染

更新一 ：

此新查询减少了联接数，
使用一种新的方法进行范围计数，并使用简单的联接进行计算。
此处可以使用onuser和change_timefield的索引来使其更快。

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

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;

2021-05-16