sql - 如果子查询在 Postgres 中返回多行，则跳过行

Question

我要更新表 prod_replay_out基于 Postgres 中的子查询结果。但是，子查询返回多行，但我想跳过这些行并根据子查询返回的单行更新表。
我已经提到链接 Subquery returns more than 1 row error但是 max()函数不适用于我的预期结果。您能否为我提供一些修改查询的建议？谢谢你。prod_replay_out有以下列:
卖家、买家、sender_tag、seller_tag、买家标签、isin、数量、in_msg_time、msg_type、cdsx_timeprod_replay_in有以下列:
卖家、买家、sender_tag、seller_tag、买家标签、isin、数量、msg_type、cdsx_time
我尝试过什么？
请在下面找到更新sql:
更新sql:

update prod_replay_out O  
   set in_msg_id = 
        (Select id
           From prod_replay_in I
          Where I.msg_type   = 'CDST010'
            and I.seller     = O.seller
            and I.buyer      = O.buyer
            and I.sender_tag = O.sender_tag
            and I.seller_tag = O.seller_tag
            and I.buyer_tag  = O.buyer_tag
            and I.isin       = O.isin
            and I.quantity   = O.quantity
            and I.cdsx_time  = O.in_msg_time
            and I.cdsx_time::text like '2020-05-12%'
         ) 
where O.msg_type = 'CDST01C'
and O.cdsx_time::text like '2020-05-12%';

我尝试了以下解决方案。这是正确的方法还是有什么漏洞？

update prod_replay_out O  
   set in_msg_id = 
        (Select id
           From prod_replay_in I
          Where I.msg_type   = 'CDST010'
            and I.seller     = O.seller
            and I.buyer      = O.buyer
            and I.sender_tag = O.sender_tag
            and I.seller_tag = O.seller_tag
            and I.buyer_tag  = O.buyer_tag
            and I.isin       = O.isin
            and I.quantity   = O.quantity
            and I.cdsx_time  = O.in_msg_time
            and I.cdsx_time::text like '2020-05-12%'
            and 1 = (Select count(id)
                       From prod_replay_in I
                      Where I.msg_type   = 'CDST010'
                        and I.seller     = O.seller
                        and I.buyer      = O.buyer
                        and I.sender_tag = O.sender_tag
                        and I.seller_tag = O.seller_tag
                        and I.buyer_tag  = O.buyer_tag
                        and I.isin       = O.isin
                        and I.quantity   = O.quantity
                        and I.cdsx_time  = O.in_msg_time
                        and I.cdsx_time::text like '2020-05-12%'
                    )    
                )
where O.msg_type = 'CDST01C'
  and O.cdsx_time::text like '2020-05-12%';

Answer 1

询问
最重要的是，不要使用相关子查询。这是完成这项工作的劣等工具。在 FROM clause 中使用子查询.
这只会更新在源表中找到单个匹配候选行的位置(既不是一个也不是多个)，和 仅在它实际更改值的地方:

UPDATE prod_replay_out o
SET    in_msg_id = i.id
FROM  (
   SELECT i.id, i.seller, i.buyer, i.sender_tag, i.seller_tag, i.buyer_tag, i.isin, i.quantity, i.cdsx_time
   FROM   prod_replay_in i
   WHERE  i.msg_type   = 'CDST010'
   AND    i.cdsx_time >= '2020-05-12'     -- ① "sargable" expression
   AND    i.cdsx_time <  '2020-05-13'     -- ② don't cast to date, it's a valid timestamp literal
   AND    NOT EXISTS (                    -- ③ EXISTS is typically faster than counting
      SELECT FROM prod_replay_in x
      WHERE  x.id <> i.id                 -- ④ unique
      AND   (i.seller, i.buyer, i.sender_tag, i.seller_tag, i.buyer_tag, i.isin, i.quantity, i.cdsx_time)  -- ⑤ short syntax
        =   (x.seller, x.buyer, x.sender_tag, x.seller_tag, x.buyer_tag, x.isin, x.quantity, x.cdsx_time)
      )
   ) i
WHERE  o.msg_type = 'CDST01C'
AND   (i.seller, i.buyer, i.sender_tag, i.seller_tag, i.buyer_tag, i.isin, i.quantity, i.cdsx_time)
  =   (o.seller, o.buyer, o.sender_tag, o.seller_tag, o.buyer_tag, o.isin, o.quantity, o.in_msg_time)  -- ⑥ o.cdsx_time?
-- AND    o.cdsx_time >= '2020-05-12'     -- ⑦ redundant
-- AND    o.cdsx_time <  '2020-05-13'
AND   o.in_msg_id IS DISTINCT FROM i.id   -- ⑧ avoid empty updates
;

① 像 GMB 已经建议的那样，将此谓词转换为“sargable”表达式。这通常更快，并且可以使用索引支持。

What does the word “SARGable” really mean?

Calculate number of concurrent events in SQL

② 但不要投到 date如果 cdsx_time是 timestamp列(似乎很可能)。 '2020-05-12'是一个完全有效的时间戳文字，表示当天的第一个实例。看:

Generating time series between two dates in PostgreSQL

如果是 timestamptz列，考虑 timezone 的可能影响环境!看:

Ignoring time zones altogether in Rails and PostgreSQL

③ EXISTS通常比计算所有行更有效，因为它可以在找到另一行后立即停止。特别是如果可以有很多对等点，并且索引支持可用。看:

Select rows which are not present in other table

④ 假设 id是唯一的(或PK)。否则使用系统栏 ctid为了工作。看:

How do I (or can I) SELECT DISTINCT on multiple columns?

⑤ 方便、等效的带有 ROW 值的简短语法。看:

Enforcing index scan for multicolumn comparison

⑥ 您的查询有:

and I.cdsx_time  = O.in_msg_time         -- !?
and I.cdsx_time::text like '2020-05-12%'

... 但:

O.cdsx_time::text like '2020-05-12%'

你不是故意写 and I.cdsx_time = O.cdsx_time ?
⑦会是噪音。该限制已在子查询中强制执行。 (也无助于索引支持。)
⑧ 如果某些列可能已经具有所需的值，则这一点很重要。然后跳过操作，而不是全成本写入相同的行版本。
如果两列都定义了 NOT NULL ，简化为 o.in_msg_id <> i.id .再看:

Update a column of a table with a column of another table in PostgreSQL

指数
如果性能是一个问题或者您重复运行它，请考虑如下索引:
对于识别源行候选的第一步(按预期查询计划的顺序!):

CREATE INDEX foo ON prod_replay_in (msg_type, cdsx_time); 

对于排除重复的第二步:

CREATE INDEX foo ON prod_replay_in (seller, buyer, sender_tag, seller_tag, buyer_tag, isin, quantity, cdsx_time);

或者任何具有足够选择性的小子集。较少列上的较小索引通常更有效 如果 它在索引扫描中包含相对较少的额外行作为“误报”。虽然相对较少，但在以下 FILTER 中廉价地消除了这些。步。
对于识别目标行的最后一步:

CREATE INDEX foo ON prod_replay_out (msg_type, in_msg_time);

再次:或任何具有足够选择性的小子集。