MySQL根据特定规则在行后选择 [英] MySQL select after row on specific rules

问题描述

我有一个这样的表:

CREATE TABLE IF NOT EXISTS `logging` (
  `id` int(6) unsigned NOT NULL,
  `status` varchar(150) NOT NULL,
  `timestamp` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `logging` (`id`, `status`, `timestamp`) VALUES
  ('1', 'logout', '2021-01-01 05:01:00'),
  ('2', 'login', '2021-01-01 06:02:00'),
  ('3', 'online', '2021-01-01 06:03:00'),
  ('4', 'away', '2021-01-01 06:04:00'),
  ('5', 'online', '2021-01-01 06:05:00'),
  ('6', 'logout', '2021-01-02 04:00:00'),
  ('7', 'login', '2021-01-02 04:05:00'),
  ('8', 'online', '2021-01-02 04:07:00'),
  ('9', 'away', '2021-01-02 04:08:00'),
  ('10', 'break', '2021-01-02 04:10:00'),
  ('11', 'online', '2021-01-02 04:15:00'),
  ('12', 'logout', '2021-01-02 04:55:00'),
  ('13', 'login', '2021-01-02 05:04:00'),
  ('14', 'online', '2021-01-02 05:05:00'),
  ('15', 'away', '2021-01-03 05:01:00'),
  ('16', 'logout', '2021-01-03 05:02:00'),
  ('17', 'login', '2021-01-03 05:04:00'),
  ('18', 'online', '2021-01-03 05:05:00'),
  ('19', 'logout', '2021-01-04 03:05:00'),
  ('20', 'login', '2021-01-04 05:07:00'),
  ('21', 'online', '2021-01-04 06:00:00'),
  ('22', 'logout', '2021-01-05 10:00:00'),
  ('23', 'login', '2021-01-05 11:00:00'),
  ('24', 'away', '2021-01-05 11:01:00'),
  ('25', 'online', '2021-01-06 06:01:00'),
  ('26', 'login', '2021-01-07 06:01:00'),
  ('26', 'logout', '2021-01-07 07:01:00');

<身体>

id	状态	时间戳
1	注销	2021-01-01 05:01:00
2	登录	2021-01-01 06:02:00
3	在线	2021-01-01 06:03:00
4	离开	2021-01-01 06:04:00
5	在线	2021-01-01 06:05:00
6	注销	2021-01-02 04:00:00
7	登录	2021-01-02 04:05:00
8	在线	2021-01-02 04:07:00
9	注销	2021-01-02 04:55:00

......

上面的插入查询中提供的数据.

data provided on the insert query above.

我想要一个输出:

<身体>

日期	A(在线)	B(注销)
2021-01-01	2021-01-02 04:07:00	2021-01-02 04:55:00
2021-01-02	2021-01-02 05:05:00	2021-01-03 04:59:59
2021-01-03	2021-01-03 05:05:00	2021-01-04 03:05:00
2021-01-04	2021-01-04 06:00:00	2021-01-04 04:59:59
2021-01-05	2021-01-04 05:00:00	2021-01-05 10:00:00
2021-01-06	2021-01-04 11:00:00	2021-01-06 04:59:59

规则是1个日志日是从5:00:00-(第二天)04:59:59开始."A"是从上次联机开始的时间戳记(登录后，如果有的话)，"B"是从上次注销开始的时间戳记(如果没有注销，则B设置为04:59:59 .另一个规则是，当最后一天在在线"之后没有注销"时，将被计入下一个日志日(如果下一个日志日的最后一条记录为'，则将下一个日志日设置为05.00.00").在线" )

the rule is, 1 log day is from 5:00:00 - (next day) 04:59:59. 'A' is timestamp start from the last online (after login, if any), 'B' is timestamp from the last logout (if there is no logout, B set to 04:59:59. Another rule is, when the last day doesnt have 'logout' after 'online', it's counted to the next log day (the next log day set to be 05.00.00 if the last record from the last log day is 'online')

当前，我正在使用此查询来应用日志日规则:

currently i'm using this query to apply the log day rule:

SELECT date(t1.timestamp) dt, (t2.timestamp) A, (t3.timestamp) B, t1.status, t2.status, t3.status
FROM logging t1
JOIN logging t2 ON t1.timestamp < t2.timestamp
JOIN logging t3 ON t2.timestamp < t3.timestamp
WHERE 
t1.status = 'login'
AND t2.status = 'online'
AND t3.status = 'logout'
AND NOT EXISTS ( SELECT NULL
               FROM loggingt4
               WHERE t1.timestamp < t4.timestamp
                 AND t4.timestamp < t2.timestamp
                 AND t4.status IN ('login', 'online', 'logout') )
AND NOT EXISTS ( SELECT NULL
               FROM logging t5
               WHERE t2.timestamp < t5.timestamp
                 AND t5.timestamp < t3.timestamp
                 AND t5.status IN ('login', 'logout'))
AND DATE(t1.timestamp - INTERVAL 6 HOUR) = DATE(t3.timestamp - INTERVAL '05:59:59' HOUR_SECOND);

推荐答案

检查此内容:

WITH cte AS (
SELECT DATE(t1.`timestamp` - INTERVAL 5 HOUR) `date`,
       MAX(t1.`timestamp`) login, 
       MAX(t2.`timestamp`) online, 
       MAX(t3.`timestamp`) logout
FROM logging t1
JOIN logging t2 ON t1.`timestamp` < t2.`timestamp`
JOIN logging t3 ON t2.`timestamp` < t3.`timestamp`
WHERE t1.status = 'login'
  AND t2.status = 'online'
  AND t3.status = 'logout'
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t4
                   WHERE t1.`timestamp` < t4.`timestamp`
                     AND t4.`timestamp` < t2.`timestamp`
                     AND t4.status IN ('login', 'online', 'logout') )
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t5
                   WHERE t2.`timestamp` < t5.`timestamp`
                     AND t5.`timestamp` < t3.`timestamp`
                     AND t5.status IN ('login', 'logout') )
GROUP BY `date`
)
SELECT `date`,
       online,
       CASE WHEN DATE(online - INTERVAL 5 HOUR) = DATE(logout - INTERVAL '04:59:59' HOUR_SECOND)
            THEN logout
            ELSE DATE(online + INTERVAL 19 HOUR) + INTERVAL '04:59:59' HOUR_SECOND
            END logout
FROM cte

小提琴

CTE仅用于可见性-您可以将所有内容组合到一个查询中.

CTE is used for visibility only - you may combine everything into one query.

这篇关于MySQL根据特定规则在行后选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！