iT邦幫忙

1

[已解決] SQL 機台運行時間計算

ms sql 稼動率
darrenchen2020 2022-01-12 17:33:48 ‧ 1846 瀏覽

  • 分享至 

  • xImage

各位大大好，小弟目前工作遇到一個問題如下，請各位大大不吝幫忙，感恩感恩。
使用MSSQL，資料如下。
SQL範例連結

SELECT [paragraph]
      ,[plc_value]
      ,[update_at]
  FROM [FAB].[dbo].[history]

paragraph plc_value update_at
=第一次=
('open', '53', '2021-12-01 01:00:00'),
('open', '53', '2021-12-01 02:00:00'),
('open', '53', '2021-12-01 03:00:00'),
('standby', '53', '2021-12-01 04:00:00'),
('standby', '53', '2021-12-01 05:00:00'),
('down', '53', '2021-12-01 06:00:00'),
('down', '53', '2021-12-01 07:00:00'),
('down', '53', '2021-12-01 08:00:00'),

=第二次=
('open', '53', '2021-12-02 11:00:00'),
('open', '53', '2021-12-02 12:00:00'),
('open', '53', '2021-12-02 13:00:00'),
('standby', '53', '2021-12-02 14:00:00'),
('standby', '53', '2021-12-02 15:00:00'),
('down', '53', '2021-12-02 16:00:00'),
('down', '53', '2021-12-02 17:00:00'),
('down', '53', '2021-12-02 18:00:00'),
第三筆...
('open', '53', '2021-12-02 19:00:00'),
('open', '53', '2021-12-02 20:00:00'),
('standby', '53', '2021-12-02 20:10:00'),
('standby', '53', '2021-12-02 21:00:00'),
('down', '53', '2021-12-02 21:30:00'),
('down', '53', '2021-12-02 22:00:00')

依此類推，需要取出機台每次運行第一筆[open] ~ 最後一筆[down]的時間，
所以第一筆取出，為以下
('open', '53', '2021-12-01 01:00:00'),
('down', '53', '2021-12-01 08:00:00'),
第二筆
('open', '53', '2021-12-02 11:00:00'),
('down', '53', '2021-12-02 18:00:00'),
第三筆
('open', '53', '2021-12-02 19:00:00'),
('down', '53', '2021-12-02 22:00:00')
...

以上，麻煩各位大大，小弟感激不盡...

純真的人 iT邦大師 1 級 ‧ 2022-01-12 19:55:39 檢舉
幫補充稼動率指定是8小時為一個區間~
這邊的判斷~我大概只能用T-SQL去推吧@@"
darrenchen2020 iT邦新手 4 級 ‧ 2022-01-13 10:15:09 檢舉
回 @純真的人 大大，感謝補充 !!
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

6
rogeryao
iT邦超人 7 級 ‧ 2022-01-12 20:19:29
最佳解答

假設 : 每一次都會包含 'open'、'down'

CREATE TABLE [dbo].[history](
	[paragraph] [varchar](10) NOT NULL,
	[plc_value] [varchar](50) NULL,
	[update_at] [datetime] NULL);
	
INSERT INTO [history]
VALUES 
-- 第一次
('open', '53', '2021-12-01 01:00:00'),
('open', '53', '2021-12-01 02:00:00'),
('open', '53', '2021-12-01 03:00:00'),
('standby', '53', '2021-12-01 04:00:00'),
('standby', '53', '2021-12-01 05:00:00'),
('down', '53', '2021-12-01 06:00:00'),
('down', '53', '2021-12-01 07:00:00'),
('down', '53', '2021-12-01 08:00:00'),
-- 第二次
('open', '53', '2021-12-02 11:00:00'),
('open', '53', '2021-12-02 12:00:00'),
('open', '53', '2021-12-02 13:00:00'),
('standby', '53', '2021-12-02 14:00:00'),
('standby', '53', '2021-12-02 15:00:00'),
('down', '53', '2021-12-02 16:00:00'),
('down', '53', '2021-12-02 17:00:00'),
('down', '53', '2021-12-02 18:00:00'),
-- 第三次
('open', '53', '2021-12-02 19:00:00'),
('open', '53', '2021-12-02 20:00:00'),
('standby', '53', '2021-12-02 20:10:00'),
('standby', '53', '2021-12-02 21:00:00'),
('down', '53', '2021-12-02 21:30:00'),
('down', '53', '2021-12-02 22:00:00');

方法一 :

;WITH CTE01 AS (
SELECT M.[update_at],M.[paragraph],M.[plc_value],
1+SUM(M.YY) OVER (ORDER BY M.[update_at]) AS NO
FROM (
SELECT [history].*,
CASE 
WHEN [paragraph]=LAG([paragraph],1,[paragraph]) OVER (ORDER BY [update_at]) 
THEN 0 ELSE 1 END AS YY
FROM [history]
WHERE [paragraph] = 'open' OR [paragraph] = 'down') AS M),
CTE02 AS (
SELECT CTE01.*,((NO-1) / 2) + 1 AS [QUOTIENT]
FROM CTE01)
--
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at]) NUN
FROM CTE02) AS XX
WHERE NUN = 1
UNION
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at] DESC) NUN
FROM CTE02) AS ZZ
WHERE NUN = 1
ORDER BY [update_at]

方法二 :

;WITH CTE01 AS (
SELECT M.[update_at],M.[paragraph],M.[plc_value],
1+SUM(M.YY) OVER (ORDER BY M.[update_at]) AS NO
FROM (
SELECT [history].*,
CASE 
WHEN [paragraph]=LAG([paragraph],1,[paragraph]) OVER (ORDER BY [update_at]) 
THEN 0 ELSE 1 END AS YY
FROM [history]
WHERE [paragraph] = 'open' OR [paragraph] = 'down') AS M),
CTE02 AS (
SELECT CTE01.*,((NO-1) / 2) + 1 AS [QUOTIENT]
FROM CTE01)
--
SELECT C.*,D.paragraph,D.update_at_down,DATEDIFF(HOUR,C.update_at_open,D.update_at_down) AS HOUR_TEMP
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at] AS [update_at_open]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at]) NUN
FROM CTE02) AS XX
WHERE NUN = 1) AS C
LEFT JOIN (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at] AS [update_at_down]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at] DESC) NUN
FROM CTE02) AS ZZ
WHERE NUN = 1
) AS D ON D.[QUOTIENT] = C.[QUOTIENT]
ORDER BY C.[update_at_open]

Demo

darrenchen2020 iT邦新手 4 級 ‧ 2022-01-13 10:23:55 檢舉

感謝大大解答，執行結果正確，小弟會在好好學習其中內容，感恩!!

登入發表回應
4
純真的人
iT邦大師 1 級 ‧ 2022-01-13 09:29:25

假設你的機台稼動率為8小時
每小時通知一次機台狀態
開機open關機down
跨夜為午夜12點
SQL如下~

select [paragraph]
,[plc_value]
,(
	case when [paragraph] = 'open'
	then min([update_at])
	else max([update_at])
	end
) [update_at]
from (
	SELECT Convert(int,(ROW_NUMBER() OVER (PARTITION  BY Convert(date,[update_at] ) ORDER BY [update_at] ) - 1) / 8) GroupSort
	,* 
	FROM [history]
) k
where [paragraph] in('open','down')
group by Convert(date,[update_at] )
,GroupSort
,[paragraph]
,[plc_value]
order by min([update_at])

https://ithelp.ithome.com.tw/upload/images/20220113/20061369lUjHe9UEAg.png

darrenchen2020 iT邦新手 4 級 ‧ 2022-01-13 10:25:08 檢舉

感謝大大解答，因為@rogeryao大大比較早發出來，所以最佳解答就選了，這份代碼小弟也會研究學習一下，感恩!!

登入發表回應
5
一級屠豬士
iT邦大師 1 級 ‧ 2022-01-13 10:59:28

另外一種方式,使用PG為例.

create table it0112 (
  paragraph text
, its timestamp(0)
);

insert into it0112 values
('open', '2021-12-01 01:00:00'),
('open', '2021-12-01 02:00:00'),
('open', '2021-12-01 03:00:00'),
('standby', '2021-12-01 04:00:00'),
('standby', '2021-12-01 05:00:00'),
('down', '2021-12-01 06:00:00'),
('down', '2021-12-01 07:00:00'),
('down', '2021-12-01 08:00:00'),
('open', '2021-12-02 11:00:00'),
('open', '2021-12-02 12:00:00'),
('open', '2021-12-02 13:00:00'),
('standby', '2021-12-02 14:00:00'),
('standby', '2021-12-02 15:00:00'),
('down', '2021-12-02 16:00:00'),
('down', '2021-12-02 17:00:00'),
('down', '2021-12-02 18:00:00'),
('open', '2021-12-02 19:00:00'),
('open', '2021-12-02 20:00:00'),
('standby', '2021-12-02 20:10:00'),
('standby', '2021-12-02 21:00:00'),
('down', '2021-12-02 21:30:00'),
('down', '2021-12-02 22:00:00');

---
with t1 as(
select paragraph, its
     , lag(paragraph) over(order by its)
     , lead(paragraph) over(order by its)
  from it0112
 where paragraph in ('open', 'down')
), t2 as (
select paragraph, its
     , case 
         when paragraph = 'open'
          and (lag is NULL or lag = 'down')
              then 'head'
         when paragraph = 'down'
          and (lead is NULL or lead = 'open')
              then 'tail'
         else 'not'
       end as hort
  from t1
)
select *
     , case 
         when hort = 'head'
          then '開工->'
         when hort = 'tail'
          and lead(its) over(order by its) is null
          then '休眠中'
         else '休息->'
        end as wrkstat
     , (lead(its) over(order by its) - its) as period
  from t2
 where hort in ('head', 'tail');

 paragraph |         its         | hort | wrkstat |     period     
-----------+---------------------+------+---------+----------------
 open      | 2021-12-01 01:00:00 | head | 開工->  | 07:00:00
 down      | 2021-12-01 08:00:00 | tail | 休息->  | 1 day 03:00:00
 open      | 2021-12-02 11:00:00 | head | 開工->  | 07:00:00
 down      | 2021-12-02 18:00:00 | tail | 休息->  | 01:00:00
 open      | 2021-12-02 19:00:00 | head | 開工->  | 03:00:00
 down      | 2021-12-02 22:00:00 | tail | 休眠中  | NULL
(6 rows)

https://ithelp.ithome.com.tw/upload/images/20220113/20050647y28z4PG747.png

rogeryao iT邦超人 7 級 ‧ 2022-01-13 11:03:14 檢舉

同時使用 lag 及 lead 找出邏輯,讚
/images/emoticon/emoticon12.gif

darrenchen2020 iT邦新手 4 級 ‧ 2022-01-14 09:17:59 檢舉

感謝大大解答，真的是一個問題有多種不同的解決方法!!!
小弟會好好研究學習一下的，感謝!!

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22195 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙