在 postgreSQL 中根据另一个表中的最高记录值更新一个表

只要您有足够的索引table1：

create index on table1(id,record);

甚至覆盖索引：

create index on table1(id,record)
   include(name,data1,data2,data3)
   with(fillfactor=100);--only if it's also static or low-traffic

您可以使用子选择UPDATE语法来获得高性能：它只会从索引顶部弹出新值。
_{演示在 db<>fiddle}

update table2
set (record,data1,data2,data3,name)
  = ( select record,data1,data2,data3,name 
      from table1
      where table1.id=table2.id
      order by record desc
      limit 1);

在此 250k 行的测试中，它花费的时间大约为，与目前显示的使用、和32ms的替代方案相比，减少了 10 倍多。distinct onmax(record)row_number()over()

如果您不想知道列名但可以信任table1和table2匹配的结构，则可以运行重新插入：

with cte as(delete from table2
--remove this `where` if you don't want to accumulate erased `table1.id`s
            where id in(select id from table1))
insert into table2 
select*from table1
where(id,record)in(select id,max(record)
                   from table1
                   group by id);

而且它仍然可以使用索引，在大约 160 毫秒内以大约 2 倍的速度完成。
如果您怀疑这会擦除并重写整个内容，从而导致某种效率低下，请不要担心。在Postgres MVCC中，每个update实际上都是delete+ insert。

实现此目的的最佳方法是使用利用的 cte distinct on。类似以下内容：

with cte as 
(SELECT DISTINCT ON (id) id, record, name, data1, data2, data3
from table1 ORDER BY id, record desc)
UPDATE table2 t
SET record = cte.record, data1 = cte.data1, data2 = cte.data2, data3 = cte.data3
FROM cte 
WHERE cte.id = t.id;

它distinct on可以让你将结果集限制为每列（或多列）一行。order by必须以 中的列开头的 决定了从剩余数据中返回哪一行。 因此，按记录降序排序与获取具有该 ID 的distinct on的行相同。MAX(record)

只需update将 cte 连接到目标表并相应地更新值。

恐怕我不知道有捷径，这意味着您不需要指定每一列！

为方便将来参考，请通过插入语句提供示例数据。这样可以节省我们的测试工作量。

我们获取 CTE（hgh）中每个 ID 的最大记录，并在相关的 UPDATE 语句中使用该记录来更新相应的列：

WITH hgh AS (
    SELECT  ID, MAX (Record) AS max_record
    FROM    Table1
    GROUP BY ID
)
UPDATE   Table2 t2
  SET Data1 = t1.Data1,
      Data2 = t1.Data2,
      Data3 = t1.Data3,
      Record = t1.Record
FROM hgh h
   JOIN Table1 t1
       ON (   t1.ID = h.ID
          AND t1.record = h.max_record
          )
WHERE  t2.ID = t1.ID

您可以使用 row_number 窗口函数来获取每个 id 的最新记录，如下所示：

with cte as (
 select * , row_number() over (partition by id order by record desc) as rn
  from table1
)

select * from cte where rn = 1

根据您想要更新 table2 的频率，可能每次只需截断 table2 并在其中插入这些值。

小提琴