Вопрос по postgresql, database-design, view – Сохранить общий запрос как столбец?

33

Используя 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, с которой связаны записи. Это возможно?

Видимо я могу, да. Спасибо! ibrewster
Не могли бы вы создать представление для этого? Ilion

Ваш Ответ

4   ответа
70

Is there a way to store that sub-select as a pseudo-column in the table?

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

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

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, Этоessential.

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

Why does this work?

Обычный способ ссылки на столбец таблицы -attribute notation:

SELECT tbl_a.col1 FROM tbl_a;

Общий способ вызова функции заключается вfunctional notation:

SELECT col3(tbl_a);

Как правило, лучше всегоstick to these canonical ways, которые согласны со стандартом SQL.

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

SELECT col1(tbl_a) FROM tbl_a;
SELECT tbl_a.col3;

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

SELECT *, a.col3
FROM   tbl_a AS a;

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

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

@kgrittn: Конечно, я внес соответствующие изменения в объявление функции. Забыл, что участвует другой стол.
@ErwinBrandstetter - но если вы сделаете a \ d tbl_a; или SELECT * FROM tbl_a ;, вы не можете видеть «сохраненный / вычисленный / вычисленный / сгенерированный»; столбец? Кроме того, я был бы признателен, если бы вы могли проверить мой ответhere - может быть, я не правильно использую ваш код? Или это ошибка в PostgreSQL?
@ V & # xE9; гонка: эта функция не является частью таблицы автоматически, вы должны изложить ее в соответствии с инструкциями. Я уточнил еще немного.
Функция действительно должна быть отмеченаSTABLE (это означает, что если вы вызываете его с одинаковыми аргументами более одного раза во время выполнения одного запроса, он вернет одно и то же значение), а неIMMUTABLE (имеется в виду, что это будетalways возвращать одно и то же значение для одних и тех же аргументов, независимо от содержимого базы данных или времени). Одна вещь, которую это сделает, - это предотвращение использования функции в индексе, что вы хотели бы, потому что в противном случае индекс был бы поврежден, если были сделаны изменения в tbl_b.IMMUTABLE было бы хорошо, если бы вы использовали только значения из строки, переданной в качестве параметра.
2

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

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 являютсяfunctionally dependent наa_id.

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

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

@JohannOskarsson: даже если функция встроена, моя версия приводит кcorrelated subquery вместоjoin, Я сомневаюсь, что оптимизатор осмелится изменить это.
Простые функции в языке ЯЗЫК SQL будут встроены, и оптимизатор запросов в любом случае может предложить лучшее решение. Происходит ли это в данном конкретном случае - это другой вопрос.
2

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

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

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

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 () зависит от типа столбца, который вы суммируете.

Похожие вопросы