postgresql - 在PostgreSQL中将值从一行复制到另一行

我有一张这样的桌子：

id   product   amount
1    A         6
1    A         8
1    A
1    B         1
1    B
2    C         2
2    C
2    C         4
2    C
2    C

我需要这样做：

id   product   amount
1    A         6
1    A         8
1    A         8
1    B         1
1    B         1
2    C         2
2    C         2
2    C         4
2    C         4
2    C         4

按上一个非缺失值复制金额。
我试图使用lag()函数。但是，在更新中不允许聚合函数lag()。

update tableA set amount = lag(amount);

我可以用PostgreSQL做什么？

最佳答案

您可以SELECT您想要的UPDATE，但实际上没有（简单的）方法来执行UPDATE，因为表fox还没有主键。

CREATE TABLE fox (
    id integer NOT NULL,
    product text NOT NULL,
    amount integer
);

给狐狸填充一些数据。

INSERT INTO fox VALUES
(1, 'A', 6),
(1, 'A', 8),
(1, 'A', NULL),
(1, 'B', 1),
(1, 'B', NULL),
(2, 'C', 2),
(2, 'C', NULL),
(2, 'C', 4),
(2, 'C', NULL),
(2, 'C', NULL),
(3, 'What does the fox say?', 5);

询问。

WITH ranks (rank, id, product, amount) AS (
  SELECT ROW_NUMBER() OVER (), id, product, amount FROM foo
)
SELECT r.id, r.product,
  (SELECT amount FROM ranks
    WHERE id = r.id AND product = r.product
      AND rank < r.rank AND amount IS NOT NULL
    ORDER BY amount DESC LIMIT 1
  )
FROM ranks r WHERE r.amount IS NULL ORDER BY 1, 2, 3;

生成以前有NULL的行，现在有适当的amount的行。

 id | product | amount
----+---------+--------
  1 | A       |      8
  1 | B       |      1
  2 | C       |      2
  2 | C       |      4
  2 | C       |      4

但是您不能使用此数据进行更新，因为行仍然不是由(id, product)唯一标识的，这意味着您不能编写一个WHERE条件来唯一标识行。WHERE子句如何知道将UPDATE中的金额更改为2还是4？(id, product) = (2, 'C')的多行在WHERE的UPDATE中无法区分。
让我们给狐狸一把主键。

ALTER TABLE fox ADD COLUMN IF NOT EXISTS pkey serial ;
ALTER TABLE fox ADD PRIMARY KEY (pkey) ;

现在我们可以通过PRIMARY KEY pkey来识别行。

WITH nulls AS (
  SELECT pkey, id, product
  FROM fox
  WHERE amount IS NULL
)
SELECT pkey,
  id, product,  -- you can leave these out in your UPDATE: pkey is UNIQUE
  (SELECT amount FROM fox
   WHERE id = n.id AND product = n.product
     AND n.pkey > pkey AND amount IS NOT NULL
   ORDER BY pkey DESC LIMIT 1)
FROM nulls n ORDER BY 1, 2, 3, 4;

显示要进行的更改

 pkey | id | product | amount
------+----+---------+--------
    3 |  1 | A       |      8
    5 |  1 | B       |      1
    7 |  2 | C       |      2
    9 |  2 | C       |      4
   10 |  2 | C       |      4

我们可以在pkey中使用UPDATE。

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

WITH nulls AS (
      SELECT pkey, id, product
      FROM fox
      WHERE amount IS NULL
    ), changes AS (
    SELECT pkey,
      (SELECT amount FROM fox
       WHERE id = n.id AND product = n.product
         AND n.pkey > pkey AND amount IS NOT NULL
       ORDER BY pkey DESC LIMIT 1)
    FROM nulls n
) UPDATE fox f SET amount = c.amount FROM changes c WHERE f.pkey = c.pkey ;

检查结果是否正常：

SELECT * FROM fox ORDER BY 1, 2, 3, 4;

并相应地接受使用COMMIT或ROLLBACK。
添加主键的替代方法
每个表都应该有一个主键。
如果您坚持不使用一个，那么您还可以使用它们当时不为空的数量来计算行，而不是将它们放入表中，然后删除那些没有数量的行。这样就可以绕过添加主键，这是唯一的。当然UPDATE和INSERT被打包成DELETE FROM fox WHERE amount IS NULL这样就不会干扰其他并发运行的事务。例如，在使用UPDATE计算要DELETE编辑的数据之后和TRANSACTION全部NULLs之前添加INSERT行的另一个事务。在这种情况下，您将错过同时添加的SELECT行（并发导致的数据丢失；请考虑ACID）。
但是丢失的主键可能会在以后咬你。

关于postgresql - 在PostgreSQL中将值从一行复制到另一行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44067366/