使用self中的值更新PostgreSQL表

发布时间：2020-12-13 15:50:20 所属栏目：百科来源：网络整理

导读：我试图用同一个表中另一行的值更新表上的多个列： CREATE TEMP TABLE person ( pid INT,name VARCHAR(40),dob DATE,younger_sibling_name VARCHAR(40),younger_sibling_dob DATE);INSERT INTO person VALUES (pid,name,dob)(1,'John','1980-01-05'),(2,'Jimm

我试图用同一个表中另一行的值更新表上的多个列：

CREATE TEMP TABLE person (
  pid INT,name VARCHAR(40),dob DATE,younger_sibling_name VARCHAR(40),younger_sibling_dob DATE
);

INSERT INTO person VALUES (pid,name,dob)
(1,'John','1980-01-05'),(2,'Jimmy','1975-04-25'),(3,'Sarah','2004-02-10'),(4,'Frank','1934-12-12');

任务是将younger_sibling_name和younger_sibling_dob填充为年龄最接近他们的人的姓名和出生日,但不是年龄或年龄相同.

我可以轻松地设置较小的兄弟姐妹dob,因为这是确定与相关子查询一起使用的记录的值(我认为这是一个例子吗？)：

UPDATE person SET younger_sibling_dob=(
SELECT MAX(dob)
FROM person AS sibling
WHERE sibling.dob < person.dob);

我只是看不出有什么办法得到这个名字？
对于每个MAX选择,对此的实际查询将以100-500的组运行大约1M个记录,因此性能是一个问题.

编辑：

在尝试了许多不同的方法之后,我已经决定了这个我觉得很好的方法
能够用中间结果验证数据的平衡表明了
意图是什么逻辑,并充分执行：

WITH sibling AS (
  SELECT person.pid,sibling.dob,sibling.name,row_number() OVER (PARTITION BY person.pid
                            ORDER BY sibling.dob DESC) AS age_closeness
  FROM person
  JOIN person AS sibling ON sibling.dob < person.dob
)
UPDATE person
  SET younger_sibling_name = sibling.name,younger_sibling_dob  = sibling.dob
FROM sibling
WHERE person.pid = sibling.pid
   AND sibling.age_closeness = 1;

SELECT * FROM person ORDER BY dob;

解决方法

相关的子查询因糟糕的表现而臭名昭着.小桌子无关紧要,对于大桌来说很重要.使用其中一个,最好是第二个：

查询1

WITH cte AS (
   SELECT *,dense_rank() OVER (ORDER BY dob) AS drk
   FROM   person
    )
UPDATE person p
SET    younger_sibling_name = y.name,younger_sibling_dob  = y.dob
FROM   cte x
JOIN   (SELECT DISTINCT ON (drk) * FROM cte) y ON y.drk = x.drk + 1
WHERE  x.pid = p.pid;

-> SQLfiddle (with extended test case)

>在CTE cte中使用窗口函数dense_rank()根据每个人的掺杂得到无间隙的等级.
>加入cte到自己,但从第二个实例删除dob上的重复项.从而每个人都得到一个更新.如果不止一个人共享相同的dop,则选择相同的一个作为下一个dob上所有人的年轻兄弟.我这样做：

(SELECT DISTINCT ON (rnk) * FROM cte)

添加ORDER BY rnk,…如果你想为每个dob选择一个特定的人.
>如果不存在年轻人,则不会发生UPDATE且列保持为NULL.
> dob和pid的指标使这一点变得快速.

查询2

WITH cte AS (
   SELECT dob,min(name) AS name,row_number() OVER (ORDER BY dob) rn
   FROM   person p
   GROUP  BY dob
   )
UPDATE person p
SET    younger_sibling_name = y.name,younger_sibling_dob  = y.dob
FROM   cte x
JOIN   cte y ON y.rn = x.rn + 1
WHERE  x.dob = p.dob;

-> SQLfiddle

>这是有效的,因为在窗函数之前应用了聚合函数.它应该非常快,因为两个操作都对排序顺序达成一致.>不需要像查询1那样的后续DISTINCT.>结果与查询1完全相同.同样,您可以向ORDER BY添加更多列,以便为每个dob选择一个特定的人.>只需要dob上的索引即可.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!