sql - DB2 SQL 添加列值
问题描述
我正在尝试将一些计算列添加在一起,其中这些列的值是当前记录和下一条记录之间的时间戳差异函数的子字符串。不幸的是,子字符串确实返回了空值,我试图通过 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 |
这个想法是首先枚举行,然后重新加入行以获取当前行的附加列中的先前时间戳。您可以对每个组(如果有)进行这样的枚举,而不是像示例中那样对整个数据集进行枚举(在开头添加相应的PARTITION BY
子句并在 中添加此类组的列)。
您可以使用列或使用您自己的计算来获得您想要的其他列。 OVER
JOIN
TS_DURATION
TS_DIFF_SEC
请提供此示例数据所需的确切结果,如果这不是您想要的。
推荐阅读
- java - Android 数据库表数据到特定模型对象
- javascript - 如何仅在使用特定方法后导出类?
- react-native - 反应原生按钮 onpress 显示 2 个新按钮并使其他内容不可点击
- javascript - Vue3 vue-draggable-next 配置问题
- swift - 此版本的 watchOS 不支持 WatchKit 1 应用程序。退出“(空)”
- flutter - return new futureBuilder 在屏幕尺寸更改时不断向服务器请求
- graph - 我如何知道以 CSR 格式形成的 RCV1 数据集中的单词序列?
- javascript - 按 btn 时删除第一个元素
- mysql - 如何从显示引擎 innodb 状态中获取最新死锁报告的日期和时间
- android - Kotlin Navigation Drawer 使用带有 RecyclerView 的导航组件作为菜单