Oracle 11g-SQL计算多行之间的时间差

Question

问题

我仍在用SQL寻找自己的脚，并尝试计算某个用户在轮班期间扫描项目多长时间。

每次扫描都带有时间戳，生成唯一的9位数序列号（ SEQ 列）和格式05-NOV-16 15:35:24（ THE_DATE
列）的日期/时间。

这个人可能正在扫描几个小时，我想做的是从轮班结束时的最后一个时间戳减去他们生成的第一个时间戳。

因此，例如，给出此数据样本：

+-----------+--------------------+--------+---------+---------+------------+-----------+
|    SEQ    |      THE_DATE      | SCANID | LOCATN  | USER_ID | FIRST_NAME | LAST_NAME |
+-----------+--------------------+--------+---------+---------+------------+-----------+
| 103939758 | 05-NOV-16 14:36:22 | 194972 | DOOR 19 | AX9868  | Mike       | Derry     |
| 103939780 | 05-NOV-16 14:38:07 | 194972 | DOOR 19 | AX9868  | Mike       | Derry     |
| 103939792 | 05-NOV-16 14:39:24 | 194972 | DOOR 19 | AX9868  | Mike       | Derry     |
| 103940184 | 05-NOV-16 15:16:53 | 194972 | DOOR 19 | AX9868  | Mike       | Derry     |
| 103940185 | 05-NOV-16 15:51:41 | 194972 | DOOR 19 | AX9868  | Mike       | Derry     |
| 103940214 | 05-NOV-16 09:51:42 | 194993 | DOOR 16 | BC1910  | Tony       | McCann    |
| 103940215 | 05-NOV-16 15:19:06 | 194993 | DOOR 16 | BC1910  | Tony       | McCann    |
|+-----------+--------------------+--------+---------+---------+------------------------

期望的结果

我想从出现的最后一行减去Mike Derry的第一行的时间戳，在这种情况下为第5行，这样我就可以在数小时内得到答案（1.25）。

最终结果应按天，user_id，first_name和last_name分组。

到目前为止，我已经在线查看了oracle文档，这使我尝试使用看起来很有希望的LEAD函数。它查看下一行以查找下一个出现在其中的用户标识的下一个时间戳，然后按该用户ID进行分区，以创建具有该时间戳的新列。

所以SQL看起来像这样

SELECT SEQ, THE_DATE,SCANID,LOCATN,USER_ID,LEAD(SYSDAT ) OVER (PARTITION BY USER_ID ORDER BY SYSDAT) AS NEXT_SCAN 
FROM myTable...

但是，这给了我错误的结果，因为它似乎重复计算了时间差。我确定您的SQL专家对此有更优雅的解决方法，因为我认为此功能不适合此特定问题:)

因此，我试图实现的最终结果是：

+-----------+---------+------------+-----------+-----------+
| THE_DATE  | USER_ID | FIRST_NAME | LAST_NAME | TOTAL_HRS |
+-----------+---------+------------+-----------+-----------+
| 05-NOV-16 | AX9868  | Mike       | Derry     |      1.25 |
| 05-NOV-16 | BC1910  | Tony       | McCann    |      5.47 |
+-----------+---------+------------+-----------+-----------+

非常感谢您的帮助

Answer 1

SELECT TRUNC(THE_DATE) as THE_DATE, USER_ID, FIRST_NAME, LAST_NAME,
       MAX(THE_DATE) - MIN(THE_DATE) as TOTAL_HRS 
FROM yourTable
GROUP BY TRUNC(THE_DATE), USER_ID, FIRST_NAME, LAST_NAME