时间戳

首页 > 解决方案 > PostgreSQL：合并两个表中的记录并计算平均值

sql - PostgreSQL：合并两个表中的记录并计算平均值

问题描述

我有两个表，table_1 和 table_2。我想将 table_1 中的一些记录（具有特定 ID）与 table_2 的一些记录（具有特定 ID）合并。

例如：我想将 table_1 中 ID 为“AA”的所有记录与 table_2 中 ID 为“HH”的记录合并。但是ID“HH”不应该改变，但是值列Val1和Val2应该通过计算平均值来合并。此外，只有来自 'AA' 和 'HH' 的记录应该合并，它们共享相同的时间戳。这样 table_2 在执行查询后看起来像这样。

这是示例：

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

请，如果您有一些问题需要更好地理解，请随时发表评论。我将编辑问题以便更好地理解。

标签： sqlpostgresqljoin

解决方案

要查询所需信息，请尝试：

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

为了Table2基于更新Table1，请尝试：

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

推荐阅读