Una expresión de tabla calcula una tabla. La
expresión de tabla contiene una cláusula FROM que opcionalmente
es seguida por las cláusulas WHERE, GROUP BY y
HAVING. Las expresiones de tabla triviales simplemente hacen referencia
a una tabla en el disco, una llamada tabla base, pero se pueden usar expresiones más
complejas para modificar o combinar tablas base de varias maneras.
Las cláusulas opcionales WHERE, GROUP BY y
HAVING en la expresión de tabla especifican una
tubería (pipeline) de transformaciones sucesivas realizadas en la tabla
derivada en la cláusula FROM. Todas estas transformaciones
producen una tabla virtual que proporciona las filas que se pasan a
la lista de selección para calcular las filas de salida de la consulta.
FROM #
La cláusula FROM deriva una
tabla a partir de una o más tablas indicadas en una lista de referencias
de tabla separadas por comas:
FROMtable_reference[,table_reference[, ...]]
Una referencia de tabla puede ser un nombre de tabla (posiblemente calificado por esquema),
o una tabla derivada como una subconsulta, una construcción JOIN o
combinaciones complejas de estas. Si se enumera más de una referencia de tabla en la
cláusula FROM, las tablas se unen de forma cruzada
(es decir, se forma el producto cartesiano de sus filas; ver más abajo).
El resultado de la lista FROM es una tabla virtual intermedia
que luego puede ser objeto de transformaciones por las cláusulas WHERE,
GROUP BY y HAVING y, finalmente, es el resultado
de la expresión de tabla general.
Cuando una referencia de tabla nombra a una tabla que es la madre de una
jerarquía de herencia de tablas, la referencia de tabla produce filas no
solo de esa tabla sino de todas sus tablas descendientes, a menos que la
palabra clave ONLY preceda al nombre de la tabla. Sin embargo, la
referencia produce solo las columnas que aparecen en la tabla nombrada
— las columnas añadidas en las subtablas son ignoradas.
En lugar de escribir ONLY antes del nombre de la tabla, puedes escribir
* después del nombre de la tabla para especificar explícitamente que se
incluyan las tablas descendientes. Ya no hay una razón real para usar esta sintaxis,
porque la búsqueda en tablas descendientes es ahora siempre el comportamiento por defecto.
Sin embargo, se soporta para compatibilidad con versiones anteriores.
Una tabla unida es una tabla derivada de otras dos tablas (reales o derivadas) de acuerdo con las reglas del tipo de unión en particular. Están disponibles las uniones internas (inner), externas (outer) y cruzadas (cross). La sintaxis general de una tabla unida es
T1join_typeT2[join_condition]
Las uniones de todos los tipos se pueden encadenar o anidar: una de ellas
o ambas, T1 y
T2, pueden ser tablas unidas. Se pueden usar paréntesis
alrededor de las cláusulas JOIN para controlar el orden de unión.
En ausencia de paréntesis, las cláusulas JOIN se
anidan de izquierda a derecha.
Tipos de unión
T1CROSS JOINT2
Para cada combinación posible de filas de
T1 y
T2 (es decir, un producto cartesiano),
la tabla unida contendrá una
fila que consta de todas las columnas en T1
seguidas de todas las columnas en T2. Si
las tablas tienen N y M filas respectivamente, la tabla unida
tendrá N * M filas.
FROM es equivalente a
T1 CROSS JOIN
T2FROM (ver más abajo).
También es equivalente a
T1 INNER JOIN
T2 ON TRUEFROM .
T1,
T2
Esta última equivalencia no se cumple exactamente cuando aparecen más de dos
tablas, porque JOIN se asocia con mayor prioridad que la
coma. Por ejemplo,
FROM
no es lo mismo que
T1 CROSS JOIN
T2 INNER JOIN T3
ON condiciónFROM
porque la T1,
T2 INNER JOIN T3
ON condicióncondición puede
hacer referencia a T1 en el primer caso pero no
en el segundo.
T1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2ONboolean_expressionT1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2USING (join column list)T1NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
Las palabras INNER y
OUTER son opcionales en todas las formas.
INNER es el valor por defecto;
LEFT, RIGHT y
FULL implican una unión externa.
La condición de unión se especifica en la
cláusula ON o USING, o implícitamente por
la palabra clave NATURAL. La condición de unión determina
qué filas de las dos tablas origen se consideran que
“coinciden”, como se explica en detalle a continuación.
Los tipos posibles de unión calificada son:
INNER JOINPara cada fila R1 de T1, la tabla unida tiene una fila por cada fila en T2 que satisface la condición de unión con R1.
LEFT OUTER JOIN
Primero se realiza una unión interna. Luego, para cada fila en T1 que no satisface la condición de unión con ninguna fila en T2, se añade una fila unida con valores nulos en las columnas de T2. Por lo tanto, la tabla unida siempre tiene al menos una fila por cada fila en T1.
RIGHT OUTER JOIN
Primero se realiza una unión interna. Luego, para cada fila en T2 que no satisface la condición de unión con ninguna fila en T1, se añade una fila unida con valores nulos en las columnas de T1. Esto es lo contrario de una unión izquierda: la tabla resultante siempre tendrá una fila por cada fila en T2.
FULL OUTER JOINPrimero se realiza una unión interna. Luego, para cada fila en T1 que no satisface la condición de unión con ninguna fila en T2, se añade una fila unida con valores nulos en las columnas de T2. También, para cada fila de T2 que no satisface la condición de unión con ninguna fila en T1, se añade una fila unida con valores nulos en las columnas de T1.
La cláusula ON es el tipo más general de condición de
unión: toma una expresión de valor booleano del mismo
tipo que la utilizada en una cláusula WHERE. Un par de filas
de T1 y T2 coinciden si la
expresión ON se evalúa como verdadera.
La cláusula USING es una abreviación que permite aprovechar
la situación específica en la que ambos lados de la unión utilizan
el mismo nombre para la(s) columna(s) de unión. Toma una
lista de nombres de columnas compartidas separadas por comas
y forma una condición de unión que incluye una comparación de igualdad
para cada una. Por ejemplo, unir T1
y T2 con USING (a, b) produce
la condición de unión ON .
T1.a
= T2.a AND T1.b
= T2.b
Además, la salida de JOIN USING suprime
las columnas redundantes: no es necesario mostrar ambas columnas coincidentes,
ya que deben tener valores iguales. Mientras que JOIN
ON produce todas las columnas de T1 seguidas de todas
las columnas de T2, JOIN USING produce una
columna de salida para cada uno de los pares de columnas indicados (en el orden
indicado), seguida de cualquier columna restante de T1,
seguida de cualquier columna restante de T2.
Finalmente, NATURAL es una forma abreviada de
USING: forma una lista USING
que consta de todos los nombres de columnas que aparecen en ambas
tablas de entrada. Al igual que con USING, estas columnas aparecen
solo una vez en la tabla de salida. Si no hay nombres de columnas
comunes, NATURAL JOIN se comporta como
CROSS JOIN.
USING es razonablemente seguro frente a cambios de columnas
en las relaciones unidas, ya que solo se combinan las columnas
indicadas. NATURAL es considerablemente más arriesgado, ya que
cualquier cambio de esquema en cualquiera de las relaciones que cause la presencia de un
nuevo nombre de columna coincidente hará que la unión combine también esa nueva
columna.
Para juntar todo esto, supongamos que tenemos las tablas t1:
num | name -----+------ 1 | a 2 | b 3 | c
y t2:
num | value -----+------- 1 | xxx 3 | yyy 5 | zzz
entonces obtenemos los siguientes resultados para las distintas uniones:
=>SELECT * FROM t1 CROSS JOIN t2;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz (9 rows)=>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 rows)=>SELECT * FROM t1 INNER JOIN t2 USING (num);num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows)=>SELECT * FROM t1 NATURAL INNER JOIN t2;num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows)=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 rows)=>SELECT * FROM t1 LEFT JOIN t2 USING (num);num | name | value -----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy (3 rows)=>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz (3 rows)=>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz (4 rows)
La condición de unión especificada con ON también puede contener
condiciones que no se relacionen directamente con la unión. Esto puede
resultar útil para algunas consultas, pero debe pensarse con
cuidado. Por ejemplo:
=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | | (3 rows)
Ten en cuenta que colocar la restricción en la cláusula WHERE
produce un resultado diferente:
=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx (1 row)
Esto es porque una restricción colocada en la cláusula ON
se procesa antes de la unión, mientras que
una restricción colocada en la cláusula WHERE se procesa
después de la unión.
Eso no importa con las uniones internas, pero importa mucho con las uniones
externas.
Se puede dar un nombre temporal a las tablas y referencias complejas de tablas para que se utilicen al referirse a la tabla derivada en el resto de la consulta. Esto se llama un alias de tabla (table alias).
Para crear un alias de tabla, escribe
FROMtable_referenceASalias
o
FROMtable_referencealias
La palabra clave AS es ruido sintáctico opcional.
El alias puede ser cualquier identificador.
Una aplicación típica de los alias de tabla es asignar identificadores cortos a nombres de tablas largos para mantener legibles las cláusulas de unión. Por ejemplo:
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
El alias se convierte en el nuevo nombre de la referencia de tabla en lo que respecta a la consulta actual; no está permitido referirse a la tabla por su nombre original en otra parte de la consulta. Por lo tanto, esto no es válido:
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- incorrecto
Los alias de tabla sirven principalmente para comodidad de notación, pero es necesario usarlos al unir una tabla consigo misma, por ejemplo:
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
Se utilizan paréntesis para resolver ambigüedades. En el siguiente ejemplo,
la primera sentencia asigna el alias b a la segunda
instancia de my_table, pero la segunda sentencia asigna el
alias al resultado de la unión:
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
Otra forma de alias de tabla da nombres temporales a las columnas de la tabla, así como a la tabla misma:
FROMtable_reference[AS]alias(column1[,column2[, ...]] )
Si se especifican menos alias de columnas de los que tiene la tabla real, las columnas restantes no se renombrarán. Esta sintaxis es especialmente útil para autouniones o subconsultas.
Cuando se aplica un alias a la salida de una cláusula JOIN,
el alias oculta el nombre o nombres
originales dentro del JOIN. Por ejemplo:
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
es SQL válido, pero:
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
no es válido; el alias de tabla a no es visible
fuera del alias c.
Las subconsultas que especifican una tabla derivada deben encerrarse entre paréntesis. Se les puede asignar un alias de tabla y, opcionalmente, alias de columnas (como en Section 7.2.1.2). Por ejemplo:
FROM (SELECT * FROM table1) AS alias_name
Este ejemplo es equivalente a FROM table1 AS
alias_name. Casos más interesantes, que no se pueden
reducir a una unión simple, surgen cuando la subconsulta implica
agrupamiento o agregación.
Una subconsulta también puede ser una lista VALUES:
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
AS names(first, last)
Nuevamente, un alias de tabla es opcional. Asignar alias de nombres a las columnas
de la lista VALUES es opcional, pero es una buena práctica.
Para obtener más información, consulta Section 7.7.
Según el estándar SQL, se debe proporcionar un alias de tabla
para una subconsulta. PostgreSQL
permite omitir AS y el alias, pero
escribirlos es una buena práctica en código SQL que podría ser portado a
otro sistema.
Las funciones de tabla son funciones que producen un conjunto de filas, compuestas
ya sea por tipos de datos base (tipos escalares) o tipos de datos compuestos
(filas de tabla). Se utilizan como una tabla, vista o subconsulta en
la cláusula FROM de una consulta. Las columnas devueltas por las funciones de tabla
se pueden incluir en las cláusulas SELECT,
JOIN o WHERE de la misma manera
como las columnas de una tabla, vista o subconsulta.
Las funciones de tabla también se pueden combinar utilizando la sintaxis ROWS FROM,
con los resultados devueltos en columnas paralelas; el número de
filas de resultado en este caso es el del resultado de la función más grande, y los
resultados más pequeños se rellenan con valores nulos para coincidir.
function_call[WITH ORDINALITY] [[AS]table_alias[(column_alias[, ... ])]] ROWS FROM(function_call[, ... ] ) [WITH ORDINALITY] [[AS]table_alias[(column_alias[, ... ])]]
Si se especifica la cláusula WITH ORDINALITY, una
columna adicional de tipo bigint se agregará a las
columnas de resultado de la función. Esta columna enumera las filas del conjunto de
resultados de la función, comenzando desde 1. (Esta es una generalización de la
sintaxis del estándar SQL para UNNEST ... WITH ORDINALITY.)
Por defecto, la columna ordinal se llama ordinality, pero
se le puede asignar un nombre de columna diferente usando
una cláusula AS.
La función de tabla especial UNNEST puede llamarse con
cualquier número de parámetros de matriz, y devuelve un número correspondiente de
columnas, como si se hubiera llamado a UNNEST
(Section 9.19) en cada parámetro
por separado y se hubieran combinado utilizando la construcción ROWS FROM.
UNNEST(array_expression[, ... ] ) [WITH ORDINALITY] [[AS]table_alias[(column_alias[, ... ])]]
Si no se especifica ningún table_alias, se utiliza el nombre de
la función como el nombre de la tabla; en el caso de una construcción ROWS FROM(),
se utiliza el nombre de la primera función.
Si no se proporcionan alias de columna, entonces, para una función que devuelve un tipo de datos base, el nombre de la columna también es el mismo que el nombre de la función. Para una función que devuelve un tipo compuesto, las columnas de resultado obtienen los nombres de los atributos individuales del tipo.
Algunos ejemplos:
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
En algunos casos es útil definir funciones de tabla que puedan
devolver diferentes conjuntos de columnas según cómo se invoquen.
Para soportar esto, la función de tabla puede declararse como que devuelve
el pseudotipo record sin parámetros OUT.
Cuando tal función se utiliza en
una consulta, la estructura de fila esperada debe especificarse en la
propia consulta, para que el sistema pueda saber cómo analizar y planificar
la consulta. Esta sintaxis se ve así:
function_call[AS]alias(column_definition[, ... ])function_callAS [alias] (column_definition[, ... ]) ROWS FROM( ...function_callAS (column_definition[, ... ]) [, ... ] )
Cuando no se utiliza la sintaxis ROWS FROM(),
la lista column_definition reemplaza la lista de alias de
columna que de otro modo podría adjuntarse al elemento FROM;
los nombres en las definiciones de columna sirven como alias de columna.
Cuando se utiliza la sintaxis ROWS FROM(),
se puede adjuntar una lista column_definition a
cada función miembro por separado; o si hay solo una función miembro
y no hay cláusula WITH ORDINALITY,
se puede escribir una lista column_definition en
lugar de una lista de alias de columna después de ROWS FROM().
Considera este ejemplo:
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
La función dblink
(parte del módulo dblink) ejecuta
una consulta remota. Se declara para devolver
record ya que podría usarse para cualquier tipo de consulta.
El conjunto de columnas real debe especificarse en la consulta de llamada para
que el analizador sepa, por ejemplo, en qué debe expandirse *.
Este ejemplo utiliza ROWS FROM:
SELECT *
FROM ROWS FROM
(
json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
AS (a INTEGER, b TEXT),
generate_series(1, 3)
) AS x (p, q, s)
ORDER BY p;
p | q | s
-----+-----+---
40 | foo | 1
100 | bar | 2
| | 3
Une dos funciones en un único objetivo FROM.
json_to_recordset() está instruida
para devolver dos columnas, la primera integer
y la segunda text. El resultado de
generate_series() se utiliza directamente.
La cláusula ORDER BY ordena los valores de las columnas
como enteros.
LATERAL #
Las subconsultas que aparecen en FROM pueden ser
precedidas por la palabra clave LATERAL. Esto les permite
hacer referencia a columnas proporcionadas por elementos FROM precedentes.
(Sin LATERAL, cada subconsulta se
evalúa de forma independiente y, por lo tanto, no puede hacer referencia cruzada a ningún otro
elemento FROM).
Las funciones de tabla que aparecen en FROM también pueden ser
precedidas por la palabra clave LATERAL, pero para las funciones la
palabra clave es opcional; los argumentos de la función pueden contener referencias
a columnas proporcionadas por elementos FROM precedentes en cualquier caso.
Un elemento LATERAL puede aparecer en el nivel superior en la
lista FROM, o dentro de un árbol JOIN. En este último
caso, también puede hacer referencia a cualquier elemento que esté en el lado izquierdo de un
JOIN del cual él mismo se encuentre en el lado derecho.
Cuando un elemento FROM contiene referencias cruzadas
de tipo LATERAL, la evaluación procede de la siguiente manera: para cada fila del
elemento FROM que proporciona la(s) columna(s) referenciada(s), o el
conjunto de filas de múltiples elementos FROM que proporcionan las
columnas, el elemento LATERAL se evalúa utilizando los valores de
las columnas de esa fila o conjunto de filas. Las filas resultantes se
unen de la manera habitual con las filas a partir de las cuales se calcularon. Esto se
repite para cada fila o conjunto de filas de la(s) tabla(s) origen de las columnas.
Un ejemplo trivial de LATERAL es
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
Esto no es especialmente útil ya que tiene exactamente el mismo resultado que la forma más convencional:
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
LATERAL es principalmente útil cuando la columna referenciada
de forma cruzada es necesaria para calcular la(s) fila(s) a unir. Una
aplicación común es proporcionar un valor de argumento para una función que devuelve un conjunto.
Por ejemplo, suponiendo que vertices(polygon) devuelve el
conjunto de vértices de un polígono, podríamos identificar los vértices cercanos
de los polígonos almacenados en una tabla con:
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
Esta consulta también podría escribirse como:
SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
o en varias otras formulaciones equivalentes. (Como ya se mencionó,
la palabra clave LATERAL no es necesaria en este ejemplo, pero
la usamos por claridad).
A menudo es particularmente práctico realizar un LEFT JOIN a una
subconsulta LATERAL, de modo que las filas origen aparezcan en
el resultado incluso si la subconsulta LATERAL no produce
filas para ellas. Por ejemplo, si get_product_names() devuelve
los nombres de los productos fabricados por un fabricante, pero algunos fabricantes en
nuestra tabla actualmente no producen productos, podríamos descubrir cuáles
son de esta manera:
SELECT m.name FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true WHERE pname IS NULL;
WHERE #
La sintaxis de la cláusula WHERE
es
WHERE search_condition
donde search_condition es cualquier expresión de
valor (ver Section 4.2) que
devuelve un valor de tipo boolean.
Después de que se completa el procesamiento de la cláusula FROM, cada
fila de la tabla virtual derivada se comprueba contra la condición de
búsqueda. Si el resultado de la condición es verdadero, la fila se
mantiene en la tabla de salida; de lo contrario (es decir, si el resultado es
falso o nulo), se descarta. La condición de búsqueda típicamente
hace referencia a al menos una columna de la tabla generada en la
cláusula FROM; esto no es obligatorio, pero de lo contrario la
cláusula WHERE será bastante inútil.
La condición de unión de una unión interna se puede escribir tanto en
la cláusula WHERE como en la cláusula JOIN.
Por ejemplo, estas expresiones de tabla son equivalentes:
FROM a, b WHERE a.id = b.id AND b.val > 5
y:
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
o tal vez incluso:
FROM a NATURAL JOIN b WHERE b.val > 5
Cuál de estas utilices es principalmente una cuestión de estilo. La
sintaxis JOIN en la cláusula FROM es
probablemente no tan portable a otros sistemas de gestión de bases de datos SQL,
aunque está en el estándar SQL. Para las uniones
externas no hay elección: deben hacerse en
la cláusula FROM. La cláusula ON o USING
de una unión externa no es equivalente a una condición
WHERE, porque da como resultado la adición
de filas (para las filas de entrada no coincidentes), así como la eliminación de filas
en el resultado final.
Aquí tienes algunos ejemplos de cláusulas WHERE:
SELECT ... FROM fdt WHERE c1 > 5 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3) SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
fdt es la tabla derivada en la cláusula
FROM. Las filas que no cumplen con la condición de búsqueda
de la cláusula WHERE son eliminadas de
fdt. Observa el uso de subconsultas escalares como
expresiones de valor. Al igual que cualquier otra consulta, las subconsultas pueden
emplear expresiones de tabla complejas. Observa también cómo se hace
referencia a fdt en las subconsultas.
Calificar c1 como fdt.c1 solo es necesario
si c1 es también el nombre de una columna en la tabla de entrada
derivada de la subconsulta. Pero calificar el nombre de la columna añade
claridad incluso cuando no es necesario. Este ejemplo muestra cómo el ámbito de
nombres de columnas de una consulta externa se extiende a sus consultas internas.
GROUP BY y HAVING #
Después de pasar el filtro WHERE, la tabla de entrada
derivada puede estar sujeta a agrupamiento, utilizando la cláusula GROUP BY,
y a la eliminación de filas de grupo utilizando la cláusula HAVING.
SELECTselect_listFROM ... [WHERE ...] GROUP BYgrouping_column_reference[,grouping_column_reference]...
La cláusula GROUP BY se
utiliza para agrupar aquellas filas en una tabla que tienen los mismos
valores en todas las columnas enumeradas. El orden en el que se enumeran las columnas
no importa. El efecto es combinar cada conjunto de filas que tienen valores comunes
en una sola fila de grupo que representa a todas las filas del grupo. Esto se hace
para eliminar la redundancia en la salida y/o calcular agregaciones que se
aplican a estos grupos. Por ejemplo:
=>SELECT * FROM test1;x | y ---+--- a | 3 c | 2 b | 5 a | 1 (4 rows)=>SELECT x FROM test1 GROUP BY x;x --- a b c (3 rows)
En la segunda consulta, no podríamos haber escrito SELECT *
FROM test1 GROUP BY x, porque no hay un único valor
para la columna y que pudiera asociarse con cada
grupo. Se puede hacer referencia a las columnas agrupadas en la lista de selección,
ya que tienen un único valor en cada grupo.
En general, si una tabla está agrupada, las columnas que no están
enumeradas en GROUP BY no pueden ser referenciadas excepto en expresiones
de agregación. Un ejemplo con expresiones de agregación es:
=>SELECT x, sum(y) FROM test1 GROUP BY x;x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)
Aquí sum es una función de agregación que
calcula un único valor sobre todo el grupo. Se puede encontrar más información
sobre las funciones de agregación disponibles en Section 9.21.
El agrupamiento sin expresiones de agregación calcula eficazmente el
conjunto de valores distintos en una columna. Esto también se puede lograr
utilizando la cláusula DISTINCT (ver Section 7.3.3).
Aquí tienes otro ejemplo: calcula las ventas totales para cada producto (en lugar de las ventas totales de todos los productos):
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
En este ejemplo, las columnas product_id,
p.name y p.price deben estar
en la cláusula GROUP BY ya que están referenciadas en
la lista de selección de la consulta (pero consulta más abajo). La columna
s.units no tiene que estar en la lista GROUP
BY ya que solo se usa en una expresión de agregación
(sum(...)), que representa las ventas
de un producto. Para cada producto, la consulta devuelve una fila de resumen sobre
todas las ventas del producto.
Si la tabla de productos está configurada de modo que, por ejemplo,
product_id sea la clave primaria, entonces sería
suficiente agrupar por product_id en el ejemplo anterior,
ya que el nombre y el precio dependerían funcionalmente (functionally
dependent) del ID del producto, y por lo tanto no habría
ambigüedad sobre qué valor de nombre y precio devolver para cada grupo de ID de
producto.
En SQL estricto, GROUP BY solo puede agrupar por columnas de
la tabla origen, pero PostgreSQL amplía
esto para permitir también que GROUP BY agrupe por columnas en la
lista de selección. También se permite agrupar por expresiones de valor en lugar de
nombres de columna simples.
Si una tabla ha sido agrupada utilizando GROUP BY,
pero solo interesan ciertos grupos, se puede utilizar la cláusula
HAVING, de manera muy similar a una cláusula
WHERE, para eliminar grupos del resultado.
La sintaxis es:
SELECTselect_listFROM ... [WHERE ...] GROUP BY ... HAVINGboolean_expression
Las expresiones en la cláusula HAVING pueden hacer referencia tanto a
expresiones agrupadas como a expresiones no agrupadas (que necesariamente
involucran una función de agregación).
Ejemplo:
=>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;x | sum ---+----- a | 4 b | 5 (2 rows)=>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';x | sum ---+----- a | 4 b | 5 (2 rows)
Nuevamente, un ejemplo más realista:
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;
En el ejemplo anterior, la cláusula WHERE selecciona
filas por una columna que no está agrupada (la expresión solo es verdadera para
las ventas durante las últimas cuatro semanas), mientras que la cláusula HAVING
restringe la salida a grupos con ventas brutas totales superiores a
5000. Ten en cuenta que las expresiones de agregación no necesitan ser necesariamente
las mismas en todas las partes de la consulta.
Si una consulta contiene llamadas a funciones de agregación, pero no tiene una cláusula GROUP BY,
aún así ocurre el agrupamiento: el resultado es una sola fila de grupo (o
tal vez ninguna fila en absoluto, si la fila única es luego eliminada por
HAVING).
Lo mismo ocurre si contiene una cláusula HAVING, incluso
sin llamadas a funciones de agregación o cláusula GROUP BY.
GROUPING SETS, CUBE y ROLLUP #
Es posible realizar operaciones de agrupamiento más complejas que las descritas anteriormente
utilizando el concepto de conjuntos de agrupamiento (grouping sets). Los datos seleccionados por
las cláusulas FROM y WHERE se agrupan por separado
por cada conjunto de agrupamiento especificado, se calculan las agregaciones para cada grupo al igual que
para las cláusulas simples GROUP BY, y luego se devuelven los resultados.
Por ejemplo:
=>SELECT * FROM items_sold;brand | size | sales -------+------+------- Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 (4 rows)=>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());brand | size | sum -------+------+----- Foo | | 30 Bar | | 20 | L | 15 | M | 35 | | 50 (5 rows)
Cada sublista de GROUPING SETS puede especificar cero o más columnas
o expresiones y se interpreta de la misma manera que si estuviera directamente
en la cláusula GROUP BY. Un conjunto de agrupamiento vacío significa que todas las
filas se agregan a un solo grupo (que se genera incluso si no hay
filas de entrada presentes), tal como se describió anteriormente para el caso de funciones de agregación
sin cláusula GROUP BY.
Las referencias a las columnas o expresiones de agrupamiento se reemplazan por valores nulos en las filas de resultados de los conjuntos de agrupamiento en los que no aparecen esas columnas. Para distinguir de qué agrupamiento resultó una fila de salida en particular, consulta Table 9.66.
Se proporciona una notación abreviada para especificar dos tipos comunes de conjuntos de agrupamiento. Una cláusula de la forma
ROLLUP (e1,e2,e3, ... )
representa la lista dada de expresiones y todos los prefijos de la lista, incluida la lista vacía; por lo tanto, es equivalente a
GROUPING SETS (
( e1, e2, e3, ... ),
...
( e1, e2 ),
( e1 ),
( )
)
Esto se usa comúnmente para el análisis de datos jerárquicos; por ejemplo, el salario total por departamento, división y el total de la empresa.
Una cláusula de la forma
CUBE (e1,e2, ... )
representa la lista dada y todos sus subconjuntos posibles (es decir, el conjunto potencia). Por lo tanto
CUBE ( a, b, c )
es equivalente a
GROUPING SETS (
( a, b, c ),
( a, b ),
( a, c ),
( a ),
( b, c ),
( b ),
( c ),
( )
)
Los elementos individuales de una cláusula CUBE o ROLLUP
pueden ser expresiones individuales o sublists de elementos en
parentesis. En este último caso, las sublistas se tratan como unidades
individuales a efectos de generar los conjuntos de agrupamiento individuales.
Por ejemplo:
CUBE ( (a, b), (c, d) )
es equivalente a
GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
y
ROLLUP ( a, (b, c), d )
es equivalente a
GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
Las construcciones CUBE y ROLLUP se pueden usar directamente
en la cláusula GROUP BY o anidadas dentro de una cláusula
GROUPING SETS. Si una cláusula GROUPING SETS
está anidada dentro de otra, el efecto es el mismo que si todos los elementos de
la cláusula interna se hubieran escrito directamente en la cláusula externa.
Si se especifican múltiples elementos de agrupamiento en una sola cláusula GROUP BY,
entonces la lista final de conjuntos de agrupamiento es el producto cartesiano de los
elementos individuales. Por ejemplo:
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
es equivalente a
GROUP BY GROUPING SETS (
(a, b, c, d), (a, b, c, e),
(a, b, d), (a, b, e),
(a, c, d), (a, c, e),
(a, d), (a, e)
)
Al especificar múltiples elementos de agrupamiento juntos, el conjunto final de conjuntos de agrupamiento puede contener duplicados. Por ejemplo:
GROUP BY ROLLUP (a, b), ROLLUP (a, c)
es equivalente a
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, b),
(a, c),
(a),
(a),
(a, c),
(a),
()
)
Si estos duplicados son indeseables, se pueden eliminar utilizando la
cláusula DISTINCT directamente en el GROUP BY.
Por lo tanto:
GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)
is equivalent to
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, c),
(a),
()
)
Esto no es lo mismo que usar SELECT DISTINCT porque las filas de
salida aún pueden contener duplicados. Si alguna de las columnas no agrupadas contiene NULL,
será indistinguible del NULL utilizado cuando esa misma columna está agrupada.
La construcción (a, b) se reconoce normalmente en las expresiones como
un constructor de filas.
Dentro de la cláusula GROUP BY, esto no se aplica en los
niveles superiores de las expresiones, y (a, b) se analiza como una lista de
expresiones como se describe anteriormente. Si por alguna razón necesitas
un constructor de filas en una expresión de agrupamiento, usa ROW(a, b).
Si la consulta contiene alguna función de ventana (ver
Section 3.5,
Section 9.22 y
Section 4.2.8), estas funciones se evalúan
después de realizar cualquier agrupamiento, agregación y filtrado por HAVING.
Es decir, si la consulta utiliza agregados, GROUP
BY o HAVING, entonces las filas que ven las funciones de ventana
son las filas de grupo en lugar de las filas de la tabla original de
FROM/WHERE.
Cuando se utilizan múltiples funciones de ventana, se garantiza que todas las funciones de ventana que tengan
cláusulas PARTITION BY y ORDER BY
equivalentes en sus definiciones de ventana verán el mismo
ordenamiento de las filas de entrada, incluso si el ORDER BY no
determina el ordenamiento de forma única.
Sin embargo, no se ofrecen garantías sobre la evaluación de funciones que tengan
especificaciones PARTITION BY u ORDER BY diferentes.
(En tales casos, normalmente se requiere un paso de ordenamiento entre las pasadas de
evaluación de las funciones de ventana, y no se garantiza que el ordenamiento preserve el
orden de las filas que su ORDER BY ve como equivalentes).
Actualmente, las funciones de ventana siempre requieren datos preordenados, por lo que la
salida de la consulta se ordenará según una u otra de las cláusulas PARTITION BY/ORDER BY de las funciones de ventana.
Sin embargo, no se recomienda confiar en esto. Utiliza una cláusula
ORDER BY explícita en el nivel superior si deseas asegurarte de que los
resultados se ordenen de una manera particular.