【SQL分享】統計工廠設備每天運作狀況時間

sql

暐翰 2018-06-10 18:42:33 ‧ 8194 瀏覽

分享至

最近處理一個滿有趣的案子提供大大們來玩玩

假設在一間工廠裡面
只有一個歷史表要去解析:"以天為準查找機台的所有運作狀態、時間"

表格結構類似:

SID	Equipment	PreviousTime	TranstionTime	PreviousStatus	Status
0	A	2018/6/6 下午 12:00:00	2018/6/7 上午 09:00:00	停機	運行
1	A	2018/6/7 上午 09:00:00	2018/6/7 上午 10:00:00	運行	停機
2	A	2018/6/7 上午 10:00:00	2018/6/7 下午 12:00:00	停機	運行
3	A	2018/6/7 下午 12:00:00	2018/6/7 下午 02:00:00	運行	維修
4	A	2018/6/7 下午 02:00:00	2018/6/9 上午 10:00:00	維修	停機
5	A	2018/6/9 上午 10:00:00	2018/6/9 下午 12:00:00	停機	運行
6	B	2018/6/7 上午 09:00:00	2018/6/7 上午 10:00:00	運行	停機
7	B	2018/6/7 上午 10:00:00	2018/6/7 下午 12:00:00	停機	運行
8	B	2018/6/7 下午 12:00:00	2018/6/7 下午 02:00:00	運行	維修
9	B	2018/6/7 下午 02:00:00	2018/6/9 上午 10:00:00	維修	停機
10	B	2018/6/9 上午 10:00:00	2018/6/9 下午 12:00:00	停機	運行

附註:

欄位註解:
Equipment 設備
PreviousTime 上個改變時間
TranstionTime 改變時間
PreviousStatus 上個狀態
Status 更改狀態
所有狀態:
運行、停機、維修
SID是由舊往新編排
表格資料量實際會到百萬級，所以需要考量效率問題
早上八點到隔天八點算一天 (不是半夜12到隔天半夜12點算一天)
能查詢7天內資料、不需要處理當天資料
時間單位為分

期望資料:

6/6是A設備剛購入，所以那天只有1200分鐘
6/7是B設備剛購入，所以那天總共只有1380分鐘
假如是該機台最後一筆資料,代表之後動作都是一樣。
舉例:
A機台最後一個動作是運行,代表6/9號 12:00 - 6/10 08:00 都是運行動作。

日期	設備	停機時間	運行時間	維修時間	總共時間
2018/6/6	A	1200	Null	Null	1200
2018/6/7	A	180	180	1080	1440
2018/6/7	B	120	180	1080	1380
2018/6/8	A	Null	Null	1440	1440
2018/6/8	B	Null	Null	1440	1440
2018/6/9	A	120	1200	120	1440
2018/6/9	B	120	1200	120	1440

我的解法:

邏輯寫在Script註解
主要問題、難點其實是在跨日資料如何切分時間、如何有效率找到前一筆下一筆資料

--【建立測試資料】
IF object_id('tempdb.dbo.#Table1','U') IS NOT NULL
	DROP TABLE #Table1; 

CREATE TABLE #Table1
	([SID] int, [Equipment] nvarchar(1), [PreviousTime] datetime, [TranstionTime] datetime, [PreviousStatus] nvarchar(20), [Status] nvarchar(20))
;
	
INSERT INTO #Table1
	([SID], [Equipment], [PreviousTime], [TranstionTime], [PreviousStatus], [Status])
VALUES
	(0, 'A', '2018-06-06 12:00:00', '2018-06-07 09:00:00', N'停機', N'運行'),
	(1, 'A', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(2, 'A', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(3, 'A', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(4, 'A', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(5, 'A', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行')
	,
	(6, 'B', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(7, 'B', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(8, 'B', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(10, 'B', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行')
;
--select * from #Table1

--【Script】
declare  @DateToday datetime  = CONVERT(datetime ,CONVERT (date, '2018/06/10')) --資料以日期6/10來測試
	,@DayRange int = 7 --決定可以查幾天資料
	,@TimeDiff int = 8 --決定以幾點劃分一天
;
--【第一步】找出所有的機台
with CTE_Equips as (
	select [Equipment]
	from #Table1
	group by [Equipment]
--【第二步】使用CTE遞迴得出一個禮拜資料,並以機台為一方資料left join日期
),CTE_DATES as (
  SELECT 1 as i
  UNION ALL
  SELECT  i + 1
  FROM CTE_DATES 
  WHERE i < @DayRange
)
,CTE_BASIC_DATA as (
	select * from CTE_Equips
	left join (
		select *
			,DATEADD ( hour,@TimeDiff,[date]) [StartDatetime]
			,DATEADD ( hour,@TimeDiff+24,[date]) [EndDatetime]
		from (
			select DATEADD ( day,-i,@DateToday) [date] 
			from CTE_DATES	
		) T
	) T on 1 =1
)
--select * from CTE_BASIC_DATA
--【第三步】按照機台以時間排序，排出從舊到新的SID，方便之後以SID+1-1方式取得上一個操作或下一個操作的資料
,CTE_RANK as (
		select *,row_number() over (partition by [Equipment] order by [PreviousTime]) rnk from #Table1 
)
,CTE_Result as (
	select  T1.* 
		,case when T3.[SID] is not null then T3.[SID]
		else T2.[SID]
		end  [SID]
		,case when T3.[SID] is not null then T1.[StartDatetime]
		else T2.[PreviousTime]
		end  [PreviousTime]
		,case when T3.[SID] is not null or T2.[TranstionTime] > T1.[EndDatetime] then T1.[EndDatetime]
			else T2.[TranstionTime]
		end  [TranstionTime]
		/*
			假如是該機台最後一筆資料,代表之後動作都是一樣
			舉例:
				前一天晚上7點最後一個動作是運行
				那麼7點到早上八點狀態都會是運行
				所以這邊要抓現在狀態 T3.[Status]

				反之不是最後一個動作
				那麼要抓前一個狀態 T3.[PreviousStatus]
		*/
		,row_number() over (partition by T1.[Equipment] order by T2.[PreviousTime] desc) descrnk
		,case when T3.[SID] is not null then 
			case when row_number() over (partition by T1.[Equipment] order by T2.TranstionTime desc) =1 then T3.[Status]
			else T3.[PreviousStatus]
			end 
		else T2.[PreviousStatus]
		end  [PreviousStatus]
		--假如是跨日資料不會有T3的資料,所以狀態要抓T2為準
		,case when T3.[SID] is not null then null
		else T2.[Status]
		end  [Status]
		/*
			假如當天第一筆資料時間不是當天早上8點
			這邊先把排名SID紀錄下來 (rnk - 1) = 該機台上一個動作資料
			好後續去抓前一個歷史資料的執行時間
		*/
		,case when row_number() over (partition by T1.[Equipment],T1.[Date] order by T2.[PreviousTime]) = 1 
			and T2.[PreviousTime] > [StartDatetime]
		then
			T2.rnk -1
		end
		PreviousOne
		--確定是不是該機台最後一筆資料
		,case when row_number() over (partition by T1.[Equipment] order by T2.[PreviousTime] desc) =1 
			and T2.TranstionTime < [EndDatetime]
		then
			'Y'
		end as MaxOne
	from CTE_BASIC_DATA T1
	left join CTE_RANK T2 on T1.[Equipment] = T2.[Equipment] 
		and T2.[PreviousTime] >=  [StartDatetime] and T2.[PreviousTime] < [EndDatetime]	
	/*
		T3決定的是跨天資料:
		1.假如歷史資料是跨天，需要新增一筆跨天時間區段:"跨天開始時間-跨天結束時間"
			舉例:
			6/1號 16:00 做運行 - 6/3號 12:00才做停機動作
			可以推論出6/2號的時間08:00 - 6/3號08:00的動作都是運行	
		2.因為是跨天，所以用"< 資料日期"+"倒序"+"第一筆資料"(這邊變相使用Max(SID)) 查到跨天的資料
	*/
	left join CTE_RANK T3 on T2.sid is null
		and T1.[Equipment] = T3.[Equipment] 
		and T3.sid = (select max(sid) from #Table1 T4 where T4.[Equipment] = T1.[Equipment] and T4.[PreviousTime] < T1.[StartDatetime])
	where T3.[SID] is not null or T2.[SID] is not null
) 
--SELECT * FROM CTE_Result order by Equipment,[Date]
,CTE_GROUP as (
	select * from (
		/*
			1.
			2.以PreviousOne來當flag，當PreviousOne <= 0代表前一筆資料是null
		*/
		select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID]
			,T1.StartDatetime  [PreviousTime]
			,T1.[PreviousTime] TranstionTime
			,T2.PreviousStatus [Status]
		from (
			select * from CTE_Result
			where 1=1
			and PreviousOne is not null
			and PreviousOne > 0 --排除歷史空資料
		) T1
		left join CTE_RANK T2 on T1.Equipment = T2.Equipment and T1.PreviousOne = T2.rnk

		union all
	
		/*
			1.假如是最後一筆操作，但更改時間<當天結束時間，需要添加一筆時間區間:"更改時間-當天結束時間"
			2.以MaxOne當Flag
		*/
		select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID]
			,T1.TranstionTime  [PreviousTime]
			,T1.EndDatetime TranstionTime
			,T1.[Status] 
		from CTE_Result T1
		where MaxOne = 'Y'


		union all

		select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID] ,T1.PreviousTime,T1.TranstionTime,T1.PreviousStatus [Status]
		from CTE_Result T1
	) T
)
--SELECT * FROM CTE_GROUP
select *,ISNULL(停機時間,0)+ISNULL(運行時間,0)+ISNULL(維修時間,0) 總共時間 from (
	select Convert(date,[Date]) 日期,Equipment 設備
		,sum(case when [Status] = N'停機' then  datediff(minute,PreviousTime,TranstionTime) end)   停機時間 
		,sum(case when [Status] = N'運行' then  datediff(minute,PreviousTime,TranstionTime) end)   運行時間 
		,sum(case when [Status] = N'維修' then  datediff(minute,PreviousTime,TranstionTime) end)   維修時間 
	from CTE_GROUP
	group by Equipment,[Date]
) T
order by 日期,設備

db<>fiddle Demo Link

我是用硬幹的方式把資料寫出來，所以有任何錯誤或是問題還請大大們糾正。
假如有其他資料庫作法或是更好的方式
大大們都可以提出、討論。

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 則留言

純真的人

iT邦大師 1 級 ‧ 2018-06-10 21:02:00

嗯@@..ok~~寫法跟你不一樣~但結果沒問題了~

T-SQL修正第2板

declare @Tmp table(
	[SID] int
	, [Equipment] nvarchar(1)
	, [PreviousTime] datetime
	, [TranstionTime] datetime
	, [PreviousStatus] nvarchar(20)
	, [Status] nvarchar(20)
)

INSERT INTO @Tmp
	([SID], [Equipment], [PreviousTime], [TranstionTime], [PreviousStatus], [Status])
VALUES
	(0, 'A', '2018-06-06 12:00:00', '2018-06-07 09:00:00', N'停機', N'運行'),
	(1, 'A', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(2, 'A', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(3, 'A', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(4, 'A', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(5, 'A', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行'),
	(6, 'B', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(7, 'B', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(8, 'B', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(10, 'B', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行')

declare @Tmp2 table(
	[SID] int
	,[Equipment] nvarchar(1)
	,[Cal_Date] datetime
	,[Cal_Minute] int
	,[PreviousStatus] nvarchar(20)
)

declare @StartDate datetime,@EndDate datetime,@Name nvarchar(1),@Statu nvarchar(20)
declare @i int ,@Max int,@Sub_i int,@Sub_Max int
set @i = 1
select @Max = count(*)
from @Tmp

while(@i <= @Max)
begin
	select @Name = [Equipment]
	,@StartDate = [PreviousTime]
	,@EndDate = [TranstionTime]
	,@Statu = [PreviousStatus]
	from (
		select Row_Number()Over(order by [SID]) as Sort
		,[Equipment]
		,[PreviousTime]
		,[TranstionTime]
		,[PreviousStatus]
		from @Tmp
	) as k
	where Sort = @i

	set @Sub_i = 1
	set @Sub_Max = datediff(hour,@StartDate,@EndDate)

	while(@Sub_i <= @Sub_Max)
	begin
		insert into @Tmp2
		select isNull((select max([SID]) from @Tmp2 ),0) + 1
		,@Name
		,dateadd(hour,@Sub_i,@StartDate)
		,60 as [Cal_Minute]
		,@Statu

		set @Sub_i = @Sub_i + 1
	end

	set @i = @i + 1
end

insert into @Tmp2
select isNull((select max([SID]) from @Tmp2 ),0) 
+ Row_Number() Over(order by 設備) as Sort
,設備
,Convert(varchar,日期,111) + ' 09:00' as 日期
,1440 - 時間 as 時間
,'運行' as 狀態
from (
	select Convert(date,dateadd(hour,-9,[Cal_Date])) as 日期
	,[Equipment] as 設備
	,Sum([Cal_Minute]) as 時間
	from @Tmp2
	where PreviousStatus in('停機','維修')
	group by [Equipment]
	,Convert(date,dateadd(hour,-9,[Cal_Date]))
) as k
where exists(
	select *
	from (
		select max(日期) as 日期
		,設備
		from (
			select Convert(date,dateadd(hour,-9,[Cal_Date])) as 日期
			,[Equipment] as 設備
			from @Tmp2
			where PreviousStatus in('停機','維修')
			group by [Equipment]
			,Convert(date,dateadd(hour,-9,[Cal_Date]))
		) as k2
		group by 設備
	) as k2
	where k.設備 = k2.設備
	and k.日期 = k2.日期
)

select 日期
,設備
,Sum(停機時間) as 停機時間
,Sum(運行時間) as 運行時間
,Sum(維修時間) as 維修時間
,Sum(停機時間) + Sum(運行時間) + Sum(維修時間) as 總共時間
from ((
	select Convert(date,dateadd(hour,-9,[Cal_Date])) as 日期
	,[Equipment] as 設備
	,Sum([Cal_Minute]) as 停機時間
	,0 as 運行時間
	,0 as 維修時間
	from @Tmp2
	where PreviousStatus = '停機'
	group by [Equipment]
	,Convert(date,dateadd(hour,-9,[Cal_Date]))
)union all(
	select Convert(date,dateadd(hour,-9,[Cal_Date])) as 日期
	,[Equipment] as 設備
	,0 as 停機時間
	,Sum([Cal_Minute]) as 運行時間
	,0 as 維修時間
	from @Tmp2
	where PreviousStatus = '運行'
	group by [Equipment]
	,Convert(date,dateadd(hour,-9,[Cal_Date]))
)union all(
	select Convert(date,dateadd(hour,-9,[Cal_Date])) as 日期
	,[Equipment] as 設備
	,0 as 停機時間
	,0 as 運行時間
	,Sum([Cal_Minute]) as 維修時間
	from @Tmp2
	where PreviousStatus = '維修'
	group by [Equipment]
	,Convert(date,dateadd(hour,-9,[Cal_Date]))
)) as k
group by 日期,設備
order by 日期

第2版修正~增加最後一天正常運行的時間~

回應 7
檢舉

看更多先前的回應...收起先前的回應...

暐翰 iT邦大師 1 級 ‧ 2018-06-10 23:06:24 檢舉

我少加一個設備判斷，謝謝大大!

純真的人 iT邦大師 1 級 ‧ 2018-06-10 23:14:34 檢舉

6/6應該是A設備剛購入，所以那天是只有1200
6/7購入B設備，所以那天只有1380

所以到6/9扣掉停機與維修時間~(最後一天的顯示)
所以A/B設備正常運行應該要再加1200分鐘~

對吧?

只是這段
(9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機')
不知道為什麼是判斷成6/8停機~卻不是維修0.0...

暐翰 iT邦大師 1 級 ‧ 2018-06-10 23:19:33 檢舉

6/6應該是A設備剛購入，所以那天是只有1200
6/7購入B設備，所以那天只有1380

是的

所以到6/9扣掉停機與維修時間~(最後一天的顯示)
所以A/B設備正常運行應該要再加1200分鐘~

對!假如是該機台最後一天最後一筆資料,代表之後動作都是一樣

純真的人 iT邦大師 1 級 ‧ 2018-06-10 23:20:35 檢舉

只是這段
(9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機')
不知道為什麼是判斷成6/8停機~卻不是維修0.0...

暐翰 iT邦大師 1 級 ‧ 2018-06-10 23:41:10 檢舉

把T2搞錯成T3造成的
再次謝謝大大!

純真的人 iT邦大師 1 級 ‧ 2018-06-11 00:27:38 檢舉

ok! 我也把最後一天正常運行的時間~補上了

暐翰 iT邦大師 1 級 ‧ 2018-06-11 00:38:35 檢舉

大大厲害

登入發表回應

小碼農米爾

iT邦高手 1 級 ‧ 2018-06-12 22:18:49

這題好有趣，終於想出來了，不過不知道正不正確。

使用 MS SQL。

DECLARE @Temp TABLE
(
	SID	INT, 
	Equipment NVARCHAR(10),
	PreviousTime DATETIME,
	TranstionTime DATETIME,
	PreviousStatus NVARCHAR(10),
	Status NVARCHAR(10)
)

INSERT INTO @Temp
	(SID,Equipment,PreviousTime,TranstionTime,PreviousStatus,Status)
VALUES
	(0	,'A','2018/6/6 12:00:00','2018/6/7 08:00:00',N'停機',N'運行'),
    (1	,'A','2018/6/7 08:00:00','2018/6/7 10:00:00',N'運行',N'停機'),
    (2	,'A','2018/6/7 10:00:00','2018/6/7 12:00:00',N'停機',N'運行'),
    (3	,'A','2018/6/7 12:00:00','2018/6/7 14:00:00',N'運行',N'維修'),
    (4	,'A','2018/6/7 14:00:00','2018/6/9 10:00:00',N'維修',N'停機'),
    (5	,'A','2018/6/9 10:00:00','2018/6/9 12:00:00',N'停機',N'運行'),
    (6	,'B','2018/6/7 09:00:00','2018/6/7 10:00:00',N'運行',N'停機'),
    (7	,'B','2018/6/7 10:00:00','2018/6/7 12:00:00',N'停機',N'運行'),
    (8	,'B','2018/6/7 12:00:00','2018/6/7 14:00:00',N'運行',N'維修'),
    (9	,'B','2018/6/7 14:00:00','2018/6/9 10:00:00',N'維修',N'停機'),
    (10	,'B','2018/6/9 10:00:00','2018/6/9 12:00:00',N'停機',N'運行')

DECLARE @DATE DATETIME
DECLARE @SDATE DATETIME
DECLARE @EDATE DATETIME

--搜尋條件，以天為單位
SET @DATE='2018-06-10'

--一天從早上8:00開始，7天內且不用處理當天
SET @SDATE=@DATE-7+'08:00'
SET @EDATE=@DATE-1+'08:00'

--用CTE將開始到結束時間拆開成每天
;WITH DATE_CTE AS
(
	SELECT @SDATE AS DATE
	UNION ALL
	SELECT DATE+1 FROM DATE_CTE WHERE DATE<@EDATE
), 
--因為資料有百萬筆，所以先將需要的日期區間資料取出
A_CTE AS 
(
	SELECT * 
	FROM @Temp
	WHERE --PreviousTime和時間區間交集
          PreviousTime>=@SDATE AND PreviousTime<@EDATE+1 OR
          --TranstionTime和時間區間交集
		  TranstionTime>@SDATE AND TranstionTime<=@EDATE+1 OR
          --PreviousTime和TranstionTime包含整個時間區間
		  PreviousTime<=@SDATE AND TranstionTime>=@EDATE+1
), 
--處理最後一筆資料,
--利用ROW_NUMBER函數按照SID排序後，找到每台設備的最後一筆資料，
--如果最後一筆資料的結束時間點在最後一天之前，補上最後一段時間區間後加入原資料
B_CTE AS 
(
	SELECT * FROM A_CTE
	UNION ALL
	SELECT NULL AS SID, 
	       Equipment AS Equipment, 
		   TranstionTime AS PreviousTime,
		   DATEADD(DAY, 0, 
               DATEDIFF(DAY, 0, TranstionTime))+1+'08:00' AS TranstionTime,
		   Status AS PreviousStatus,
		   NULL AS Status
	FROM
	(
		SELECT *, ROW_NUMBER() 
            OVER(PARTITION BY Equipment ORDER BY SID DESC) AS RID
		FROM A_CTE
	) AS A
	WHERE RID=1 AND TranstionTime<@EDATE+1
), 
--因為資料可能跨日，所以利用JOIN將跨日資料拆開，
--再用DATEDIFF函數算出每筆資料相減後的時間以分鐘為單位
C_CTE AS
(
	SELECT DT.DATE, Equipment, PreviousStatus AS Status,
	       DATEDIFF(MINUTE, 
				CASE WHEN PreviousTime > DT.DATE 
				     THEN PreviousTime ELSE DT.DATE END,
				CASE WHEN TranstionTime < DT.DATE+1 
				     THEN TranstionTime ELSE DT.DATE+1 END
		   ) AS Time
	FROM B_CTE
	LEFT JOIN DATE_CTE AS DT ON 
		  PreviousTime>=DT.DATE AND PreviousTime<DT.DATE+1 OR   
		  TranstionTime>DT.DATE AND TranstionTime<=DT.DATE+1 OR 
		  PreviousTime<=DT.DATE AND TranstionTime>=DT.DATE+1
), 
--最後使用PIVOT將狀態欄位直轉橫，就是期望的結果
D_CTE AS
(
	SELECT CONVERT(NVARCHAR(20), PV.DATE, 111) AS 日期,
	       PV.Equipment AS 設備,
		   PV.停機 AS 停機時間,
		   PV.運行 AS 運行時間,
		   PV.維修 AS 維修時間,
		   CASE WHEN 停機 IS NULL THEN 0 ELSE 停機 END +
		   CASE WHEN 運行 IS NULL THEN 0 ELSE 運行 END +
		   CASE WHEN 維修 IS NULL THEN 0 ELSE 維修 END AS 總共時間
	FROM 
	(
		SELECT * FROM C_CTE
	) AS T
	PIVOT
	(
		SUM(Time) FOR Status IN ([停機], [運行], [維修])
	) AS PV
)

SELECT * FROM D_CTE ORDER BY 日期, 設備

回應 4
檢舉

看更多先前的回應...收起先前的回應...

純真的人 iT邦大師 1 級 ‧ 2018-06-13 09:34:41 檢舉

喔~~簡單明瞭~正確呢~厲害~

若要調的話~只有這段呢~~

--搜尋條件，以天為單位
SET @SDATE='2018-06-06'
SET @EDATE='2018-06-09'

暐翰 iT邦大師 1 級 ‧ 2018-06-13 09:54:33 檢舉

厲害!
IT邦友測試fysh711426大大Script可以到這
db<>fiddle Test Demo LINK

小碼農米爾 iT邦高手 1 級 ‧ 2018-06-13 22:57:51 檢舉

純真的人感謝大大，那段我調整了，且發現有 BUG，原寫法會撈出區間 0 的資料。

日期	設備	停機時間	運行時間	維修時間	總共時間
2018/06/07	A	120	240	1080	1440
2018/06/07	B	120	180	1080	1380
2018/06/08	A	NULL	NULL	1440	1440
2018/06/08	B	NULL	NULL	1440	1440
2018/06/09	A	120	1200	120	1440
2018/06/09	B	120	1200	120	1440
2018/06/10	A	NULL	0	NULL	0
2018/06/10	B	NULL	0	NULL	0

原因在於取時間區間的這三個地方使用了 >= 和 <=，會多取區間 0 的資料。

PreviousTime>=@SDATE AND PreviousTime<=@EDATE+1 OR
TranstionTime>=@SDATE AND TranstionTime<=@EDATE+1 OR
PreviousTime<=@SDATE AND TranstionTime>=@EDATE+1

WHERE RID=1 AND TranstionTime<=@EDATE+1

PreviousTime>=DT.DATE AND PreviousTime<=DT.DATE+1 OR   
TranstionTime>=DT.DATE AND TranstionTime<=DT.DATE+1 OR 
PreviousTime<=DT.DATE AND TranstionTime>=DT.DATE+1

解決辦法:
將造成頭尾相接的判斷改成 > 和 <。

PreviousTime>=@SDATE AND PreviousTime<@EDATE+1 OR
TranstionTime>@SDATE AND TranstionTime<=@EDATE+1 OR
PreviousTime<=@SDATE AND TranstionTime>=@EDATE+1

WHERE RID=1 AND TranstionTime<@EDATE+1

PreviousTime>=DT.DATE AND PreviousTime<DT.DATE+1 OR   
TranstionTime>DT.DATE AND TranstionTime<=DT.DATE+1 OR 
PreviousTime<=DT.DATE AND TranstionTime>=DT.DATE+1

頭尾相接示意圖:

暐翰感謝大大的測試連結。

純真的人 iT邦大師 1 級 ‧ 2018-06-14 00:06:15 檢舉

哈哈~

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2021-04-23 16:09:03

玩一下...

CREATE TABLE Table1
([SID] int, [Equipment] nvarchar(1), [PreviousTime] datetime, [TranstionTime] datetime, [PreviousStatus] nvarchar(20), [Status] nvarchar(20));
	
INSERT INTO Table1
	([SID], [Equipment], [PreviousTime], [TranstionTime], [PreviousStatus], [Status])
VALUES
	(0, 'A', '2018-06-06 12:00:00', '2018-06-07 09:00:00', N'停機', N'運行'),
	(1, 'A', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(2, 'A', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(3, 'A', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(4, 'A', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(5, 'A', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行')
	,
	(6, 'B', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'),
	(7, 'B', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'),
	(8, 'B', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'),
	(9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'),
	(10, 'B', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行'),
	-- 額外加入
	(11, 'C', '2018-06-06 06:00:00', '2018-06-07 09:00:00', N'停機', N'運行'),
	(12, 'D', '2018-06-06 06:00:00', '2018-06-07 08:00:00', N'停機', N'運行');

WITH CTE_X1 AS (
SELECT SID,Equipment,PreviousTime,TranstionTime,
-- 時間平移 8 小時
  DATEADD(HOUR, -8, PreviousTime) AS PreviousNew,
  DATEADD(HOUR, -8, TranstionTime) AS TranstionNew,
  PreviousStatus,
-- 計算天數
  DATEDIFF(DAY,DATEADD(HOUR, -8, PreviousTime),DATEADD(HOUR, -8, TranstionTime))+1 AS DayCount
FROM (
SELECT SID,Equipment,PreviousTime,TranstionTime,PreviousStatus
FROM Table1
UNION
-- 合併最後一筆
SELECT SID,Equipment,TranstionTime AS PreviousTime,
  CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(DAY, 1, TranstionTime),120) + ' 08:00:00') AS TranstionTime,
  Status AS PreviousStatus
FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Equipment ORDER BY TranstionTime DESC) AS ROWNUM
FROM Table1) AS TableY
WHERE ROWNUM = 1) AS TableZ),
-- 依 DayCount-1> 0 遞減,日期遞增
CTE_GetDate AS (
SELECT SID,Equipment,PreviousNew,TranstionNew,PreviousStatus,PreviousTime,TranstionTime,
  CONVERT(DATE, PreviousNew) AS DateStart,CONVERT(DATE, DATEADD(DAY, 1, PreviousNew)) AS DateEnd,
  CASE WHEN TranstionNew <= CONVERT(DATE, DATEADD(DAY, 1, PreviousNew)) THEN DATEDIFF(MINUTE, PreviousNew, TranstionNew)
  ELSE DATEDIFF(MINUTE, PreviousNew, CONVERT(DATE, DATEADD(DAY, 1, PreviousNew)))
  END AS NewMinute,DayCount AS 'DayCount'
FROM CTE_X1
UNION ALL
SELECT SID,Equipment,PreviousNew,TranstionNew,PreviousStatus,PreviousTime,TranstionTime,
  DATEADD(DAY, 1, DateStart),DATEADD(DAY, 1, DateEnd),
  CASE WHEN TranstionNew <= CONVERT(DATE, DATEADD(DAY, 1, DateEnd)) THEN DATEDIFF(MINUTE, PreviousNew, TranstionNew)
  ELSE DATEDIFF(MINUTE, PreviousNew, CONVERT(DATE, DATEADD(DAY, 1, DateEnd)))
  END AS NewMinute,(DayCount - 1) AS 'DayCount'
FROM CTE_GetDate
WHERE DayCount - 1 > 0)
--
SELECT DateStart AS '日期',Equipment AS '設備',
  SUM(CASE WHEN PreviousStatus = N'停機' THEN FinalMinute END) AS '停機時間',
  SUM(CASE WHEN PreviousStatus = N'運行' THEN FinalMinute END) AS '運行時間',
  SUM(CASE WHEN PreviousStatus = N'維修' THEN FinalMinute END) AS '維修時間',
  SUM(FinalMinute) AS '總共時間'
FROM (
SELECT Equipment,PreviousStatus,DateStart,
-- 計算時間差
  NewMinute - LAG(NewMinute, 1, 0) OVER (PARTITION BY SID, Equipment, PreviousStatus ORDER BY DateStart) AS FinalMinute
FROM CTE_GetDate) AS TableX
WHERE FinalMinute > 0
GROUP BY DateStart, Equipment
ORDER BY DateStart, Equipment

Demo