oracle 记录之间的时间差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4308620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference in time between records
提问by AndyDan
I have a table that has (among others) a timestamp column (named timestamp; it's a standard Oracle DATE datatype). The records are about 4-11 minutes apart, about 7 or 8 records every hour, and I'm trying to determine if there is any pattern to them.
我有一个表（其中包括）一个时间戳列（名为时间戳；它是标准的 Oracle DATE 数据类型）。记录相隔大约 4-11 分钟，每小时大约有 7 或 8 条记录，我正在尝试确定它们是否存在任何模式。
Is there an easy way to see each record, and the number of minutes that record occurred after the previous record?
有没有一种简单的方法可以查看每条记录，以及该记录发生在上一条记录之后的分钟数？
Thanks, AndyDan
谢谢，安迪丹
回答by OMG Ponies
This is Oracle 9i+, using the LAG functionto get the previous timestamp value without needing to self join:
这是Oracle 9i+，使用LAG函数获取之前的时间戳值，无需自连接：
SELECT t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp) AS diff
FROM YOUR_TABLE t
...but because whole numbers represent the number of days in the result, a difference of less than 24 hours will be a fraction. Also, the LAG will return NULL if there's no earlier value -- same as if having used an OUTER JOIN.
...但是因为整数代表结果中的天数，小于 24 小时的差异将是一小部分。此外，如果没有更早的值，LAG 将返回 NULL——就像使用了 OUTER JOIN 一样。
To see minutes, use the ROUND function:
要查看分钟，请使用 ROUND 函数：
SELECT ROUND((t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp)) *1440) AS diff_in_minutes
FROM YOUR_TABLE t
回答by Andrew Cooper
If the records have sequential id's you could do a self join like this:
如果记录具有顺序 ID，您可以像这样进行自连接：
SELECT t2.*, t2.timestamp - t1.timestamp AS timediff
FROM foo t1 inner join foo.t2 on t1.id = t2.id-1
You'd probably need to tweak this to handle the first and last records, but that's the basics.
您可能需要调整它以处理第一条和最后一条记录，但这是基础知识。
