代码之家 › 专栏 › 技术社区 › Tom

SQL左连接行为异常

postgresql-9.5 postgresql sql

Tom · 技术社区 · 6 年前

有人能指出我做错了什么吗?

CREATE TABLE data_record (
    id UUID,
    t  TIMESTAMP,
    d  INTEGER,
    PRIMARY KEY(id, t)
);

我从该表中的一些项目中获得了一些数据,现在我试图创建一个批处理过程来维护一个项目,其中数据值( d )是其他项目的总和。首先,我尝试为其他项目有行的所有时间戳插入摘要项目的行:

WITH source_ids AS (
    SELECT UNNEST(ARRAY['1e77b896-9e1b-11e7-a0db-f23c91e2b423'::uuid, '7dd37dd0-9e1a-11e7-a0db-f23c91e2b423'::uuid])
)
INSERT INTO data_record (id, t) (
     SELECT DISTINCT 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid, d1.t
       FROM data_record d1
  LEFT JOIN data_record d2
         ON d1.t = d2.t
        AND d1.id IN (SELECT * FROM source_ids)
        AND d2.id = 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid
      WHERE d2.t IS NULL;

据我所知,这应该会创建一个id为的行 ab3b516e-acd7-11e7-a0db-f23c91e2b423 对于一行中具有以下任一ID的每个时间戳: source_ids

WITH source_ids AS (
    SELECT UNNEST(ARRAY['1e77b896-9e1b-11e7-a0db-f23c91e2b423'::uuid, '7dd37dd0-9e1a-11e7-a0db-f23c91e2b423'::uuid])
)
   SELECT COUNT(d1.t)
     FROM data_record d1
LEFT JOIN data_record d2
       ON d1.t = d2.t
      AND d1.id IN (SELECT * FROM source_ids)
      AND d2.id = 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'
    WHERE d2.t IS NULL;

INSERT 受查询影响的28237行;SELECT查询返回55561,我认为它应该返回零。

我想这是因为 AND d1.id IN (SELECT * FROM source_ids) 这看起来并没有起到应有的作用,但又怎么样?

1 回复 | 直到 6 年前

Gordon Linoff 6 年前

使用时 LEFT JOIN 第一表应位于 WHERE 条款上的过滤器第二 ON 条款通常,第二张表上的过滤器会出错。你的在第一张桌子上的过滤器上。

因此:

WITH source_ids AS (
    SELECT UNNEST(ARRAY['1e77b896-9e1b-11e7-a0db-f23c91e2b423'::uuid, '7dd37dd0-9e1a-11e7-a0db-f23c91e2b423'::uuid])
)
INSERT INTO data_record (id, t) (
     SELECT DISTINCT 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid, d1.t
     FROM data_record d1 LEFT JOIN data_record
          d2
          ON d1.t = d2.t AND
             d2.id = 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid
      WHERE d2.t IS NULL AND d1.id IN (SELECT * FROM source_ids);

在我看来,这仍然过于复杂。我可以建议:

WITH . . . 
SELECT DISTINCT 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid, dr.t
FROM (SELECT dr.*,
             COUNT(*) FILTER (WHERE dr.id = 'ab3b516e-acd7-11e7-a0db-f23c91e2b423'::uuid) OVER (PARTITION BY t) as cnt
      FROM data_record dr
     ) dr
WHERE cnt = 0;

根据数据和索引的设置方式,原始版本可能会更快。