Some applied personnel (work number) will be scheduling construction (have began to end time, the same process duration is not necessarily the same), the process involves a few steps, separated by commas, working duty roaster, process steps table,
Requirements:
Statistics steps a scheduling conflict situation, specific for a particular step will be three or more than three at the same time construction personnel usage at the same time.
Output: steps, work number (with a comma), the conflict of time conflict began (third time)
The following example has two conflict
L1 N1, N2 and N3 18:08:20
L4 N1, N3 and N4 interchange, N7 15:25:02
Want to achieve that through saved, use cursor traverses the roaster, grouping statistics, count (*) & gt;=3 inserted into a temporary table, wrote a half-day didn't write, strives for the great god help,
Add:
Roaster
Working process
start time to end time N1 18:02:01 18:30:20 S1
N2 18:07:31 18:31:20 S2
N3 18:08:20 18:35:20 S3
N4 interchange 19:00:02 19:10:10 S4
N2 13:02:01 14:30:20 S1
N3 15:07:31 15:31:20 S2
N4 interchange 15:08:20 15:35:20 S3
N1 15:25:02 16:10:10 S4
N7 15:26:03 17:10:10 S7
Process step
S1 L1, L2, L3,
L1, and S2 L4, L5
S3 L1, L4, about the
S4 L2, L4, L8
S7 L3 and L4
Collision detection results
Step gonghaowu conflict time
L1 N1, N2 and N3 18:08:20
L4 N1, N3 and N4 interchange, N7 15:25:02
CodePudding user response:
DeclareV_col varchar2 (10);
V_col1 varchar2 (20);
Cursor cur is the select col1, to_char (wm_concat (col) col from (select col, regexp_substr (col1, '[^,] +, 1, level) col1 from test1 connect by level & lt;=regexp_count (col1, '[and]) + 1 and the rowid=the prior rowid and (the prior dbms_random. Value) is not null) group by col1.
The begin
For cur_r in cur loop;
Select the col, the start_date from (select id, the start_date, end_date row_number () over (partition by rn order by the start_date) rn, wm_concat (id) over (partition by rn) col from (select id, the start_date, end_date, sum (rn) over (the order by the start_date) rn from
(select id, the start_date, end_date, case when rn> 0 then 0 else 1 end rn from
(select the id, the start_date, end_date lead_end_date - the start_date rn from (select id, the start_date, end_date, lag (end_date) over (the order by the start_date) lead_end_date from test where instr (' S1 and S3 and S2, col) & gt;=1))))) where an rn=3;
Wrote an hour the original poster to perfect the
Insert into the test values (' N1, to_date (' 18:02:01 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 18:30:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S1');
Insert into the test values (N2, to_date (' 18:07:31 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 18:31:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S2);
Insert into the test values (N3, to_date (' 18:08:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 18:35:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), "S3");
Insert into the test values (N4 interchange, to_date (' 19:00:02 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 19:10:10 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S4');
Insert into the test values (N2, to_date (' 13:02:01 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 14:30:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S1');
Insert into the test values (N3, to_date (' 15:07:31 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 15:31:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S2);
Insert into the test values (N4 interchange, to_date (' 15:08:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 15:35:20 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), "S3");
Insert into the test values (' N1, to_date (' 15:25:02 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 16:10:10 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S4');
Insert into the test values (' N7, to_date (' 15:26:03 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), to_date (' 17:10:10 2016-11-23 ', '- dd yyyy - mm hh24: mi: ss'), 'S7);
Insert into test1 values (' S1 'and' L1, L2, L3);
Insert into test1 values (' S2 ', 'L1, L4, L5,');
Insert into test1 values (' S3 ', 'L1, L4, about the');
Insert into test1 values (' S4 ', 'L2, L4, L8);
Insert into test1 values (' S7 ', 'L3 and L4);
CodePudding user response:
select job number, count (*) from table group by job number having the count (*) & gt;=3