Postgresql join on same table

Question

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.

It's more of an aggregation than a join, but the main question is how to determine which rows relate to each other. What are the definitive rules that mean rows 1, 2, 3, 4 don't all merge together, and in-fact are grouped as 1&2, 3&4? Does block = TRUE always precede block = FALSE in date order? Can you ever get TRUE, TRUE, FALSE or FALSE, FALSE, FALSE? Or is it always nice and perfectly clean, always starting with TRUE and then always alternating? — MatBailie
– MatBailie, Commented Jan 24, 2018 at 13:49

Gordon Linoff · Accepted Answer · 2018-01-24 14:59:00Z

4

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.

edited Jan 24, 2018 at 14:59

answered Jan 24, 2018 at 13:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Juan Carlos Oropeza Over a year ago

I think you also need include admin_id in the partition.

MatBailie Over a year ago

I would order by when_blocked rather than id... Or am I overly pessimistic, or just mistaken? (Defining previous and next by time ordering rather than surrogate key ordering...)

MatBailie Over a year ago

@JuanCarlosOropeza - Reading the OP again, I can imagine possible cases where AdminX blocks a user then AdminY unblocks them. Also, the notion of an alternating sequence should hold (according to the op) for the duration of a user, adding admin_id should be superfluous?

Juan Carlos Oropeza Over a year ago

@MatBailie For how is build the data I dont think make any difference on this case

Gordon Linoff Over a year ago

@MatBailie . . . You would be trusting that when_blocked is actually stored in a native date/time format, even though the column values could easily be strings.

Clodoaldo Neto · Accepted Answer · 2018-01-24 14:16:00Z

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 |

Kjetil S. · Accepted Answer · 2018-01-24 15:32:10Z

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.

Collectives™ on Stack Overflow

Postgresql join on same table

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related