WITH SortedData AS (
SELECT
A.equipmentid,
A.loadport,
TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS') AS txntimestamp,
A.state,
A.loadedfoup,
A.bindingfoup,
LAG(A.state, 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS'), 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev_timestamp,
LAG(A.loadedfoup, 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev_loadedfoup,
LAG(A.state, 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev2_state,
LAG(A.bindingfoup, 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev2_bindingfoup,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS'), 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev2_timestamp,
LAG(A.state, 3) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev3_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMM DDHH24MISS'), 3) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev3_timestamp,
LAG(A.state, 4) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev4_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMM DDHH24MISS'), 4) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 15), 'YYYYMMDD HH24MISS')
) AS prev4_timestamp
FROM DR01.sdb_tb_smic_loadport_history A
JOIN SDB_TB_DATE_SHIFT B
ON A.txntimestamp BETWEEN B.starttime AND B.endtime
WHERE A.equipmentid IN ('EDPTC01')
AND A.state IS NOT NULL
AND B.SHIFT_DATE >= TO_CHAR(sysdate-1,'YYYYMMDD')
),
FilteredData AS (
SELECT
equipmentid,
loadport,
txntimestamp AS time4,
prev_timestamp AS time3,
prev2_timestamp AS time2,
CASE
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad'
THEN prev3_timestamp
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload'
THEN prev2_timestamp
ELSE CAST(NULL AS DATE)
END AS time1,
CASE
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad'AND prev4_state = 'ReadyToUnload'
THEN prev4_timestamp
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload'
THEN prev3_timestamp
ELSE CAST(NULL AS DATE)
END AS time0,
loadedfoup
FROM SortedData
WHERE
state = 'ReadyToUnload'
AND prev_state = 'LoadCompleted'
AND prev_loadedfoup = loadedfoup
AND (
(prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad' AND prev2_bindingfoup = loadedfoup AND prev4_state = 'ReadyToUnload')
OR
(prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload' AND prev2_bindingfoup = loadedfoup)
)
AND prev3_timestamp IS NOT NULL
AND prev4_timestamp IS NOT NULL
)
SELECT
equipmentid,
loadedfoup AS foupid,
loadport,
time0,
time1,
time2,
time3,
time4,
ROUND((time1 - time0) ) AS time10,
ROUND((time2 - time1)) AS time21,
ROUND((time3 - time2)) AS time32,
ROUND((time4 - time3)) AS time43
FROM FilteredData
WHERE time0 IS NOT NULL
AND time1 IS NOT NULL
AND time2 IS NOT NULL
AND time3 IS NOT NULL
AND time4 IS NOT NULL
ORDER BY equipmentid, loadport, time4;
上段sql的执行结果如下，
equipmentid foupid loadport time0 time1 time2 time3 time4 time10 time21 time32 time43
EDPTC01 EFP20472 1 21.11.2025 07:36:52 21.11.2025 07:37:21 21.11.2025 07:37:21 21.11.2025 07:38:44 21.11.2025 07:45:17 0 0 0 0
为什么time0 time1 time2 time3 time4的格式不是YYYYMMDD HH24MISS，而是输出的这种格式，而且为什么time10 time21 time32 time43都为0
本文介绍了如何使用to_date()函数进行时间相减以计算两个日期之间的差距，并展示了如何利用TRUNC函数来获取特定日期的截断值，此外还提供了一个统计不同状态记录下时间总和的示例。
扫一扫
6996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
