sql - 计算连续行的时间差

有人可以根据我的以下要求帮助我进行 SQL 查询吗？
我有一张这样的 table 。

S_ID      S_ACTV_CODE   S_USER  S_DATETIME            S_ACT_IND
AAA-111   NULL          USER1   2015-06-15 00:21:06   0
AAA-111   2             USER1   2015-06-15 00:21:07   0
AAA-111   2             USER1   2015-06-15 00:25:12   0
AAA-111   4             USER2   2015-06-17 03:20:33   0
AAA-111   3             USER1   2015-06-17 03:43:25   0
AAA-111   4             USER3   2015-06-22 05:02:37   0
AAA-111   4             USER4   2015-06-23 05:25:05   1
AAA-112   NULL          USER4   2015-06-25 11:11:11   0
AAA-112   4             USER3   2015-06-25 11:11:12   0
AAA-112   4             USER4   2015-06-26 20:25:49   0
AAA-112   4             USER2   2015-06-29 18:04:32   1
AAA-113   NULL          USER2   2015-06-24 07:10:37   0
AAA-113   NULL          USER1   2015-06-24 07:10:41   0
AAA-113   3             USER1   2015-06-24 07:10:43   1

基本上我想计算 S_Users 在特定 S_ACTV_CODE 上花费的时间:

S_ACTV_CODE_PREV 表示之前的事件记录。

S_START_TIME 是 S_ACTV_CODE 启动时 S_DATETIME 的时间

S_END_TIME 是 S_ACTV_CODE 更改为另一个 S_ACTV_CODE 之前的时间

第一条记录，S_ACTV_CODE为null，所以没有S_ACTV_CODE_PREV，所以S_ACTV_CODE_PREV为NULL

对于第二条记录，S_ACTV_CODE 有一些值，但对于第一条记录，S_ACTV_CODE_PREV 为 NULL。所以第二个记录
S_ACTV_CODE_PREV 也是 NULL

对于最后一条记录(意味着 S_ACTV_IND = 1)，用户当前正在处理它并且 S_ACTV_CODE 没有改变。所以 S_END_TIME 是一个开放的
时间，我们希望将其保留为 NULL

所以结果应该如下:

S_ID      S_ACTV_CODE_PREV  S_ACTV_CODE_CURR  S_USER  S_START_TIME         S_END_TIME             TIME_SPENT (in Sec)
AAA-111   NULL              NULL              USER1   2015-06-15 00:21:06  2015-06-15 00:21:07    1
AAA-111   NULL              2                 USER1   2015-06-15 00:21:07  2015-06-17 03:20:33    183566
AAA-111   2                 4                 USER2   2015-06-17 03:20:33  2015-06-17 03:43:25    1372
AAA-111   4                 3                 USER3   2015-06-17 03:43:25  2015-06-22 05:02:37    436752
AAA-111   3                 4                 USER4   2015-06-22 05:02:37  NULL                   NULL
AAA-112   NULL              NULL              USER4   2015-06-25 11:11:11  2015-06-25 11:11:12    1
AAA-112   NULL              4                 USER3   2015-06-25 11:11:12  NULL                   NULL
AAA-113   NULL              NULL              USER2   2015-06-24 07:10:37  2015-06-24 07:10:43    6
AAA-113   NULL              3                 USER1   2015-06-24 07:10:43  NULL                   NULL

最佳答案

重新编写 SQL 以获取行的第一个日期和最后一个用户。它使它变得更加丑陋，但这应该有效:

select
  s_id,
  lag(s_actv_code, 1) over (partition by s_id order by s_datetime asc) as s_actv_code_prev,
  s_actv_code,
  s_user,
  s_datetime as start_time,
  lead(s_datetime, 1) over (partition by s_id order by s_datetime asc) as end_time,
  datediff (second, s_datetime,
    lead(s_datetime, 1) over (partition by s_id order by s_datetime asc)) as duration
from
(
  select distinct
  s_id,
  S_ACTV_CODE,
  last_value(s_user)
  over (partition by s_id, S_ACTV_CODE, GRP
        order by S_datetime asc
        rows between current row and unbounded following) as s_user,
  first_value(s_datetime)
  over (partition by s_id, S_ACTV_CODE, GRP
        order by S_datetime asc
        ROWS UNBOUNDED PRECEDING
      ) as s_datetime
   from (
     select
       *,
       row_number() over (partition by s_id order by s_datetime asc) -
       row_number() over (partition by s_id, s_actv_code order by s_datetime asc) as GRP
     from
       table1
   ) X
) Y
order by s_id, start_time

编辑:由于对示例的新更改，通过 s_id 添加了分区。

您可以在 SQL Fiddle 中对此进行测试

关于sql - 计算连续行的时间差，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31117454/