时间戳

首页 > 解决方案 > oracle sql group by change in time valid from valid to

sql - oracle sql group by change in time valid from valid to

问题描述

我有以下关于票状态随时间变化的历史数据，我感兴趣的是使用 VALFROM-VALTO 获得结果

源数据

ticket_ID, change-time        , queue
--------------------------------------
001      , 2018-01-01 00:00:00, queue1
001      , 2018-01-01 00:01:00, queue1
001      , 2018-01-01 00:03:00, queue2
001      , 2018-01-01 00:04:00, queue1
001      , 2018-01-01 00:05:00, queue3

目标数据

ticket_ID, valfrom            , valto              , queue
-----------------------------------------------------------
001      , 2018-01-01 00:00:00, 2018-01-01 00:02:59, queue1
001      , 2018-01-01 00:03:00, 2018-01-01 00:03:59, queue2
001      , 2018-01-01 00:04:00, 2018-01-01 00:04:59, queue1
001      , 2018-01-01 00:05:00, 2999-12-31 23:59:59, queue3

我认为这可以使用带有 OVER 的 ROW_NUMBER 函数来完成。任何有用的建议都非常受欢迎。

标签： sqloraclegroup-byrow-number

解决方案

使用行号差异方法将行分类（基于连续队列值相同），lead以从下一行获取值。此后，这是一个group by获取从和到时间的操作。

select ticketid,queue,min(change_time),
coalesce(max(next_change_time)-interval '1' second,timestamp '2999-12-31 23:59:59')
from (select t.*,row_number() over(partition by ticketid order by change_time) 
                 -row_number() over(partition by ticketid,queue order by change_time) as grp,
      lead(change_time) over(partition by ticketid order by changetime) as next_change_time
      from tbl t 
     ) t
group by ticketid,queue,grp

推荐阅读