SQLize Online / PHPize Online  /  SQLtest Online

-- SCHEMA create table location_dict(id number, name varchar2(200)); create table unit_dict(unit_id number not null, location_id number, parent_id number, name varchar2(200), parameter_count number); create table pipes(matid number, pipe_no varchar2(200)); create table unit_passes(pass_id number, matid number, parent_pass_id number, unitid number, dt date, duration number); comment on column location_dict.id is 'Идентификатор участка'; comment on column location_dict.name is 'Наименование'; comment on column unit_dict.unit_id is 'Идентификатор агрегата'; comment on column unit_dict.location_id is 'Идентификатор участка'; comment on column unit_dict.parent_id is 'Идентификатор группового агрегата'; comment on column unit_dict.name is 'Наименование'; comment on column unit_dict.parameter_count is 'Количество параметров у агрегата'; comment on column pipes.matid is 'Идентификатор'; comment on column pipes.pipe_no is 'Номер трубы'; comment on column unit_passes.pass_id is 'Номер прохода'; comment on column unit_passes.matid is 'Идентифкатор продукции'; comment on column unit_passes.parent_pass_id is 'Идентификатор предыдущего прохода'; comment on column unit_passes.unitid is 'Агрегат'; comment on column unit_passes.dt is 'Дата обработки'; comment on column unit_passes.duration is 'Продолжительность, с'; alter table location_dict add constraint pk_location_dict primary key (id); alter table unit_dict add constraint pk_unit_dict primary key (unit_id); alter table unit_dict add constraint fk_location_dict foreign key (location_id) references location_dict (id) on delete cascade; alter table unit_dict add constraint fk_unit_dict foreign key (parent_id) references unit_dict (unit_id) on delete cascade; alter table pipes add constraint pk_pipes primary key (matid); alter table unit_passes add constraint pk_unit_passes primary key (pass_id); alter table unit_passes add constraint fk_unit_passes foreign key (parent_pass_id) references unit_passes (pass_id) on delete cascade; alter table unit_passes add constraint dk_pipes foreign key (matid) references pipes (matid) on delete cascade; commit; -- SQL delete from unit_dict; delete from location_dict; commit; insert into location_dict (id, name) values (1, 'Участок 1'); insert into location_dict (id, name) values (2, 'Участок 2'); insert into location_dict (id, name) values (3, 'Участок 3'); commit; insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (1, 1, null, 'ТЭСЦ', 100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (2, 2, null, 'ЦПиОП', 1000); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (3, 1, 1, 'ТФУ', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (4, 1, 1, 'ВСС', 2000); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (5, 1, 1, 'НСС', 1100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (6, 2, 2, 'ОНП', 100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (7, 2, 2, 'ОВП', 50); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (8, 1, 3, 'КГП', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (9, 1, 3, 'КФУ', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (10, 1, 4, 'ВСС1', 135); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (11, 1, 4, 'ВСС2', 135); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (12, 1, 4, 'ВСС3', 140); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (13, 1, 5, 'НСС1', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (14, 1, 5, 'НСС2', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (15, 1, 5, 'НСС3', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (16, 2, 6, 'Экструдер', 12); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (17, 2, 7, 'Покраска', 28); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (18, 2, 7, 'Мойка', 5); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (19, 2, 7, 'Покраска', 5); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (21, 3, null, 'Приемка', 0); commit; delete from pipes; delete from unit_passes; commit; insert into pipes (matid, pipe_no) values (123456, 'Итз125704.9'); insert into pipes (matid, pipe_no) values (123455, '125703.7_итз'); commit; insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (1, 123456, null, 1, to_date('01.04.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (2, 123456, 1, 2, to_date('01.04.2016', 'dd.mm.yyyy'), 20); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (3, 123455, null, 1, to_date('01.05.2016', 'dd.mm.yyyy'), 30); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (4, 123456, 2, 5, to_date('01.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (5, 123456, 4, 5, to_date('02.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (6, 123456, 5, 11, to_date('03.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (7, 123455, 3, 22, to_date('04.05.2016', 'dd.mm.yyyy'), 30); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (8, 123455, 7, 22, to_date('05.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (9, 123456, 6, 2, to_date('06.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (10, 123456, 9, 805, to_date('07.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (11, 123455, 8, 5, to_date('07.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (12, 123456, 10, 1, to_date('08.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (13, 123455, 11, 7, to_date('09.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (14, 123456, 12, 7, to_date('12.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (15, 123456, 14, 11, to_date('14.05.2016', 'dd.mm.yyyy'), 100); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (16, 123455, 13, 3, to_date('16.05.2016', 'dd.mm.yyyy'), 20); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (17, 123455, 16, 21, to_date('18.05.2016', 'dd.mm.yyyy'), 50); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (18, 123455, 17, 23, to_date('20.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (19, 123456, 15, 103, to_date('21.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (20, 123456, 19, 104, to_date('22.05.2016', 'dd.mm.yyyy'), 60); commit; create table previous_data as ( select pipes.pipe_no , unit_passes.dt, unit_passes.duration, lag(unit_passes.duration) over (partition by pipes.matid order by unit_passes.dt) as previous_duration from pipes inner join unit_passes on pipes.matid = unit_passes.matid where upper (pipes.pipe_no) like 'итз%' ); select * from previous_data order by unit_passes.dt