SQL Left Join ведет себя странно

Может кто-нибудь указать, что я здесь делаю неправильно?

У меня есть таблица данных с метками времени:

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;

Насколько я вижу, это должно создать строку с идентификатором ab3b516e-acd7-11e7-a0db-f23c91e2b423 для каждой метки времени в строке с любым из идентификаторов в 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) работает не так, как должно, но что?


postgresql sql postgresql-9.5
person Tom    schedule 09.10.2017    source источник
comment
Переместите пункт AND d1.id IN (SELECT * FROM source_ids) AND d2.id = 'ab3b516e-acd7-11e7-a0db-f23c91e2b423' WHERE d2.t IS NULL; в пункт WHERE.   -  person Lukasz Szozda    schedule 09.10.2017

Ответы (1)


arrow_upward
2
arrow_downward

При использовании LEFT JOIN фильтры для первой таблицы должны находиться в предложении WHERE. Фильтрует по секунде в предложении ON. Обычно ошибка делается на фильтрах второй таблицы. Ваш находится на фильтрах на первом столе.

So:

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;

В зависимости от того, как настроены данные и индексы, исходная версия может работать быстрее.

person Gordon Linoff    schedule 09.10.2017