iT邦幫忙

6

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

sql

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

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

表格結構類似:

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 停機 運行

附註:

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

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


2 則留言

2
純真的人
iT邦研究生 3 級 ‧ 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 日期


https://ithelp.ithome.com.tw/upload/images/20180610/200613693RFwg0SsUa.png

第2版修正~增加最後一天正常運行的時間~
https://ithelp.ithome.com.tw/upload/images/20180611/20061369IY6BNLbTu4.png

看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 10 級‧ 2018-06-10 23:06:24 檢舉

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

純真的人 iT邦研究生 3 級‧ 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邦大師 10 級‧ 2018-06-10 23:19:33 檢舉

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

是的

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

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

純真的人 iT邦研究生 3 級‧ 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邦大師 10 級‧ 2018-06-10 23:41:10 檢舉


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

純真的人 iT邦研究生 3 級‧ 2018-06-11 00:27:38 檢舉

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

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

/images/emoticon/emoticon12.gif 大大厲害

2
fysh711426
iT邦新手 2 級 ‧ 2018-06-12 22:18:49

這題好有趣,終於想出來了,不過不知道正不正確。 /images/emoticon/emoticon41.gif

使用 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 日期, 設備
看更多先前的回應...收起先前的回應...
純真的人 iT邦研究生 3 級‧ 2018-06-13 09:34:41 檢舉

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

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

--搜尋條件,以天為單位
SET @SDATE='2018-06-06'
SET @EDATE='2018-06-09'
暐翰 iT邦大師 10 級‧ 2018-06-13 09:54:33 檢舉

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

純真的人 感謝大大,那段我調整了,且發現有 BUG,原寫法會撈出區間 0 的資料。 /images/emoticon/emoticon16.gif

日期 設備 停機時間 運行時間 維修時間 總共時間
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

頭尾相接示意圖:
https://ithelp.ithome.com.tw/upload/images/20180613/20106865A6kkTUTsTt.jpg

暐翰 感謝大大的測試連結。 /images/emoticon/emoticon37.gif

純真的人 iT邦研究生 3 級‧ 2018-06-14 00:06:15 檢舉

哈哈~

我要留言

立即登入留言