SQL 左连接行为异常

Question

有人可以指出我在这里做错了什么吗？

我有一张带时间戳的数据表:

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;

据我所知，对于 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 查询影响了 28,237 行； SELECT 查询返回 55561，我认为它应该返回零。

我想 AND d1.id IN (SELECT * FROM source_ids) 有一些东西没有像它应该的那样工作，但是什么？

Answer 1

当使用 LEFT JOIN 时，第一个 表上的过滤器应该在 WHERE 子句中。在 ON 子句中的 second 上过滤。通常，错误发生在第二张表的过滤器上。你的在第一张 table 上的过滤器上。

所以:

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;

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