tablas - unir dos consultas postgresql

Almacenar consulta común como columna?

Question

Usando PostgreSQL, tengo una cantidad de consultas que se ven así:

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

Dado que la sub-selección será idéntica en todos los casos, ¿hay alguna forma de almacenar esa sub-selección como una pseudo-columna en la tabla? Básicamente, quiero poder seleccionar una columna de la tabla A que sea una suma de una columna específica de la tabla B donde los registros estén relacionados. es posible?

Answer 1

¿Hay alguna manera de almacenar esa sub-selección como una pseudo-columna en la tabla?

UNA VIEW como se ha informado es una solución perfectamente válida. Pero hay otra manera que se ajusta a su pregunta aún más de cerca. Puede escribir una función que tome el tipo de tabla como parámetro para emular un "campo calculado" o "columna generada" .

Considere este caso de prueba, derivado de su descripción:

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);

Crear una función que emule 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;

Ahora puedes consultar:

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

O incluso:

SELECT *, a.col3 FROM tbl_a a;

Tenga en cuenta cómo escribí tbl_a.col3 / a.col3 , no solo col3 . Esto es esencial .

A diferencia de una "columna virtual" en Oracle , no se incluye automáticamente en un SELECT * FROM tbl_a . Podría usar una VIEW para eso.

¿Por qué funciona esto?

La forma más común de referenciar una columna de tabla es con la notación de atributo :

SELECT tbl_a.col1 FROM tbl_a;

La forma común de llamar a una función es con notación funcional :

SELECT col3(tbl_a);

En general, es mejor seguir estas formas canónicas , que están de acuerdo con el estándar SQL.

Pero en PostgreSQL, la notación funcional y la notación de atributos son equivalentes. Entonces estos funcionan también:

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

Más sobre eso en el manual.
Probablemente ya veas a dónde va esto. Parece que agregaría una columna extra de la tabla tbl_a mientras que tbl_a col3() es en realidad una función que toma la fila actual de tbl_a (o su alias) como argumento de tipo de fila y calcula un valor.

SELECT *, a.col3
FROM tbl_a AS a;

Si hay una columna real col3 , tiene prioridad y el sistema no busca una función de ese nombre tomando la fila tbl_a como parámetro.

La belleza de esto: puede agregar o eliminar columnas de tbl_a y la última consulta devolverá dinámicamente todas las columnas actuales, donde una vista solo devolvería las columnas que existían en el momento de la creación (enlace anticipado frente a enlace tardío de * ).
Por supuesto, debe soltar la función dependiente antes de poder soltar la tabla ahora. Y debe tener cuidado de no invalidar la función cuando realice cambios en la tabla.

Answer 2

Además de una vista, puede crear una función para la suma.

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;

y luego úsalo como tu agregador:

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

Tenga en cuenta que el tipo de devolución de sum_other_table () depende del tipo de columna que está resumiendo.

Answer 3

Aparentemente esto se maneja con vistas, según el comentario del león. Entonces en mi caso, utilicé el comando:

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

que esencialmente me da otra tabla que incluye la columna deseada.

Answer 4

Hay tres respuestas hasta ahora, todas funcionan. Cualquiera de ellos podría ser una "mejor solución" según las circunstancias. En el caso de las tablas pequeñas, el rendimiento debería ser bastante cercano, pero es probable que ninguna de ellas se adapte bien a las tablas con millones de filas. La manera más rápida de obtener los resultados deseados con un gran conjunto de datos sería probablemente (usando la configuración de 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;

Si a_id se declara como clave principal, y se ejecuta bajo 9.1 o posterior, la cláusula GROUP BY se puede simplificar porque col1 y col2 dependen funcionalmente de a_id .

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

La vista se podría definir de esta manera y se escalaría, pero no creo que se consideren todas las mismas rutas de ejecución para los enfoques que usan funciones, por lo que la ruta de ejecución más rápida podría no ser utilizada.