[MySQL]同一張資料表的日期欄位相減_派生表(dervied table)的應用

以往對於MySQL數據庫的應用，大多是基本的增刪改查步驟，因此MySQL大多停留在基本語法階段。然而在實際的數據應用上，若能從數據庫直接運算和抽取運算結果，同時不需要在MySQL中額外建立一個實體表，再好不過。

在MySQL中，虛擬表可符合上述需求，其中虛擬表包含臨時表(temporary table)、派生表(derived table)等等。臨時表的語法需注意嵌入以CREAT TEMPORARY TABLE...為開頭；派生表類似臨時表，語法為在SELECT...FROM中嵌入子查詢語句，同時須注意使用派生表必須具有別名，避免MySQL報錯。

此次用同一個資料表的日期欄位相減，取得設備的每日運作累積時間為範例，說明派生表的語法使用方式。使用情境為當設備停機或運作時，連線到MySQL同步紀錄，僅僅是datetime和status的紀錄，其raw data如圖一所示。

圖一

 

a.首先，從datecaltest資料表中，抽取出日期作為獨立欄位。

SELECT datetime, status, Date(datetime) AS date FROM datecaltest;

圖二

b.利用LAG()...OVER()將datetime欄位做向下一個欄位的錯位，並新增為獨立欄位 datatime_2。須提醒的是LAG()...OVER()只能在MySQL 8.0以上的版本調用。

SELECT datetime, status,Date(datetime) AS date,LAG(datetime,1) OVER(ORDER BY datetime DESC) as datetime2 FROM datecaltest;

圖三

 

c.使datatime和datetime1相減做為timedelta欄位，同時用date作為分區參考，並依照datetime排序。

SELECT status,datetime,datetime2,Date(datetime) AS date,timestampdiff(SECOND,datetime,datetime2) AS timedelta 
FROM(
	SELECT status,datetime,Date(datetime) AS date, LAG(datetime,1) OVER(PARTITION by date      
    ORDER BY datetime DESC) as datetime2 FROM datecaltest) AS a  
ORDER BY `a`.`datetime` DESC

圖四

 

d.將同一個日期的Runnning的timedelta欄位加總為累積時間Run_acctime，表示當天設備開啟總時間。

SELECT Date(datetime) AS date,sum(timedelta) AS Run_acctime_sec 
FROM(
	SELECT status,datetime,datetime2,Date(datetime) AS date,timestampdiff(SECOND,datetime,datetime2) AS timedelta 
		FROM(
			SELECT status,datetime,Date(datetime) AS date, LAG(datetime,1) OVER(PARTITION by date ORDER BY datetime DESC) as datetime2 FROM datecaltest
	    	) AS a
    ) AS b 	
WHERE status='Running!'  GROUP BY date DESC	ORDER BY date DESC

圖五

 

一般觀察設備運作的週期，大多由今日前算起的前一段週期，因此若要進一步使該派生表僅體現由今日算起的前一個月內的設備運作累積時間，語法應如何修正，供各位延續思考和應用。

Reference:

1.MySQL派生表 -MySQL教程

2.浅谈MYSQL中的基本表、中间表、临时表、派生表和视图_中间表和临时表的区别_SunBairn的博客-CSDN博客