4

I have two tables with two columns keeping tabs of categorical values, such as:

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

These Begin and End columns represent a continuous interval in which the value is valid. In the above example, the interval would be days in a month, so according to Table 1 I'd have a normal condition from day 1 to day 8, critical from day 8 to day 23, and normal again from the 23rd to the 30th. And I know from Table 2 that John was supervising from the 1st to the 14th, and Janice took over from the 14th to the 30th.

What I want is to merge these two tables, to have both Condition and Supervisor values in the same table, with the minimal interval for each pairing of values. So, this:

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

What can be guaranteed of the tables is that:

  • each will have its respective "Begin" and "End" fields.
  • those will have the same span (the "Begin" value of the first row and the "End" value of the last row are the same for every table).
  • that every interval is sequential (the "End" value of row n is always equal to the "Begin" value of row n+1).
  • that the value of "End" will always be higher than "Begin" for a given row.

I had this worked out programatically in python, but I'm trying to scrap that script from my workflow and do everything directly in the DB. Ultimately I could replicate my python function in plpgsql, but I wonder if there is a more SQL-esque way of achieving this?

  • DDL statements that allow us to re-create this would be helpful. – Max Vernon Mar 9 '18 at 19:27
  • 2
    See Darwen, Date & Lorentzos' book (now 2nd edition) on temporal databases. (Or Lorentzos's paper.) (Avoid Snodgrass's temporal work.) It's really a tutorial explanation, so there's not so much content as the page count would suggest. The keys are 6NF & interval NFs. However, although they explain the (elegant) semantics in terms of unpacking & packing to & from every point within an interval, which is how JackDouglas's answer here implements, it's possible to describe & implement using only the intervals that partition the timeline per input start & end points. More complex but fewer rows. – philipxy Mar 12 '18 at 22:59
3

I'd approach this in three logical stages:

  1. Expand the rows in both tables for all the days in the month and join them together.
  2. Assign a 'group' to each series of rows where condition and supervisor don't change for a period (a "gaps and islands" problem).
  3. Group the results.

So the solution looks like this:

create table t1(
  id serial primary key
, begin_on integer
, end_on integer
, condition text
);

insert into t1(begin_on,end_on,condition)
values (1,8,'Normal')
     , (8,23,'Critical')
     , (23,30,'Normal');

create table t2(
  id serial primary key
, begin_on integer
, end_on integer
, supervisor text
);

insert into t2(begin_on,end_on,supervisor)
values (1,14,'John')
     , (14,30,'Janice');
select min(g) begin_on, max(g)+1 end_on, condition, supervisor
from( select g
           , condition
           , supervisor
           , row_number() over (order by g) 
             - row_number() over (partition by t1.id, t2.id order by g) grp
      from generate_series(1,30) g
           join t1 on g>=t1.begin_on and g<t1.end_on
           join t2 on g>=t2.begin_on and g<t2.end_on ) z
group by condition, supervisor, grp
order by begin_on;
begin_on | end_on | condition | supervisor
-------: | -----: | :-------- | :---------
       1 |      8 | Normal    | John      
       8 |     14 | Critical  | John      
      14 |     23 | Critical  | Janice    
      23 |     30 | Normal    | Janice    

dbfiddle here

I am assuming this is a cut-down example of your real-world problem or I would also suggest changing the way you store the data in the first place, perhaps using date ranges instead of integers.

  • Sorry for such a late response (shortly after I posted the question, was sent on a trip unable to look it up). Your answer works wonders! Yes, this was a cut-down example, I had to modify my data slightly to fit this MO, but it works very well, and very fast! Thanks a lot! – Roberto Ribeiro Mar 28 '18 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.