时间戳

首页 > 解决方案 > 如果子查询在 Postgres 中返回多行，则跳过行

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

问题描述

我想prod_replay_out根据 Postgres 中的子查询结果更新表。但是，子查询返回多行，但我想跳过这些行并根据子查询返回的单行更新表。

我已经提到链接子查询返回超过 1 行错误，但max()函数不适用于我的预期结果。你能给我一些修改查询的建议吗？谢谢你。

prod_replay_out有以下列：

卖家、买家、sender_tag、seller_tag、buyer_tag、isin、数量、in_msg_time、msg_type、cdsx_time

prod_replay_in有以下列：

卖家、买家、sender_tag、seller_tag、buyer_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%';

标签： sqlpostgresqlselectsql-updatesubquery

解决方案

询问

最重要的是，不要使用相关子查询。这是这项工作的劣质工具。在FROM子句中使用子查询。

这仅更新在源表中找到单个匹配候选行的位置（既没有也没有多个），并且仅更新实际更改值的位置：

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”表达式。这通常更快，并且可以使用索引支持。

②但不要转换为dateif cdsx_timeis a timestampcolumn（看起来很可能）。'2020-05-12'是一个完全有效的时间戳文字，表示当天的第一个实例。看：

如果是timestamptz列，请考虑设置的可能影响timezone！看：

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

④ 假设id是唯一的（或PK）。否则将系统列ctid用于作业。看：

⑤ 方便的、与 ROW 值等效的短句法。看：

⑥ 您的查询有：

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。再次，请参阅：

指数

如果性能是一个问题或者您重复运行它，请考虑如下索引：

对于识别源行候选的第一步（按预期查询计划的顺序！）：

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);

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

推荐阅读