Блог

Я работаю над археологической базой данных, которая включает в себя пару таблиц, описывающих пространственные отношения между стратиграфическими единицами. Это довольно просто — единица находится либо выше, либо ниже другой единицы. Для этого у меня есть таблица, в которой записаны unit_1, unit_2 и тип пространственных отношений между ними (выше или ниже). Я также хочу сгенерировать представление, в котором также записывается транзитивный аналог. Другими словами, если блок A находится выше блока B, я также хочу временную строку, указывающую, что блок B находится ниже блока A.

Вот как выглядит мой CTE в настоящее время. Я получаю ошибку «ОШИБКА: отношение «matrix_cte» не существует», так что, вероятно, это не тот способ сделать это. Но идея здесь в том, что когда отношение «выше» (которое совпадает с 1), команда INSERT должна добавить новую строку в таблицу, созданную, где две единицы поменялись местами, а отношение находится ниже (или 2). Любая помощь с благодарностью, заранее спасибо.

 WITH matrix_cte  (unit, related_unit, relationship)
AS (SELECT lookup_unit,
lookup_unit_2,
lookup_unit_relationship

FROM register_unit_matrix)
INSERT INTO matrix_cte(unit, related_unit, relationship)
SELECT lookup_unit_2, lookup_unit, 2
FROM (register_unit_matrix

INNER JOIN matrix_cte ON ((register_unit_matrix.lookup_unit = matrix_cte.unit)))
WHERE relationship = 1;
  

Вы не можете INSERT перейти в CTE. CTE — это логическая таблица, это псевдоним результирующего набора. Вы можете SELECT из CTE. Не совсем уверен, чего вы пытаетесь там достичь.

ОШИБКА: отношение «matrix_cte» не существует

Эти сообщения об ошибках означают, что вы можете INSERT использовать только отношения (таблицы). CTE — это не таблица, это не постоянный объект в базе данных, и в вашей базе данных нет таблицы с именем matrix_cte .

Чтобы сгенерировать все связи, как прямые, так и обратные, вы можете UNION объединить два результирующих набора. Если ваша исходная таблица имеет связи только в одном направлении, тогда вы можете использовать UNION ALL , и запрос будет быстрее. Я имею в виду, если в исходной таблице никогда не бывает двух строк для одной и той же пары единиц:

 unit1, unit2, 1
unit2, unit1, 2
  

тогда вы можете использовать UNION ALL приведенное ниже. Если в исходной таблице могут быть такие дубликаты, вам следует использовать UNION для удаления дополнительных дубликатов.

 -- all direct relationships as they are
SELECT 
    lookup_unit,
    lookup_unit_2,
    lookup_unit_relationship
FROM register_unit_matrix

UNION

-- inverse all relationships 
SELECT 
    lookup_unit_2,
    lookup_unit,
    CASE WHEN lookup_unit_relationship = 1 THEN 2 ELSE 1 END AS lookup_unit_relationship
FROM register_unit_matrix
  

Вы можете поместить этот запрос выше в представление или использовать как есть.