我有一个成员表,里面有一百万条记录。主键是id。该表中的一列是master_location_id,它引用master_location表中的id列,该表有41481条记录。成员表中的master_location_id尚未填充。我正在使用匿名块填充它。它将从成员表中获取所有记录,然后循环,从随机中获取一条随机记录(select id from master_location order by random() limit 1 ),然后更新成员表。但它的效率非常低。使用SQL语句可以做到这一点吗?PostgreSQL版本为13.2。
发布于 2021-04-23 19:45:24
您可以使用row_number()。假设主位置比成员多,您可以使用:
update members m
set master_location_id = ml.id
from (select m.*, row_number() over (order by id) as seqnum
from members m
) mm join
(select ml.*, row_number() over (order by random()) as seqnum
from master_locations ml
) ml
on ml.seqnum = mm.seqnum
where mm.id = m.id;请注意,更新一百万行将需要一些时间。
此外,如果位置少于成员,则可以很容易地调整这一点--对JOIN条件使用模算法。这将是对查询的调整。
编辑:
分配多个位置的调整是:
update members m
set master_location_id = ml.id
from (select m.*, row_number() over (order by id) as seqnum
from members m
) mm join
(select ml.*,
row_number() over (order by random()) as seqnum,
count(*) over () as cnt
from master_locations ml
) ml
on ml.seqnum = mm.seqnum % ml.cnt
where mm.id = m.id;https://stackoverflow.com/questions/67229296
复制相似问题