MySQL去除重疊時(shí)間求時(shí)間差和的實(shí)現(xiàn)
我個(gè)人并不推薦在實(shí)際開(kāi)發(fā)中使用存儲(chǔ)過(guò)程，充滿了各種的不方便，之所以寫這東西，全在于學(xué)習(xí)，如果有高手看到我的內(nèi)容有問(wèn)題，可以隨時(shí)指出或向我開(kāi)炮。
需求：
在生產(chǎn)中常常出現(xiàn)計(jì)算兩個(gè)時(shí)間差的業(yè)務(wù)，比如總宕機(jī)時(shí)間、總開(kāi)通會(huì)員時(shí)間等等。。。但是這些時(shí)間往往不是連貫的，斷斷續(xù)續(xù)，甚至可能會(huì)出現(xiàn)重疊的情況。無(wú)法直接求出時(shí)間差。
例如：
開(kāi)車：
一開(kāi)始，我想的是用單條SQL實(shí)現(xiàn)，例如：↓
SELECT TIMESTAMPDIFF(MINUTE, '2021-08-19 14:30:00', '2021-08-19 15:00:00') FROM DUAL;
我發(fā)現(xiàn)，數(shù)據(jù)庫(kù)數(shù)據(jù)千千萬(wàn)，不可能這樣，也不可能用UNION這種東西去拼接，數(shù)據(jù)很多，就一定會(huì)有循環(huán)，所以，在不使用Java語(yǔ)言的情況下，我選擇嘗試用存儲(chǔ)過(guò)程來(lái)解決以下這個(gè)問(wèn)題。
思路：
首先，一次進(jìn)入循環(huán)的數(shù)據(jù)不會(huì)進(jìn)行計(jì)算，防止后邊的數(shù)據(jù)和它有重疊，
從第二條數(shù)據(jù)開(kāi)始，就要判斷開(kāi)始時(shí)間是否和上一個(gè)數(shù)據(jù)重疊，如果重疊，則校驗(yàn)結(jié)束時(shí)間是否也重疊，如果重疊我就啥也不干，不重疊，則把這個(gè)值賦給上一次的數(shù)據(jù)的結(jié)束時(shí)間。
如果開(kāi)始時(shí)間不再范圍內(nèi)，那么需要判斷開(kāi)始時(shí)間是在上一次時(shí)間的之前還是之后
如果這個(gè)范圍之前，把這個(gè)值賦給上一次的數(shù)據(jù)的開(kāi)始時(shí)間。
在這個(gè)范圍之后，計(jì)算并賦值
最后一次循環(huán)也要計(jì)算并賦值
實(shí)現(xiàn)：
首先創(chuàng)建表，模擬數(shù)據(jù)
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');
創(chuàng)建存儲(chǔ)過(guò)程：
CREATE PROCEDURE sumTime()
BEGIN
-- 定義變量
-- 是否首次
DECLARE is_old int(1) DEFAULT 0;
-- 上一次數(shù)據(jù)
DECLARE old_start_time datetime;
DECLARE old_end_time datetime;
-- 本次數(shù)據(jù)
DECLARE start_time datetime;
DECLARE end_time datetime;
-- 返回結(jié)果
DECLARE num int(32) DEFAULT 0;
-- 循環(huán)結(jié)束開(kāi)關(guān)
DECLARE done int DEFAULT 0;
-- 創(chuàng)建游標(biāo)（查詢數(shù)據(jù)庫(kù)數(shù)據(jù)）
DECLARE list CURSOR FOR SELECT a.start_time, a.end_time FROM test01 a;
-- 定義最后一次循環(huán)時(shí)設(shè)置 循環(huán)結(jié)束開(kāi)關(guān) 為 1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- 開(kāi)啟游標(biāo)
OPEN list;
-- 開(kāi)啟循環(huán)
posLoop:LOOP
-- 取值 將當(dāng)前循環(huán)的值取出 賦值給當(dāng)前數(shù)據(jù)變量
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
-- 校驗(yàn)是否在區(qū)間內(nèi)
if (start_time >= old_start_time AND start_time <= old_end_time) THEN
-- 校驗(yàn)結(jié)束時(shí)間是否不在在區(qū)間內(nèi)
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;
-- 校驗(yàn)是否最后一次循環(huán)
IF done=1 THEN
SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
LEAVE posLoop;
END IF;
-- 結(jié)束循環(huán)
END LOOP posLoop;
-- 關(guān)閉游標(biāo)
CLOSE list;
SELECT num;
END;
-- 調(diào)用存儲(chǔ)過(guò)程 call sumTime();
-- 刪除存儲(chǔ)過(guò)程 drop procedure if exists sumTime;
到此這篇關(guān)于MySQL去除重疊時(shí)間求時(shí)間差和的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 求時(shí)間差和內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信