Oracle 11g-SQL计算多行之间的时间差 [英] Oracle 11g - SQL to Calculate time difference between several rows

问题描述

问题

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

I'm still finding my feet with SQL and trying to calculate how long a certain user has been scanning items during their shift.

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

Each scan is timestamped generating a unique 9 digit sequence number (SEQ column) and date/time in the format 05-NOV-16 15:35:24 (THE_DATE column).

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

The person may be scanning for several hours, and what im trying to do is subtract the first timestamp they generated from the very last timestamp at the end of their shift.

例如，给定此数据示例:

So for example given this data sample:

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

I would like to subtract the timestamp in the first row for Mike Derry, from the last row on which he appears, row 5 in this case, so that i have an answer in hours (1.25).

最终结果应按天，用户名，名字和姓氏分组.

the final result should be grouped by day and by user_id,first_name and last_name.

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

So far i have looked online and at the oracle documentation ,which led me to try using the LEAD function which seemed promising. It looks at the next rows to find the next timestamp where a userid appears next and then partitions by this userid to create a new column with that timestamp.

所以SQL看起来像这样

So the SQL looked like this

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

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

However this is giving me incorrect results as it seems to double count the time difference. Im sure you SQL gurus have a more elegant way around this as i dont think this function suits this particular problem :)

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

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

非常感谢您的帮助

推荐答案

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

这篇关于Oracle 11g-SQL计算多行之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！