CODEWITHSUNDEEP
sqldatabasepostgresql
Sergey
Sergey

Reputation: 45

How to cross history when joining tables?

There are two tables:

Table1(id, param1, param2, date_from, date_to):

(1, 'z', 55, '01.05.2010 12:30:20', '17.05.2010 13:10:14'),
(1, 'c', null, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

Table2(id, param3, date_from, date_to):

(1, 15, '01.04.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 35, '02.05.2010 13:10:15', '01.01.9999 00:00:00');

It is necessary that when merging 2 tables, there are fields containing the history of changes to each parameter. That is:

Select(id, param1, param2, param3, date_from, date_to):

(1, null, null, 10, '01.04.2010 12:30:20', '01.05.2010 12:30:19'),
(1, 'z', 55, 15, '01.05.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 'z', 55, 35, '02.05.2010 13:10:15', '17.05.2010 13:10:14'),
(1, 'c', null, 35, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, 35, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

Where records from two tables do not overlap in time(or part of time), null should be output .

So far I have done:

SELECT a.id, param1, param2, param3, a.dfrom as afrom, a.dto as ato, b.dfrom as bfrom, b.dto as bto
FROM Table1 a LEFT JOIN Table2 b ON a.id = b.id
UNION
SELECT id, null as param1, null as param2, param3, null as afrom, null as bfrom, dfrom, dto
FROM Table2

And then I do not understand how to act...

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

The idea for solving this is to deconstruct the data into "events" that assign values to the parameters. Then, reconstruct the history using aggregation and window functions.

Because you have legitimate NULL values, this is a little tricky, but I think the following does what you want:

with t as (
      select t1.id, t1.param1, t1.param2, null as param3, t1.date_from, 1 as which
      from table1 t1
      union all
      select t2.id, null as param1, null as param2, t2.param3, t2.date_from, 2 as which
      from table2 t2
     )
select t.id, dfrom,
       lead(dfrom, 1, '9999-01-01'::timestamp) over (partition by id order by dfrom) as dto,
       max(param1) over (partition by param1_grp) as param1,
       max(param2) over (partition by param2_grp) as param2,
       max(param3) over (partition by param3_grp) as param3
from (select id, max(param1) as param1, max(param2) as param2,
             max(param3) as param3, dfrom,
             count(*) filter (where which = 1) over (partition by id order by dfrom) as param1_grp,
             count(*) filter (where which = 1) over (partition by id order by dfrom) as param2_grp,
             count(*) filter (where which = 2) over (partition by id order by dfrom) as param3_grp
      from t
      group by id, dfrom
     ) t

Upvotes: 1

Related Questions