create table Operations ("Operator" text, dDate date, tTime time, sOper text);
insert into Operations values
('Иванова', '2020-06-01', '14:29:00', 'Расход'),
('Иванова', '2020-06-01', '14:30:01', 'Расход'),
('Иванова', '2020-06-01', '14:35:17', 'Приход'),
('Иванова', '2020-06-01', '14:30:25', 'Контроль'),
('Петрова', '2020-06-01', '14:35:18', 'Приход'),
('Петрова', '2020-06-01', '14:35:19', 'Приход'),
('Петрова', '2020-06-01', '14:35:20', 'Контроль');
---------------------------------------------------------------------------------------|
select * from Operations;
explain
with t as (
select
*,
row_number() over(partition by "Operator", dDate order by tTime) as rn
from Operations
)
select
a."Operator",
a.dDate,
a.tTime as tOper,
b.tTime as tControl
from t as a, t as b
where a."Operator" = b."Operator"
and a.dDate = b.dDate
and a.rn = b.rn - 1
and b.sOper = 'Контроль';
---------------------------------------------------------------------------------------|
explain
execute 'with t as (
select
*,
row_number() over(partition by "Operator", dDate order by tTime) as rn
from Operations
)
select
a."Operator",
a.dDate,
a.tTime as tOper,
b.tTime as tControl
from t as a, t as b
where a."Operator" = b."Operator"
and a.dDate = b.dDate
and b.sOper = ''' || 'Контроль'
Telegram: DB developers channel