我正在尝试将一些计算列添加到一起，其中这些列的值是当前记录和下一个记录之间的时间戳差函数的子字符串。不幸的是，子字符串确实返回了null值，我已经尝试通过COALESCE、ISNULL转换这些值，但我无法正确添加这些值。我也尝试过SUM函数，但我在这方面也遇到了错误。
select T01."ENGVIDN", T01."ENGSTAT", (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8)))) as "T01 TIMESTAMP", (TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) as "T02 TIMESTAMP",
CAST(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))) as INT) as "DIFF",
CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-8, 2)as INT) as "DAYS",
CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-6, 2) as INT) as "HOURS",
CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-4, 2) as INT) as "MINUTES",
CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-2, 2) as INT) as "SECONDS",
--
(CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-4, 2) as INT)*60 +
CAST(SUBSTR(((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))), LOCATE('.', ((TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) - (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))))-2, 2) as INT))
from mrc_main."SAMENGST" T01
INNER JOIN mrc_main."SAMENGST" T02 on RRN(T02) = RRN(T01)+1
and (TIMESTAMP(((SUBSTR( T02."ENGSTME", 1, LOCATE('T', T02."ENGSTME")-1))),(SUBSTR( T02."ENGSTME", LOCATE('T', T02."ENGSTME")+1, 8)))) > (TIMESTAMP(((SUBSTR( T01."ENGSTME", 1, LOCATE('T', T01."ENGSTME")-1))),(SUBSTR( T01."ENGSTME", LOCATE('T', T01."ENGSTME")+1, 8))))
WHERE T01."ENGVIDN" = T02."ENGVIDN" and
T02."ENGVIDN" = 212014918948687 and
T01."ENGSTME" like '%2020-02-17%'
我正在尝试添加标记为DAYS、HOURS、MINUTES、SECONDS的列，那么实现这一点的正确方法是什么？
以下的样本数据
ENGVIDN ENGSTME ENGSTME
212014918948687 2020-02-17T09:46:19Z Off
212014918948687 2020-02-17T09:46:29Z On
212014918948687 2020-02-17T09:50:10Z Idle
212014918948687 2020-02-17T10:01:11Z On
212014918948687 2020-02-17T10:12:16Z Idle
212014918948687 2020-02-17T11:10:17Z On
212014918948687 2020-02-17T11:19:27Z Idle
212014918948687 2020-02-17T11:23:27Z On
212014918948687 2020-02-17T11:35:19Z Idle
212014918948687 2020-02-17T11:36:53Z Off
212014918948687 2020-02-17T12:07:20Z On
212014918948687 2020-02-17T12:09:20Z Idle
212014918948687 2020-02-17T12:21:01Z On
212014918948687 2020-02-17T12:38:48Z Idle
212014918948687 2020-02-17T12:39:47Z On
212014918948687 2020-02-17T12:53:44Z Idle
212014918948687 2020-02-17T12:56:23Z On
212014918948687 2020-02-17T12:58:48Z Idle
212014918948687 2020-02-17T13:01:14Z On
212014918948687 2020-02-17T13:17:17Z Idle
212014918948687 2020-02-17T14:01:01Z On
212014918948687 2020-02-17T14:09:16Z Idle
212014918948687 2020-02-17T14:41:29Z On
212014918948687 2020-02-17T15:01:19Z Off
212014918948687 2020-02-17T15:35:42Z On
212014918948687 2020-02-17T15:43:11Z Off
212014918948687 2020-02-17T16:16:57Z On
212014918948687 2020-02-17T16:26:36Z Idle
212014918948687 2020-02-17T16:50:30Z On
212014918948687 2020-02-17T16:58:20Z Idle
212014918948687 2020-02-17T17:03:24Z On
212014918948687 2020-02-17T17:13:30Z Idle
212014918948687 2020-02-17T17:23:16Z On
212014918948687 2020-02-17T17:36:51Z Off
212014918948687 2020-02-17T19:00:35Z On
212014918948687 2020-02-17T19:19:31Z Off
212014918948687 2020-02-17T19:21:58Z On
212014918948687 2020-02-17T19:24:09Z Idle
212014918948687 2020-02-17T19:26:08Z On
212014918948687 2020-02-17T19:42:24Z Idle
212014918948687 2020-02-17T19:44:27Z On
212014918948687 2020-02-17T19:48:55Z Off
212014918948687 2020-02-17T19:50:53Z On
212014918948687 2020-02-17T19:57:27Z Off
212014918948687 2020-02-18T01:55:56Z On
212014918948687 2020-02-18T01:58:43Z Off
212014918948687 2020-02-18T05:02:17Z Off
212014918948687 2020-02-18T08:58:01Z On
212014918948687 2020-02-18T09:01:45Z Idle
212014918948687 2020-02-18T09:05:17Z On
我不确定，但它似乎是V7.1。
按原样尝试：
WITH TAB (ENGSTME) AS
(
VALUES
TIMESTAMP('2020-02-17-09.46.19')
, TIMESTAMP('2020-02-17-09.46.29')
, TIMESTAMP('2020-02-17-09.50.10')
, TIMESTAMP('2020-02-17-10.01.11')
, TIMESTAMP('2020-02-17-10.12.16')
, TIMESTAMP('2020-02-17-11.10.17')
)
, TAB_ENUM (RN, ENGSTME) AS
(
SELECT ROWNUMBER() OVER (ORDER BY ENGSTME) AS RN, ENGSTME
FROM TAB
)
SELECT
A.ENGSTME, B.ENGSTME AS ENGSTME_PREV
, DIGITS(DEC(A.ENGSTME - B.ENGSTME, 14)) AS TS_DURATION --yyyymmddhhmmss format with leading zeroes
, (DAYS(A.ENGSTME) - DAYS(B.ENGSTME)) * 86400 + MIDNIGHT_SECONDS(A.ENGSTME) - MIDNIGHT_SECONDS(B.ENGSTME) AS TS_DIFF_SEC
FROM TAB_ENUM A
LEFT JOIN TAB_ENUM B ON B.RN = A.RN - 1
ORDER BY A.ENGSTME;
结果是：
|ENGSTME |ENGSTME_PREV |TS_DURATION |TS_DIFF_SEC|
|-------------------|-------------------|--------------|-----------|
|2020-02-17 09:46:19| | | |
|2020-02-17 09:46:29|2020-02-17 09:46:19|00000000000010|10 |
|2020-02-17 09:50:10|2020-02-17 09:46:29|00000000000341|221 |
|2020-02-17 10:01:11|2020-02-17 09:50:10|00000000001101|661 |
|2020-02-17 10:12:16|2020-02-17 10:01:11|00000000001105|665 |
|2020-02-17 11:10:17|2020-02-17 10:12:16|00000000005801|3481 |
其想法是首先枚举行，然后重新加入行，以获取当前行的附加列中的上一个时间戳。您可以对每个组(如果有的话(进行枚举，而不是像示例中那样对整个数据集进行枚举(在开头的OVER中添加相应的PARTITION BY子句，并在JOIN中添加此类组的列(
您可以使用TS_DURATION列，也可以在TS_DIFF_SEC上使用自己的计算来获得所需的其他列。
请提供此样本数据所需的确切结果，如果不是您想要的。