CODEWITHSUNDEEP
sqlpostgresqljoin
user7335295
user7335295

Reputation: 441

PostgreSQL: Merge records from two tables and calculate the average

I have two tables, table_1 and table_2. I want to merge some records from table_1 (with a specific ID) with some records of table_2 (with a specific ID).

For example: I want to merge all records from table_1 with the ID "AA" with the records of table_2 which have the ID "HH". But the ID "HH" should not change, but the value columns Val1 AND Val2 should be merged by calculating the average. Furthermore, only those records from 'AA' and 'HH' should be merged which share the same timestamp. So that table_2 looks like this after the query is executed.

Here's the example:

Table_1:
ID |     Timestamp    | Val1 | Val2
-----------------------------------
AA | 2018-14-07 00:00 | 203  | 294
BB | 2018-14-07 00:00 | 193  | 194
CC | 2018-14-07 00:00 | 193  | 136
AA | 2018-14-07 00:15 | 194  | 198
BB | 2018-14-07 00:15 | 124  | 594
CC | 2018-14-07 00:15 | 105  | 110

Table_2:
ID |     Timestamp    | Val1 | Val2
-----------------------------------
HH | 2018-14-07 00:00 | 123  | 311  
GG | 2018-14-07 00:00 | 156  | 202 
HH | 2018-14-07 00:15 | 200  | 502  
HH | 2018-14-07 00:30 | 303  | 198 
PP | 2018-14-07 00:00 | 111  | 123 

Table_2 (after executing the query):
ID |     Timestamp    | Val1 | Val2
-----------------------------------
HH | 2018-14-07 00:00 | 163  | 302  // Val1: (123 + 203)/2 = 163; Val2: (311+294)/2 = 302
GG | 2018-14-07 00:00 | 156  | 202 
HH | 2018-14-07 00:15 | 197  | 350  // Val1: (200 + 194)/2 = 197; Val2: (502+198)/2 = 350
HH | 2018-14-07 00:30 | 303  | 198  // values stay the same because no record with the ID "AA" and the timestamp "2018-14-07 00:30" is available.
PP | 2018-14-07 00:00 | 111  | 123

Please, if you have some questions for better understanding, don't hesitate to comment. I will edit the question for better understanding then.

Upvotes: 1

Views: 79

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37430

To query required information, please try:

select T2.ID,
       T2.Timestamp, 
       (T1.Val1 + T2.Val1)/2 Val1,
       (T1.Val2 + T2.Val2)/2 Val2
from Table2 T2 left join (
    select case ID when 'AA' then 'HH' end ID,
           Timestamp,
           Val1,
           Val2
    from Table1
) T1 on T2.ID = T1.ID and T2.Timestamp = T1.Timestamp

In order to update Table2 based on Table1, try:

update Table2 as T2 set Val1 = (coalesce(T1.Value1, Val1) + Val1)/2,
                        Val2 = (coalesce(T1.Value2, Val2) + Val2)/2
from (
    select case ID when 'AA' then 'HH' else ID end ID,
           Timestamp,
           Val1 AS Value1,
           Val2 AS Value2
    from Table1
) T1 where T2.ID = T1.ID and T2.Timestamp = T1.Timestamp

Upvotes: 1

Ajay Gupta
Ajay Gupta

Reputation: 1855

Try this

Select t2.ID, t2.Timestam, 
       Case when t1.Val1 is null Then t2.Val1 ELSE (T1.Val1 + T2.Val1)/2 END Val1,
       Case when t1.Val2 is null Then t2.Val2 ELSE (T1.Val2 + T2.Val2)/2 END Val2 
from #tbl2 t2
left join #tbl1 t1
on t1.Timestam = t2.Timestam
and t1.ID = 'AA' and t2.ID = 'HH'

Upvotes: 0

Related Questions