首页 > 编程语言 > MySQL去除重叠时间求时间差和的实现
2022
05-17

MySQL去除重叠时间求时间差和的实现

         我个人并不推荐在实际开发中使用存储过程，充满了各种的不方便，之所以写这东西，全在于学习，如果有高手看到我的内容有问题，可以随时指出或向我开炮。 

需求：

        在生产中常常出现计算两个时间差的业务，比如总宕机时间、总开通会员时间等等。。。但是这些时间往往不是连贯的，断断续续，甚至可能会出现重叠的情况。无法直接求出时间差。

例如：

 开车：

        一开始，我想的是用单条SQL实现，例如：↓

?
1
SELECT TIMESTAMPDIFF(MINUTE, '2021-08-19 14:30:00', '2021-08-19 15:00:00') FROM DUAL;

 我发现，数据库数据千千万，不可能这样，也不可能用UNION这种东西去拼接，数据很多，就一定会有循环，所以，在不使用Java语言的情况下，我选择尝试用存储过程来解决以下这个问题。

思路：

         首先，一次进入循环的数据不会进行计算，防止后边的数据和它有重叠，

        从第二条数据开始，就要判断开始时间是否和上一个数据重叠，如果重叠，则校验结束时间是否也重叠，如果重叠我就啥也不干，不重叠，则把这个值赋给上一次的数据的结束时间。

        如果开始时间不再范围内，那么需要判断开始时间是在上一次时间的之前还是之后

        如果这个范围之前，把这个值赋给上一次的数据的开始时间。

        在这个范围之后，计算并赋值

        最后一次循环也要计算并赋值

实现：        

首先创建表，模拟数据

?
1
2
3
4
5
6
7
8
9
10
CREATE TABLE test01 (
  id int(32) unsigned NOT NULL AUTO_INCREMENT,
  start_time datetime NOT NULL,
  end_time datetime NOT NULL,
  PRIMARY KEY (`id`)
)
  
INSERT INTO test01(id, start_time, end_time) VALUES (1, '2021-08-18 16:27:51', '2021-08-18 17:27:59');
INSERT INTO test01(id, start_time, end_time) VALUES (2, '2021-08-18 17:20:26', '2021-08-18 20:10:37');
INSERT INTO test01(id, start_time, end_time) VALUES (3, '2021-08-18 22:05:57', '2021-08-18 23:55:20');

 

 创建存储过程：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
CREATE PROCEDURE sumTime()
BEGIN
    -- 定义变量
  
    -- 是否首次
    DECLARE is_old int(1) DEFAULT 0;
  
    -- 上一次数据
    DECLARE old_start_time datetime;
    DECLARE old_end_time datetime;
  
    -- 本次数据
    DECLARE start_time datetime;
    DECLARE end_time datetime;
  
    -- 返回结果
    DECLARE num int(32) DEFAULT 0;
  
    -- 循环结束开关
    DECLARE done int DEFAULT 0;
  
    -- 创建游标（查询数据库数据）
    DECLARE list CURSOR FOR SELECT a.start_time, a.end_time FROM test01 a;
  
    -- 定义最后一次循环时设置 循环结束开关 为 1
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  
    -- 开启游标
    OPEN list;
  
        -- 开启循环
        posLoop:LOOP
            -- 取值 将当前循环的值取出 赋值给当前数据变量
            FETCH list INTO start_time,end_time;
            -- 判断是否首次
            if (is_old = 0) THEN
  
                SET is_old = 1;
                SET old_start_time = start_time;
                SET old_end_time = end_time;
  
            -- 否则
            ELSE
                -- 校验是否在区间内
                 if (start_time >= old_start_time AND start_time <= old_end_time) THEN
  
                    -- 校验结束时间是否不在在区间内
                   if (end_time < old_start_time OR end_time > old_end_time) THEN
                        SET old_end_time = end_time;
                   END IF;
  
                 -- 否则
                 ELSE
  
                   if (start_time < old_start_time )  THEN
  
                        SET old_start_time = start_time;
  
                     ELSE
  
                        SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
                        SET old_start_time = start_time;
                        SET old_end_time = end_time;
                     END IF;
                 END IF;
            END IF;
            -- 校验是否最后一次循环
            IF done=1 THEN
                SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
                LEAVE posLoop;
            END IF;
        -- 结束循环
        END LOOP posLoop;
    -- 关闭游标
    CLOSE list;
    SELECT num;
END;
?
1
2
-- 调用存储过程
call sumTime();

 

?
1
2
-- 删除存储过程
drop procedure if exists sumTime;

到此这篇关于MySQL去除重叠时间求时间差和的实现的文章就介绍到这了,更多相关MySQL 求时间差和内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网！

编程技巧