用户在同一天的30分钟内执行的最大连续操作数 [英] max number of consecutive actions that the user performed within 30 minutes of the same day

问题描述

我有一个包含用户，操作和时间的表.我试图获取一天中的用户总操作以及用户在同一天的30分钟内执行的最大连续操作数.我是sql的新手，我发现了总操作根据用户，但是如何在30分钟内找到最大的连续动作.我在下面的查询

I have table with user,action and time.I was trying get user total action in day and max number of consecutive actions that the user performed within 30 minutes of the same day.I am new to sql ,I found total action by user but how to find max consecutive actions within 30 min .I tried below query

我选择了e.user，COUNT(e.user)个AS计数 从audit_log e 电子用户分组

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

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

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

For example lets assume that user 'userA' performed actions during the following timestamps:

     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

这一天将有一个类似于以下内容的条目:

There will be an entry for this day that is like the following:

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

推荐答案

已更新:

• 此新查询减少了连接数
• 使用一种新的方法进行范围计数和简单的联接进行计算.
• 在user和change_time字段上建立索引可用于使其更快.

• This new query reduces the number of joins,
• uses a new way to do range counting and simple joins to do the calculation.
• Indexing on user and change_time fields can be used here to make it faster.

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

Note: To pick records of particular time use WHERE condition on both tables on change_time fields.

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

旧查询:

就目前而言，如果我们不将性能作为考虑因素.这是给您想要的结果的查询.

For now, if we don't consider performance as a factor. Here is the query to give you the desired result.

• 首先，创建所有30分钟的范围，这些范围可以由用户执行最多连续的操作.

• First, create all 30 minutes ranges which can have max consecutive actions by a user.

第二，获取每个用户和每30分钟范围内的计数.

Second, get count per user and per 30-minute range.

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

Third, get total count from table directly and get max(30-minute) range count from the previous step.

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;

这篇关于用户在同一天的30分钟内执行的最大连续操作数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！