PostgreSQL – How to Select Events Based on Adjacent Times

postgresqlquerysubquery

I have a table rooms which contains information regarding events that take in rooms. This is the definition of the table:

create table room(
  sdate date,--start date
  stime time,--start time
  rno int,--room no
  bno int,--building no
  eid int,--event id
  duration interval,
  primary key(sdate, stime, rno, bno, eid)
);

I want to find all events that are adjacent to one another, that is if event x finishes at 10:00 and event y starts at 10:00 (provided they're in the same room, building and date of course) these events are adjacent in terms of time.

I'm new to SQL and I wrote a query to find such events using 5 subqueries and one union which seems to me to be over the top. I'd like to learn the best practices therefore is there a better, more concise way to perform such a query?

This is the query:

with
  --calculate end times (etime)
  schedule_w_etime(sdate, stime, rno, bno, etime) as (
    select sdate, stime, rno, bno, (stime + duration) as etime from schedule
  ),
  --find adjacent events
  result(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S
    where S.etime in (select stime from schedule_w_etime as T where S.sdate=T.sdate
                                                                    and S.rno=T.rno
                                                                    and S.bno=T.bno)
  ),
  --get the last event from result
  max_time(date, start_time, room_no, build_no, end_time) as (
    select * from result
    where etime=(select max(etime) from result)
  ),
  --lookup the start time of the last event
  lookup_max_time(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S, max_time as M
    where S.stime=M.end_time and S.bno=M.build_no and S.rno=M.room_no
  ),
  --format lookup_max_time to only relevant columns
  last_event_final(sdate, stime, rno, bno) as (
    select sdate, stime, rno, bno from lookup_max_time
  )
  --union of the last event and all the other events from result
  (select * from last_event_final)
    union
  (select sdate, stime, rno, bno from result)

As you can see my main problem is with displaying the last event because in the result view doesn't contain the start time of the last event.

Best Answer

You may simply do a self-join, like:

SELECT r1.sdate, r1.rno, r1.bno, r1.eid,
       r2.* -- choose the r2 fields that are needed
FROM room r1 JOIN room r2
 ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
   and r1.stime + r1.duration = r2.stime);

This answers the query: what are the adjacent events?, by returning event x and event y where y follows x, as a relationship.

The proposed SQL statement in your question answers a question that seems slightly different, like: which events are involved in an adjacency relationship? The structure of the result in this case would be one row per such event, then.

One way of getting that is to build it on top of the above query and extract the r1's and r2's in separate rows, UNIONing the result (including the deduplication that UNION does). For example:

WITH list AS (
  SELECT r1.sdate, r1.rno, ...
         r2.sdate AS r2_sdate, r2.rno AS r2_rno, ...
  FROM room r1 JOIN room r2
   ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
     and r1.stime + r1.duration = r2.stime)
)
SELECT sdate, rno, ... FROM list
UNION
SELECT r2_sdate, r2_rno, ... FROM list
Related Question