CODEWITHSUNDEEP
sqlpostgresql
Dasshield
Dasshield

Reputation: 167

Postgresql join on same table

I have a table looking like a block history:

id  user_id admin_id    when_blocked    block   reason
1   1       4           05.05.17        TRUE    flood
2   1       4           06.05.17        FALSE   
3   1       4           07.05.17        TRUE    flood
4   1       4           08.05.17        FALSE   
5   1       5           09.05.17        TRUE    cheating

Where block column means block action (TRUE as block, FALSE as unblock). New block record may appear, only if previously user was unblocked. Therefore, records with same user_id always alternate between TRUE and FALSE.

The problem:

I want to get table looking like:

user_id admin_id    when_blocked    reason   when_unblocked
1       4           05.05.17        flood    06.05.17       
1       4           07.05.17        flood    08.05.17     
1       5           09.05.17        cheating null

Is there a possibility to implement this resulting table?

Thanks.

Upvotes: 1

Views: 101

Answers (3)

Kjetil S.
Kjetil S.

Reputation: 3777

I like Gordon Linoffs answer with lead, and it might be faster if the table has very many rows, but I think this select is easier to wrap one's head around:

select user_id, admin_id, when_blocked, reason,
  ( select min(when_blocked)
    from history
    where not block
    and user_id = h.user_id
    and when_blocked >= h.when_blocked ) when_unblocked
from history h
where block
order by when_blocked, user_id;

Column when_blocked should be of type date or timestamp for >= to work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you just want lead(), but a subquery is also needed:

select user_id, admin_id, when_blocked, reason, next_when_blocked as when_unblocked
from (select bh.*,
             lead(when_blocked) over (partition by user_id, admin_id order by id) as next_when_blocked
      from block_history bh
     ) bh
where block;

This assumes that the values are interleaved -- as in your example and as implied by your explanation.

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

https://www.db-fiddle.com/f/6N1EaoeAmegfdvgg9ELDHn/0

select distinct on (coalesce(unblocked_id, id))
    user_id, admin_id, when_blocked, reason, when_unblocked
from
    (select * from history where block) b
    left join
    (
        select id as unblocked_id, when_blocked as when_unblocked
        from history
        where not block
    ) nb on b.id < nb.unblocked_id
order by coalesce(unblocked_id, id), id
;
 user_id | admin_id | when_blocked |  reason  | when_unblocked 
---------+----------+--------------+----------+----------------
       1 |        4 | 2017-05-05   | flood    | 2017-05-06
       1 |        4 | 2017-05-05   | flood    | 2017-05-08
       1 |        5 | 2017-05-09   | cheating |

Upvotes: 1

Related Questions