Oracle 将打孔时钟数据分组为24小时周期，从time_in打孔开始

我们有从员工任务到工作订单的打孔数据。当遇到这些记录时，我需要根据员工组知道24个时间段的calc_hrs (表中的字段) 是否> 13小时。

我能够开始:

with grp_starts as (
  select id, time_in, time_out, calculated_hrs, pr_emp_id, source, source_id, note, trunc(time_in) as prod_date, 'Alert#1842' as user_id, sysdate as timestamp,
 case
  when time_in >= lag(time_in) over(order by time_in, time_out) and time_in <= lag(time_out) over(order by time_in, time_out)
  then 0 else 1
 end grp_start
 from ta_labor
 where nvl(processed,'N')='N'
 and source in ('WO')
 order by time_in
)
, grps as (
 select id, time_in, time_out, calculated_hrs,
 sum(grp_start) over(order by time_in, time_out) grp
 from grp_starts
)
select
 id,
 (time_in) time_in,
 (time_out) time_out,
 (calculated_hrs) calc_hrs,
 grp,
 (select sum(calculated_hrs) from grps where grp=dtl.grp) total
from grps dtl
--group by grp
--having sum(calculated_hrs) > 13  --hide or use this row to debug the data
order by time_in, time_out
复制

但是我的工作副本，试图获得重叠的时间，我无法处理24小时的时间段分组。我的分组只是告诉我连续的打孔记录是否重叠。我想以此为基础，但无法弄清楚如何定义相应的时间段和分组。

以下是示例数据:

id,time_in,time_out,calc_hrs,grp,total
5341,7/21/17 09:00:00 AM,7/21/17 10:00:00 AM,1.00,1,1.00
5707,7/31/17 12:23:50 PM,7/31/17 12:33:42 PM,0.16,2,0.16
5711,7/31/17 12:47:57 PM,7/31/17 12:49:01 PM,0.02,3,0.02
5713,7/31/17 12:49:16 PM,7/31/17 12:49:40 PM,0.01,4,0.01
6860,8/29/17 10:50:26 AM,8/29/17 10:52:47 AM,0.04,5,0.11
6861,8/29/17 10:50:55 AM,8/29/17 10:53:13 AM,0.04,5,0.11
6862,8/29/17 10:51:08 AM,8/29/17 10:52:53 AM,0.03,5,0.11
6863,8/29/17 10:53:15 AM,9/8/17 01:14:05 PM,242.34,6,727.01
6864,8/29/17 10:53:31 AM,9/8/17 01:14:05 PM,242.34,6,727.01
6866,8/29/17 10:53:53 AM,9/8/17 01:14:05 PM,242.33,6,727.01
复制

如您所见，我的 “grp” 仅提供重叠时间，而不是我最终想要的。我希望输出反映第一次time_in定义一个24小时的周期，并在该周期内分组，然后下一个，依此类推:

所需输出:

id,time_in,time_out,calc_hrs,grp,total
5341,7/21/17 09:00:00 AM,7/21/17 10:00:00 AM,1.00,1,1.00

5707,7/31/17 12:23:50 PM,7/31/17 12:33:42 PM,0.16,2,0.19
5711,7/31/17 12:47:57 PM,7/31/17 12:49:01 PM,0.02,2,0.19
5713,7/31/17 12:49:16 PM,7/31/17 12:49:40 PM,0.01,2,0.19

6860,8/29/17 10:50:26 AM,8/29/17 10:52:47 AM,0.04,3,727.12
6861,8/29/17 10:50:55 AM,8/29/17 10:53:13 AM,0.04,3,727.12
6862,8/29/17 10:51:08 AM,8/29/17 10:52:53 AM,0.03,3,727.12
6863,8/29/17 10:53:15 AM,9/8/17 01:14:05 PM,242.34,3,727.12
6864,8/29/17 10:53:31 AM,9/8/17 01:14:05 PM,242.34,3,727.12
6866,8/29/17 10:53:53 AM,9/8/17 01:14:05 PM,242.33,3,727.12
复制

然后，我可以标记超过我们一天的阈值打孔的记录，这很13.00，不必担心午夜时分的三档打孔。我确定我已经使问题复杂化了，但是我陷入困境，似乎无法摆脱困境。

示例日期表:

create table ta_labor (id int, time_in date, time_out date, calculated_hours number(5,2), pr_emp_id int, source varchar2(10));

with this data in it:

insert into ta_labor values (5341, to_date('7/21/2017 09:00:00', 'mm/dd/yyyy hh24:mi:ss'), to_date('7/21/2017 10:00:00', 'mm/dd/yyyy hh24:mi:ss'), 1, 9081, 'WO');
insert into ta_labor values (5707, to_date('7/31/2017 12:23:50', 'mm/dd/yyyy hh24:mi:ss'), to_date('7/31/2017 12:33:42', 'mm/dd/yyyy hh24:mi:ss'), 0.16, 9081, 'WO');
insert into ta_labor values (5711, to_date('7/31/2017 12:47:57', 'mm/dd/yyyy hh24:mi:ss'), to_date('7/31/2017 12:49:01', 'mm/dd/yyyy hh24:mi:ss'), 0.02, 9081, 'WO');
insert into ta_labor values (5713, to_date('7/31/2017 12:49:16', 'mm/dd/yyyy hh24:mi:ss'), to_date('7/31/2017 12:49:40', 'mm/dd/yyyy hh24:mi:ss'), 0.01, 9081, 'WO');
insert into ta_labor values (6860, to_date('8/29/2017 10:50:26', 'mm/dd/yyyy hh24:mi:ss'), to_date('8/29/2017 10:52:47', 'mm/dd/yyyy hh24:mi:ss'), 0.04, 9081, 'WO');
insert into ta_labor values (6861, to_date('8/29/2017 10:50:55', 'mm/dd/yyyy hh24:mi:ss'), to_date('8/29/2017 10:53:13', 'mm/dd/yyyy hh24:mi:ss'), 0.04, 9081, 'WO');
insert into ta_labor values (6862, to_date('8/29/2017 10:51:08', 'mm/dd/yyyy hh24:mi:ss'), to_date('8/29/2017 10:52:53', 'mm/dd/yyyy hh24:mi:ss'), 0.03, 9081, 'WO');
insert into ta_labor values (6863, to_date('8/29/2017 10:53:15', 'mm/dd/yyyy hh24:mi:ss'), to_date('9/8/2017 13:14:05', 'mm/dd/yyyy hh24:mi:ss'), 242.34, 9081, 'WO');
insert into ta_labor values (6864, to_date('8/29/2017 10:53:31', 'mm/dd/yyyy hh24:mi:ss'), to_date('9/8/2017 13:14:05', 'mm/dd/yyyy hh24:mi:ss'), 242.34, 9081, 'WO');
insert into ta_labor values (6866, to_date('8/29/2017 10:53:53', 'mm/dd/yyyy hh24:mi:ss'), to_date('9/8/2017 13:14:05', 'mm/dd/yyyy hh24:mi:ss'), 242.33, 9081, 'WO');
复制

因此，您希望将行分组为24小时周期。但是，如果两组之间的时间超过24小时，您想从下一个时间的确切时间开始下一个时期？

如果你在12c，这很容易。使用匹配 _ 识别!

定义一个 “日” 模式变量。这将匹配始终正确的开始变量一天内的所有行。内置的match_number返回组号:

select * from ta_labor
match_recognize (
  order by time_in
  measures 
    match_number() as grp
  all rows per match
  pattern ( st dy* ) 
  define 
    dy as dy.time_in <= st.time_in + 1
);

TIME_IN                GRP   ID     TIME_OUT               CALCULATED_HOURS   PR_EMP_ID   SOURCE   
21-JUL-2017 09:00:00       1   5341 21-JUL-2017 10:00:00                    1        9081 WO       
31-JUL-2017 12:23:50       2   5707 31-JUL-2017 12:33:42                 0.16        9081 WO       
31-JUL-2017 12:47:57       2   5711 31-JUL-2017 12:49:01                 0.02        9081 WO       
31-JUL-2017 12:49:16       2   5713 31-JUL-2017 12:49:40                 0.01        9081 WO       
29-AUG-2017 10:50:26       3   6860 29-AUG-2017 10:52:47                 0.04        9081 WO       
29-AUG-2017 10:50:55       3   6861 29-AUG-2017 10:53:13                 0.04        9081 WO       
29-AUG-2017 10:51:08       3   6862 29-AUG-2017 10:52:53                 0.03        9081 WO       
29-AUG-2017 10:53:15       3   6863 08-SEP-2017 13:14:05               242.34        9081 WO       
29-AUG-2017 10:53:31       3   6864 08-SEP-2017 13:14:05               242.34        9081 WO       
29-AUG-2017 10:53:53       3   6866 08-SEP-2017 13:14:05               242.33        9081 WO
复制

如果您想了解更多有关其工作原理的信息，请阅读:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533477800346658909

当你被困在11.2上时，你可以使用递归解决方案。这从数据集中最早的time_in行开始。然后在每次迭代中找到与下一次的行。

要进行分组，请添加两个计算列。

每组的开始时间。测试当前行的time_in (或time_out，取决于您定义24个周期的方式) 是否在组开始时间的一天内。如果是，则保留当前开始时间。否则将其替换为当前time_in。

对组列进行类似的测试。根据是否在小组开始时间的一天内增加它。

with times as (
  select t.*,
         min(time_in) over () mn ,
         lead(time_in) over (order by time_in) next_rec
  from   ta_labor t
), grps (id, time_in, time_out, calc_hrs, grp, grp_start, next_rec) as (
  select id, time_in, time_out, calculated_hours, 
         1 as grp, time_in grp_start, next_rec
  from   times
  where  time_in = mn
  union all
  select ta.id, ta.time_in, ta.time_out, ta.calculated_hours, 
         case
           when ta.time_in <= g.grp_start + 1 then grp 
           else grp + 1
         end as grp, 
         case
           when ta.time_in <= g.grp_start + 1 then g.grp_start
           else ta.time_in
         end as grp_start, ta.next_rec
  from   times ta 
  join   grps g
  on     ta.time_in = g.next_rec
)
  select * from grps;

ID     TIME_IN                TIME_OUT               CALC_HRS   GRP   GRP_START              NEXT_REC               
  5341 21-JUL-2017 09:00:00   21-JUL-2017 10:00:00            1     1 21-JUL-2017 09:00:00   31-JUL-2017 12:23:50   
  5707 31-JUL-2017 12:23:50   31-JUL-2017 12:33:42         0.16     2 31-JUL-2017 12:23:50   31-JUL-2017 12:47:57   
  5711 31-JUL-2017 12:47:57   31-JUL-2017 12:49:01         0.02     2 31-JUL-2017 12:23:50   31-JUL-2017 12:49:16   
  5713 31-JUL-2017 12:49:16   31-JUL-2017 12:49:40         0.01     2 31-JUL-2017 12:23:50   29-AUG-2017 10:50:26   
  6860 29-AUG-2017 10:50:26   29-AUG-2017 10:52:47         0.04     3 29-AUG-2017 10:50:26   29-AUG-2017 10:50:55   
  6861 29-AUG-2017 10:50:55   29-AUG-2017 10:53:13         0.04     3 29-AUG-2017 10:50:26   29-AUG-2017 10:51:08   
  6862 29-AUG-2017 10:51:08   29-AUG-2017 10:52:53         0.03     3 29-AUG-2017 10:50:26   29-AUG-2017 10:53:15   
  6863 29-AUG-2017 10:53:15   08-SEP-2017 13:14:05       242.34     3 29-AUG-2017 10:50:26   29-AUG-2017 10:53:31   
  6864 29-AUG-2017 10:53:31   08-SEP-2017 13:14:05       242.34     3 29-AUG-2017 10:50:26   29-AUG-2017 10:53:53   
  6866 29-AUG-2017 10:53:53   08-SEP-2017 13:14:05       242.33     3 29-AUG-2017 10:50:26
复制