Сохранить общий запрос как столбец?

Question

Используя PostgreSQL, у меня есть несколько запросов, которые выглядят так:

SELECT <col 1>, <col 2>
     , (SELECT sum(<col x>)
        FROM   <otherTable> 
        WHERE  <other table foreignkeyCol>=<this table keycol>) AS <col 3>
FROM   <tbl>

Учитывая, что суб-выбор будет идентичен в каждом случае, есть ли способ сохранить этот суб-выбор как псевдостолбец в таблице? По сути, я хочу иметь возможность выбрать столбец из таблицы A, который является суммой определенного столбца из таблицы B, с которой связаны записи. Это возможно?

Answer 1

Есть ли способ сохранить этот суб-выбор в качестве псевдостолбца в таблице?

VIEW Как было рекомендовано, это совершенно правильное решение. Но есть и другой способ, который подходит вашему вопросу еще ближе. Вы можете написать функцию, которая принимает тип таблицы в качестве параметра для эмуляции "вычисляемого поля" или "сгенерированного столбца".

Рассмотрим этот контрольный пример, полученный из вашего описания:

CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);

CREATE TABLE tbl_b (b_id int, a_id int, colx int);
INSERT INTO tbl_b VALUES
 (1,1,5),  (2,1,5),  (3,1,1)
,(4,2,8),  (5,2,8),  (6,2,6)
,(7,3,11), (8,3,11), (9,3,11);

Создать функцию, которая имитирует col3:

CREATE FUNCTION col3(tbl_a)
  RETURNS int8 AS
$func$
    SELECT sum(colx)
    FROM   tbl_b b
    WHERE  b.a_id = $1.a_id
$func$ LANGUAGE SQL STABLE;

Теперь вы можете запросить:

SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

Или даже:

SELECT *, a.col3 FROM tbl_a a;

Обратите внимание, как я написал tbl_a.col3 / a.col3, не просто col3, Это важно

В отличие от "виртуального столбца" в Oracle, он не включается автоматически в SELECT * FROM tbl_a, Вы могли бы использовать VIEW для этого.

Почему это работает?

Обычный способ ссылки на столбец таблицы - с помощью обозначения атрибута:

 ВЫБЕРИТЕ tbl_a.col1 ОТ tbl_a; 

Обычный способ вызова функции - это функциональная запись:

 ВЫБЕРИТЕ col3 (tbl_a); 

Как правило, лучше придерживаться этих канонических способов, которые соответствуют стандарту SQL.

Но в PostgreSQL функциональные обозначения и обозначения атрибутов эквивалентны. Так что эти работы также:

 ВЫБЕРИТЕ col1(tbl_a) ОТ tbl_a;
ВЫБЕРИТЕ tbl_a.col3; 

Подробнее об этом в руководстве.
Вы, наверное, уже видите, куда это идет. Похоже, вы бы добавили дополнительный столбец таблицы tbl_a в то время как col3() на самом деле функция, которая принимает текущий ряд tbl_a (или его псевдоним) в качестве аргумента типа строки и вычисляет значение.

SELECT *, a.col3
FROM   tbl_a AS a;

Если есть фактический столбец col3 он имеет приоритет, и система не ищет функцию с таким именем, занимающую строку tbl_a в качестве параметра.

Прелесть этого: вы можете добавлять или удалять столбцы из tbl_a и последний запрос будет динамически возвращать все текущие столбцы, где представление будет возвращать только такие столбцы, которые существовали во время создания (раннее связывание или позднее связывание *).
Конечно, вам нужно отбросить зависимую функцию, прежде чем вы сможете удалить таблицу сейчас. И вы должны позаботиться о том, чтобы не сделать функцию недействительной при внесении изменений в таблицу.

Answer 2

Помимо представления, вы можете создать функцию для суммы.

CREATE FUNCTION sum_other_table( key type_of_key ) RETURNS bigint
AS $$ SELECT sum( col_x ) FROM table_1 where table_1.key = key $$ LANGUAGE SQL;

и затем используйте его в качестве агрегатора:

SELECT col_1, col_2, sum_other_table( key ) AS col_3
FROM table_2 WHERE table_2.key = key;

Обратите внимание, что тип возвращаемого значения sum_other_table() зависит от типа столбца, который вы суммируете.

Answer 3

По-видимому, это обрабатывается с представлениями, согласно комментарию льва. Так что в моем случае я использовал команду:

CREATE VIEW <viewname> AS
SELECT *, (SELECT sum(<col x>)
FROM   <otherTable
WHERE  <otherTable foreignkeyCol>=<thisTable keycol>) AS <col 3>
FROM   <tablename>

что по сути дает мне другую таблицу, включая нужный столбец.

Answer 4

Пока есть три ответа, каждый из которых работает. Любой из них может быть "лучшим решением" в зависимости от обстоятельств. С небольшими таблицами производительность должна быть довольно близкой, но ни одна из них, вероятно, не будет хорошо масштабироваться для таблиц с миллионами строк. Вероятно, самый быстрый способ получить желаемые результаты с большим набором данных (используя настройки Эрвина):

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id, col1, col2;

Если a_id объявлен в качестве первичного ключа, и это работает под 9.1 или более поздней, GROUP BY пункт может быть упрощен, потому что col1 а также col2 функционально зависят от a_id,

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id;

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