MySQL去除重疊時間求時間差和的實現

Posted on by

         我個人並不推薦在實際開發中使用存儲過程，充滿瞭各種的不方便，之所以寫這東西，全在於學習，如果有高手看到我的內容有問題，可以隨時指出或向我開炮。 

需求：

        在生產中常常出現計算兩個時間差的業務，比如總宕機時間、總開通會員時間等等。。。但是這些時間往往不是連貫的，斷斷續續，甚至可能會出現重疊的情況。無法直接求出時間差。

例如：

 開車：

        一開始，我想的是用單條SQL實現，例如：↓

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

 我發現，數據庫數據千千萬，不可能這樣，也不可能用UNION這種東西去拼接，數據很多，就一定會有循環，所以，在不使用Java語言的情況下，我選擇嘗試用存儲過程來解決以下這個問題。

思路：

         首先，一次進入循環的數據不會進行計算，防止後邊的數據和它有重疊，

        從第二條數據開始，就要判斷開始時間是否和上一個數據重疊，如果重疊，則校驗結束時間是否也重疊，如果重疊我就啥也不幹，不重疊，則把這個值賦給上一次的數據的結束時間。

        如果開始時間不再范圍內，那麼需要判斷開始時間是在上一次時間的之前還是之後

        如果這個范圍之前，把這個值賦給上一次的數據的開始時間。

        在這個范圍之後，計算並賦值

        最後一次循環也要計算並賦值

實現：        

首先創建表，模擬數據

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

 

 創建存儲過程：

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;
-- 調用存儲過程
call sumTime();

 

-- 刪除存儲過程
drop procedure if exists sumTime;

到此這篇關於MySQL去除重疊時間求時間差和的實現的文章就介紹到這瞭,更多相關MySQL 求時間差和內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：