SQL for pairing rows by value of field

I have a table:

create table transaction_log (
id serial,
operation_type character varying(36),
date timestamp with time zone,
sum double precision,
user_id integer,
PRIMARY KEY(id)
);


It keeps two type of operations: block and unblock for multiple users (contragent_id) and lots (lot_id) and datetime field of operation.

block

unblock

operation

For example:

id, sum, operation_type, date, user_id
1, 5900, blocked, 2018-01-05 11:00, 1
2, 3500, blocked, 2018-01-08 12:00, 2
3, 5900, unblock, 2018-02-11 09:00, 1
4, 1000, blocked, 2018-01-09 05:00, 3
5, 3500, unblock, 2018-01-24 19:00, 2


So I need to get SQL to fetch all block operations with date of corresponding unblock operation, if it exists. E.g.: block_ID, sum, blocked_date, unblock_date. So, from example data I need to get:
For example:

block_ID, sum, blocked_date, unblock_date
1, 5900, 2018-01-05 11:00, 2018-02-11 09:00
2, 3500, 2018-01-08 12:00, 2018-01-24 19:00
4, 1000, blocked, 2018-01-09 05:00, null


I suppose that I need a WITH statement for this, but I can't get it how to match records correctly.

Any help appreciated.

BTW Postgres 9.4

2 Answers
2

You can try below way

with block as
(
select * from transactions
where operation='blocked'
),
unblock as
(
select * from transactions
where operation='unblock'
)
select block.id as block_ID, block.sum,
block.date, unblock.date from block
left join unblock on block.user_id=unblock.user_id


If your data is consistent, you are merely looking for the minimum unblock date following a block date. You can get this in a subquery in your SELECT clause:

SELECT

select user_id, sum, date as block,
(
select min(ub.date)
from blocktable ub
where ub.operation_type = 'unblock'
and ub.user_id = b.user_id
and ub.date >= b.date
) as unblock
from blocktable b
where operation_type = 'blocked';


Or in the FROM clause with a lateral join:

FROM

select b.user_id, b.sum, b.date as block, ub.unblock
from blocktable b
left join lateral
(
select min(ub.date) as unblock
from blocktable ub
where ub.operation_type = 'unblock'
and ub.user_id = b.user_id
and ub.date >= b.date
) b
where operation_type = 'blocked';


With a lateral join can even get the whole row:

select *
from blocktable b
left join lateral
(
select *
from blocktable ub
where ub.operation_type = 'unblock'
and ub.user_id = b.user_id
and ub.date >= b.date
order by ub.date
fetch first 1 row only
) as unblock
where operation_type = 'blocked';


Another option to get the single date would be LEAD:

LEAD

select user_id, sum, block, unblock
from
(
select
user_id,
sum,
date as block,
lead(date) over (partition by user_id order by date, operation_type) as unblock,
operation_type
from mytable
) block_and_unblock
where operation_type = 'blocked';


If you want a query that works great even in case of inconsistencies (especially two blocked or two unblocked records following each other), you may have to select the data sequentially, i.e. in a recursive query or with an app.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.