Блог

У меня есть два стола, boxes и things они частично моделируют склад.

Коробка может

• содержать одну вещь
• содержит одну или несколько коробок
• быть пустым

Существует только один уровень вложенности: ящик может быть родителем или ребенком, но не бабушкой и дедушкой.

Я хочу определить родительские поля, которые удовлетворяют этим критериям:

• заводите детей более чем в одном месте
• следует учитывать только дочерние поля, связанные с количеством > 0

Используя данные примера, следует выбрать поле с идентификатором 2, поскольку в нем есть дочерние элементы с количествами в двух местах. Вставка 1 должна быть отклонена, потому что все ее дети находятся в одном месте, а вставка 3 должна быть отклонена, потому что, хотя у нее есть дети в двух местах, только в одном месте есть положительное количество.

Запрос должен работать на всех поддерживаемых версиях Postgresql. Обе таблицы содержат около двух миллионов записей.

Установка:

 DROP TABLE IF EXISTS things;
DROP TABLE IF EXISTS boxes;

CREATE TABLE boxes (
    id serial primary key,
    box_id integer references boxes(id)
);


CREATE TABLE things (
    id serial primary key,
    box_id integer references boxes(id),
    place_id integer,
    quantity integer
);


INSERT INTO boxes (box_id)
  VALUES (NULL), (NULL), (NULL), (1), (1), (2), (2), (3), (3);


INSERT INTO things (box_id, place_id, quantity)
  VALUES (4, 1, 1), (5, 1, 1), (6, 2, 1), (7, 3, 1), (8, 4, 1), (9, 5, 0);
 

Я придумал это решение

 WITH parent_places AS (
    SELECT DISTINCT ON (b.box_id, t.place_id) b.box_id, t.place_id
    FROM boxes b 
    JOIN things t ON b.id = t.box_id
    WHERE t.quantity > 0
)

SELECT box_id, COUNT(box_id)
FROM parent_places
GROUP BY box_id
HAVING COUNT(box_id) > 1;
 

но мне интересно, пропустил ли я более очевидное решение (или в моем решении есть какие-либо ошибки, которые я упустил из виду).

Скрипка БД

Единственный способ, чтобы в коробке были вещи с разными свойствами расположения, — это только тогда, когда в коробке есть несколько коробок с вещами в них.

 SELECT
  b2.box_id, COUNT(DISTINCT place_id)
FROM
  boxes b2
  JOIN things t ON b2.id = t.box_id AND quantity > 0
WHERE
  b2.box_id IS NOT NULL
GROUP BY
  b2.box_id
HAVING 
  COUNT(DISTINCT place_id) > 1;
 

Я не вижу причин для использования CTE, как в вашем примере. Я думаю, вам следует использовать самый простой запрос, который выполняет эту работу.