作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有以下 View ,我需要做的是获取具有相同 Vehicle AND OrderCode 的每 2 条记录之间的字段 ActionDate 的日期差异,我如何在 Oracle 数据库中实现这一点。
还要考虑到减去的日期应该是具有 的日期。模式 O - 模式 I
我需要获取差异列表才能获得该时间的平均值。
谢谢你的帮助。
最佳答案
您可以使用分析 LAG() OVER() 函数来获取日期之间的差异。
例如,
SQL> WITH t AS
2 (
3 select 'O' as "MODE", 'V1234567890' as Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 16:34:35','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
4 union all
5 select 'I' as "MODE", 'V1234567890' as Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 15:27:09','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
6 union all
7 select 'O' as "MODE", 'V2987654321' as Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 14:03:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
8 union all
9 select 'I' as "MODE", 'V2987654321' as Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 13:47:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
10 union all
11 select 'O' as "MODE", 'V2987654321' as Vehicle, '1411185798' as OrderCode, to_date('2014-11-20 01:40:58','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
12 union all
13 SELECT 'I' AS "MODE", 'V2987654321' AS Vehicle, '1411185798' AS OrderCode, to_date('2014-11-20 00:47:02','yyyy-mm-dd hh24:mi:ss') AS ActionDate FROM dual
14 )
15 SELECT "MODE",
16 Vehicle,
17 OrderCode,
18 TO_CHAR(ActionDate,'yyyy-mm-dd hh24:mi:ss') dt,
19 TO_CHAR(LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate),'yyyy-mm-dd hh24:mi:ss') lag_dt,
20 ActionDate - LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate) diff
21 FROM t;
M VEHICLE ORDERCODE DT LAG_DT DIFF
- ----------- ---------- ------------------- ------------------- ----------
I V1234567890 1411196232 2014-11-19 15:27:09
O V1234567890 1411196232 2014-11-19 16:34:35 2014-11-19 15:27:09 .046828704
I V2987654321 1411185798 2014-11-20 00:47:02
O V2987654321 1411185798 2014-11-20 01:40:58 2014-11-20 00:47:02 .037453704
I V2987654321 1411206614 2014-11-20 13:47:02
O V2987654321 1411206614 2014-11-20 14:03:02 2014-11-20 13:47:02 .011111111
6 rows selected.
SQL>
SELECT "MODE",
Vehicle,
OrderCode,
TO_CHAR(ActionDate,'yyyy-mm-dd hh24:mi:ss') dt,
TO_CHAR(LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate),'yyyy-mm-dd hh24:mi:ss') lag_dt,
ActionDate - LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate) diff
FROM your_table;
关于sql - Oracle 中不同行中相同字段的日期/时间差异,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30098289/
在我的 android 应用程序中,我想在同一行中实现 2 个文本字段。如果文本太长可以转到下一行。所以我使用了更精简的水平布局并添加了 2 个文本字段。 [例如:TextView1、TextV
由于node使用了javascript,它可以充当webrtc peer吗? 然后我可以将它编码为 VP8 流并广播给所有其他对等方吗? 最佳答案 WebRTC 是一个浏览器 API 而不是 Java
我是一名优秀的程序员,十分优秀!