记录之间的时间差 [英] Difference in time between records

问题描述

我有一个表，其中有一个时间戳列(称为时间戳；这是标准的Oracle DATE数据类型).记录大约相隔4-11分钟，每小时大约7或8条记录，我正在尝试确定它们是否有任何模式.

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.

有没有一种简单的方法可以查看每条记录，以及上一条记录之后记录的分钟数?

Is there an easy way to see each record, and the number of minutes that record occurred after the previous record?

谢谢， 安迪丹

推荐答案

这是Oracle 9i +，使用 LAG函数即可获取先前的时间戳值，而无需自行加入:

This is Oracle 9i+, using the LAG function to get the previous timestamp value without needing to self join:

SELECT t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp) AS diff
  FROM YOUR_TABLE t

...但是由于整数代表结果的天数，因此少于24小时的差异将很小.另外，如果没有更早的值，则LAG将返回NULL －与使用OUTER JOIN一样.

...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.

要查看分钟，请使用ROUND函数:

To see minutes, use the ROUND function:

SELECT ROUND((t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp)) *1440) AS diff_in_minutes
  FROM YOUR_TABLE t

这篇关于记录之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！