Oracle 按状态查找连续行的最后时间

你好，


我需要一些有关Oracle Sql查询的帮助。对于每一天，我必须计算每个周期进出之间的时间差，然后将差异求和为总工作分钟。我遇到的主要问题是输入数据是可变的，所以我可以有1或2或3或N时间与状态IN和1或2或3或N时间与状态OUT。因此，我使用了row_number 1用于set IN和row_number用于set OUT的分析功能，但是我无法始终确定每个周期的IN或OUT状态的最大 (时间)。

附言: 我的问题是，在第三周期的第30天，我有一对30-01-2018 13:02:21 -- 外出和30-01-2018 12:52:16 -- 进来，我需要外出是30-01-2018 14:55:02 -- 外出，在第31天也是如此
在第一个周期中，我让这对夫妇31-01-2018 10:03:58-出去和
31-01-2018 10:02:39-进来，我需要31-01-2018 11:11:33-出去

感谢您的支持!

 create table test
 (
 time date not null 
 ,evolutionid int not null
 ,in_out_status varchar2(3) not null
 );


insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:52:34 AM', 'mm-dd-yyyy HH:MI:SS AM'),101,'IN'); 
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:52:54 AM', 'mm-dd-yyyy HH:MI:SS AM'),100,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:54:10 AM', 'mm-dd-yyyy HH:MI:SS AM'),99,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 10:28:27 AM', 'mm-dd-yyyy HH:MI:SS AM'),98,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 11:43:50 AM', 'mm-dd-yyyy HH:MI:SS AM'),97,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 12:37:40 PM', 'mm-dd-yyyy HH:MI:SS AM'),96,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 12:37:57 PM', 'mm-dd-yyyy HH:MI:SS AM'),95,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 2:16:59 PM', 'mm-dd-yyyy HH:MI:SS AM'),94,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 2:58:00 PM', 'mm-dd-yyyy HH:MI:SS AM'),93,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 3:34:22 PM', 'mm-dd-yyyy HH:MI:SS AM'),92,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:20:06 PM', 'mm-dd-yyyy HH:MI:SS AM'),91,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:31:36 PM', 'mm-dd-yyyy HH:MI:SS AM'),90,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:56:48 PM', 'mm-dd-yyyy HH:MI:SS AM'),89,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 9:53:08 AM', 'mm-dd-yyyy HH:MI:SS AM'),88,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:24:15 AM', 'mm-dd-yyyy HH:MI:SS AM'),87,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:25:09 AM', 'mm-dd-yyyy HH:MI:SS AM'),86,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:55:11 AM', 'mm-dd-yyyy HH:MI:SS AM'),85,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 11:12:52 AM', 'mm-dd-yyyy HH:MI:SS AM'),84,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 12:52:16 PM', 'mm-dd-yyyy HH:MI:SS AM'),83,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 1:02:21 PM', 'mm-dd-yyyy HH:MI:SS AM'),82,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 2:55:02 PM', 'mm-dd-yyyy HH:MI:SS AM'),81,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 2:57:09 PM', 'mm-dd-yyyy HH:MI:SS AM'),80,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 3:37:14 PM', 'mm-dd-yyyy HH:MI:SS AM'),79,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 4:53:42 PM', 'mm-dd-yyyy HH:MI:SS AM'),78,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 5:56:30 PM', 'mm-dd-yyyy HH:MI:SS AM'),77,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 6:01:58 PM', 'mm-dd-yyyy HH:MI:SS AM'),76,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 10:02:39 AM', 'mm-dd-yyyy HH:MI:SS AM'),75,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 10:03:58 AM', 'mm-dd-yyyy HH:MI:SS AM'),74,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:11:33 AM', 'mm-dd-yyyy HH:MI:SS AM'),73,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:16:01 AM', 'mm-dd-yyyy HH:MI:SS AM'),72,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:30:16 AM', 'mm-dd-yyyy HH:MI:SS AM'),71,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:52:41 AM', 'mm-dd-yyyy HH:MI:SS AM'),70,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 12:11:36 PM', 'mm-dd-yyyy HH:MI:SS AM'),69,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 5:33:18 PM', 'mm-dd-yyyy HH:MI:SS AM'),68,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 6:25:50 PM', 'mm-dd-yyyy HH:MI:SS AM'),67,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:06:11 PM', 'mm-dd-yyyy HH:MI:SS AM'),66,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:07:30 PM', 'mm-dd-yyyy HH:MI:SS AM'),65,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:10:54 PM', 'mm-dd-yyyy HH:MI:SS AM'),64,'OUT');

-- truncate table test;


alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';
select 
BB.time_BB
,BB.in_out_status_BB
,AA.time_AA
,AA.in_out_status_AA  
,round(((BB.time_BB - AA.time_AA) * 24 * 60),2) AS diff_time_minutes
from
(SELECT
time  as time_AA
,in_out_status as in_out_status_AA
,row_number() over ( order by  evolutionid desc) as rn_AA
FROM
test) AA
    
   inner join
   
(SELECT
time  as time_BB
,in_out_status as in_out_status_BB
,row_number() over ( order by  evolutionid desc) as rn_BB
FROM
test) BB

on  AA.rn_AA+1 = BB.rn_BB   -- and  mod (AA.rn_AA,2)= 0
and  AA.in_out_status_AA  not like 'OUT' and  BB.in_out_status_BB  not like 'IN' 
order by AA.TIME_AA ;
复制

所以你有一个行和行序列:

IN *
OUT *
IN
IN *
OUT
OUT *
IN
IN
IN *
OUT
OUT
OUT *
复制

你想把最后一个进和最后一个出配对，然后再下一个进？即旁边有星星的行？

如果是这样，这是另一个问题match_recognize变得容易!

寻找一个或多个输入后跟一个或多个输出的模式。并返回最后一次为每个:

select * 
from   test match_recognize (
  order by time
  measures
    last (time_in.time) as last_in,
    last (time_out.time) as last_out
  pattern ( time_in+ time_out+ )
  define
    time_in as in_out_status = 'IN',
    time_out as in_out_status = 'OUT'
);

LAST_IN               LAST_OUT              
29-01-2018 09:52:34   29-01-2018 09:52:54   
29-01-2018 12:37:40   29-01-2018 12:37:57   
29-01-2018 16:20:06   29-01-2018 16:31:36   
30-01-2018 09:53:08   30-01-2018 10:24:15   
30-01-2018 10:25:09   30-01-2018 10:55:11   
30-01-2018 12:52:16   30-01-2018 14:55:02   
30-01-2018 17:56:30   30-01-2018 18:01:58   
31-01-2018 10:02:39   31-01-2018 11:11:33   
31-01-2018 11:16:01   31-01-2018 11:30:16   
31-01-2018 11:52:41   31-01-2018 12:11:36   
31-01-2018 18:25:50   31-01-2018 19:06:11   
31-01-2018 19:07:30   31-01-2018 19:10:54 
复制

可悲的是，这是一个12c功能。而且您使用的是11g。所以你需要一些不同的东西...

一种方法是:

-使用Tabibitosan方法的变体对连续的进进出出进行分组
-对于每个状态，这将连续一次汇总在一起，但是值中会有空白。这使得很难知道哪个IN与哪个OUT链接。
-用从1开始的连续数字重新编号组。Dense_rank会这样做
-透视结果

这给出了:

with tabibitosan as (  
  select time, in_out_status,
         row_number() over (order by time) -
           row_number() over (partition by in_out_status order by time) grp
  from   test
), grps as (
  select dense_rank() over ( partition by in_out_status order by grp ) dr,
         t.time, t.in_out_status
  from   tabibitosan t
)
  select *
  from   grps
  pivot (
    max(time) for
    in_out_status in ( 'IN' last_in, 'OUT' last_out)
  )
  order  by dr;

DR   LAST_IN                LAST_OUT               
   1 29-JAN-2018 09:52:34   29-JAN-2018 09:52:54   
   2 29-JAN-2018 12:37:40   29-JAN-2018 12:37:57   
   3 29-JAN-2018 16:20:06   29-JAN-2018 16:31:36   
   4 30-JAN-2018 09:53:08   30-JAN-2018 10:24:15   
   5 30-JAN-2018 10:25:09   30-JAN-2018 10:55:11   
   6 30-JAN-2018 12:52:16   30-JAN-2018 14:55:02   
   7 30-JAN-2018 17:56:30   30-JAN-2018 18:01:58   
   8 31-JAN-2018 10:02:39   31-JAN-2018 11:11:33   
   9 31-JAN-2018 11:16:01   31-JAN-2018 11:30:16   
  10 31-JAN-2018 11:52:41   31-JAN-2018 12:11:36   
  11 31-JAN-2018 18:25:50   31-JAN-2018 19:06:11   
  12 31-JAN-2018 19:07:30   31-JAN-2018 19:10:54
复制