Hologres:SELECT

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
from_item [ NATURAL ] join_type from_item
          [ ON join_condition | USING ( join_column [, ...] ) ]

Cláusula WITH

La cláusula WITH define Common Table Expressions (CTE), que son subconsultas con nombre a las que se hace referencia en la consulta principal.

WITH query_name [ ( column_name [, ...] ) ] AS ( select )

Separe varias CTE con comas. Una CTE posterior puede hacer referencia a una anterior. En consultas subsecuentes, utilice query_name directamente como si fuera una vista.

Si no se proporciona una lista de column_name, los nombres de columna toman como valor predeterminado aquellos devueltos por la subconsulta. Si se incluye una lista de column_name, la cantidad debe coincidir con las columnas que devuelve la instrucción SELECT.

Lista SELECT

La lista SELECT (ubicada entre las palabras clave SELECT y FROM) especifica las expresiones que conforman las filas de salida.

Agregue AS output_name después de una expresión para asignar un nombre a la columna de salida. La palabra clave AS es opcional, salvo que el nombre entre en conflicto con una palabra clave de PostgreSQL; en ese caso, utilice AS o escriba el nombre entre comillas dobles. Si no se especifica un nombre explícito, PostgreSQL usa el nombre de la columna para referencias simples o genera un nombre como ?column? para expresiones complejas.

Haga referencia a una columna de salida por su nombre en las cláusulas ORDER BY y GROUP BY. En las cláusulas WHERE y HAVING, utilice la expresión completa en su lugar, ya que los nombres de columnas de salida no se reconocen en esos contextos.

Utilice un asterisco (*) como forma abreviada para seleccionar todas las columnas de las filas indicadas, o bien table_name.* para todas las columnas de una tabla específica. Al usar * o table_name.*, no es posible utilizar AS para cambiar el nombre de las columnas de salida.

Cláusula FROM

La cláusula FROM especifica una o más tablas de origen. Si hay varias tablas de origen, se genera un producto cartesiano (cross join) de todas las filas. Por lo general, una cláusula WHERE con condiciones de unión restringe el resultado a un subconjunto pequeño del producto cartesiano.

La cláusula FROM admite los siguientes elementos:

Cláusula WHERE

La cláusula opcional WHERE filtra las filas del resultado.

WHERE condition

Cláusula GROUP BY

La cláusula opcional GROUP BY condensa las filas que comparten los mismos valores en las expresiones de agrupamiento en una sola fila de resumen.

GROUP BY grouping_element [, ...]

La expresión grouping_element puede ser el nombre de una columna de entrada, el nombre o número ordinal de una columna de salida de la lista SELECT, o una expresión formada por valores de columnas de entrada. Si un nombre es ambiguo, GROUP BY lo interpreta como un nombre de columna de entrada en lugar de un nombre de columna de salida.

Si un elemento de agrupamiento contiene GROUPING SETS, ROLLUP o CUBE, la cláusula GROUP BY define múltiples conjuntos de agrupamiento independientes. Esto equivale a un UNION ALL de subconsultas con cláusulas GROUP BY individuales para cada conjunto de agrupamiento.

Las funciones de agregación se calculan sobre todas las filas de cada grupo y producen un único valor por grupo. Si no hay una cláusula GROUP BY, la consulta trata todas las filas seleccionadas como un solo grupo.

Agregue una cláusula FILTER a una llamada de función de agregación para incluir solo las filas que cumplen la condición de filtro en la entrada de dicha agregación.

Cuando existe una cláusula GROUP BY o alguna función de agregación, las expresiones de la lista SELECT no pueden hacer referencia a columnas no agrupadas, excepto dentro de una función de agregación o cuando la columna no agrupada depende funcionalmente de las columnas agrupadas. Existe una dependencia funcional cuando las columnas agrupadas incluyen la clave primaria (o un subconjunto de ella) de la tabla que contiene la columna no agrupada.

Todas las funciones de agregación se calculan antes que cualquier expresión escalar en la cláusula HAVING o en la lista SELECT. Una expresión CASE no puede omitir el cálculo de una función de agregación.

Cláusula HAVING

La cláusula HAVING filtra los grupos generados por GROUP BY. Su sintaxis es igual a la de WHERE, pero HAVING opera sobre filas agrupadas en lugar de filas individuales.

HAVING condition [, ...]

Utilice HAVING para filtrar basándose en resultados de agregaciones. Por ejemplo, HAVING sum(length) < interval '5 hours' elimina los grupos donde la duración total es de 5 horas o más.

CUBE, ROLLUP y GROUPING SETS

Estas subcláusulas extienden GROUP BY para definir múltiples conjuntos de agrupamiento en una sola consulta.

CUBE ( { expression | ( expression [, ...] ) } [, ...] )

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )

GROUPING SETS ( grouping_element [, ...] )

Cláusula DISTINCT

SELECT DISTINCT elimina todas las filas duplicadas del conjunto de resultados y conserva solo una fila por cada grupo de duplicados.

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT

COUNT DISTINCT cuenta el número de valores distintos en una columna. Un valor que aparece varias veces se cuenta solo una vez. Los valores NULL se excluyen del recuento.

Recuento exacto:

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Recuento aproximado:

El uso de COUNT DISTINCT exacto puede consumir muchos recursos. Hologres ofrece approx_count_distinct como una alternativa aproximada que requiere menos recursos:

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;

Cláusula UNION

UNION calcula la unión de conjuntos de las filas devueltas por dos instrucciones SELECT.

select_statement UNION [ ALL | DISTINCT ] select_statement

Cláusula INTERSECT

INTERSECT calcula la intersección de conjuntos de las filas devueltas por dos instrucciones SELECT.

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

Cláusula EXCEPT

EXCEPT calcula la diferencia de conjuntos: las filas presentes en el resultado del SELECT izquierdo que no están en el resultado del SELECT derecho.

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

Cláusula ORDER BY

La cláusula opcional ORDER BY ordena las filas del resultado según una o más expresiones.

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

Las filas se ordenan primero por la expresión más a la izquierda y luego por las expresiones subsecuentes para desempatar. Las filas iguales en todas las expresiones se devuelven en un orden que depende de la implementación.

Cada expresión puede ser:

• El nombre o número ordinal de una columna de salida de la lista SELECT.

• Una expresión arbitraria formada por valores de columnas de entrada.

El número ordinal hace referencia a la posición de la columna de salida (de izquierda a derecha), lo cual es útil para columnas sin nombres únicos. Generalmente, se prefiere asignar un nombre mediante AS.

Cuando una expresión de ORDER BY es un nombre simple que coincide tanto con un nombre de columna de salida como con uno de entrada, ORDER BY utiliza el nombre de la columna de salida. Esto difiere de GROUP BY, que en la misma situación utiliza el nombre de la columna de entrada. Esta inconsistencia existe para mantener la compatibilidad con el estándar SQL.

Dirección de ordenamiento:

• Agregue ASC (ascendente, valor predeterminado) o DESC (descendente) después de cualquier expresión.

• Alternativamente, utilice una cláusula USING con un operador de ordenamiento que sea miembro menor que o mayor que de una familia de operadores B-tree. ASC generalmente equivale a USING <, y DESC a USING >.

Orden de valores NULL:

• NULLS LAST: Los valores NULL se ordenan después de todos los valores no NULL (predeterminado para ASC).

• NULLS FIRST: Los valores NULL se ordenan antes de todos los valores no NULL (predeterminado para DESC).

• De forma predeterminada, los valores NULL se tratan como mayores que los valores no NULL.

• Con una cláusula USING, la posición predeterminada de NULL depende de si el operador es menor que o mayor que.

Las opciones de ordenamiento se aplican únicamente a la expresión que siguen. Por ejemplo, ORDER BY x, y DESC no es lo mismo que ORDER BY x DESC, y DESC.

Limitación: Cuando ORDER BY se aplica al resultado de UNION, INTERSECT o EXCEPT, solo se permiten nombres de columnas de salida o números ordinales; no se admiten expresiones.

Cláusula LIMIT

La cláusula LIMIT restringe el número de filas devueltas. FETCH FIRST es una sintaxis equivalente del estándar SQL.

LIMIT { count | ALL }
OFFSET start

Sintaxis alternativa del estándar SQL:

FETCH FIRST count ROWS ONLY

Cuando se especifican ambos, se omiten start filas antes de contar count filas para devolver.

Utilice una cláusula ORDER BY junto con LIMIT para obtener resultados predecibles. Sin ORDER BY, distintas ejecuciones pueden devolver diferentes subconjuntos; este es un comportamiento esperado, no un bug.

El planificador de consultas toma en cuenta LIMIT al generar el plan, por lo que diferentes valores de LIMIT y OFFSET pueden producir distintos órdenes de filas. Imponga un orden determinista con ORDER BY para lograr una paginación consistente.

Consulta básica

Recuperar todas las columnas de una tabla:

SELECT * FROM distributors ORDER BY name;

Unir dos tablas

SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did;

Cláusula WITH (CTE)

WITH distributor_name(name) AS (
    SELECT name FROM distributors
)
SELECT name FROM distributor_name ORDER BY name;

GROUP BY

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind;

GROUP BY con HAVING

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind
HAVING sum(length) < interval '5 hours';

GROUP BY CUBE

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY CUBE ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY ROLLUP

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY ROLLUP ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY GROUPING SETS

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY GROUPING SETS ((l_returnflag, l_shipmode), ())
ORDER BY l_returnflag, l_shipmode;

DISTINCT

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT (exacto y aproximado)

Exacto:

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Aproximado:

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;