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

A cláusula WITH define Expressões de Tabela Comuns (CTEs), que são subconsultas nomeadas referenciadas na consulta principal.

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

Separe múltiplas CTEs com vírgulas. Uma CTE posterior pode referenciar uma anterior. Nas consultas subsequentes, use query_name diretamente como uma visualização.

Sem uma lista de column_name, os nomes das colunas assumem como padrão aqueles retornados pela subconsulta. Com uma lista de column_name, a quantidade deve corresponder às colunas retornadas pela instrução SELECT.

Lista SELECT

A lista SELECT (entre as palavras-chave SELECT e FROM) especifica as expressões que formam as linhas de saída.

Adicione AS output_name após uma expressão para nomear a coluna de saída. A palavra-chave AS é opcional, a menos que o nome entre em conflito com uma palavra-chave do PostgreSQL. Nesse caso, use AS ou coloque o nome entre aspas duplas. Sem um nome explícito, o PostgreSQL usa o nome da coluna para referências simples ou gera um nome como ?column? para expressões complexas.

Referencie uma coluna de saída pelo nome nas cláusulas ORDER BY e GROUP BY. Nas cláusulas WHERE e HAVING, use a expressão completa, pois esses contextos não reconhecem nomes de colunas de saída.

Use um asterisco (*) como abreviação para todas as colunas das linhas selecionadas, ou table_name.* para todas as colunas de uma tabela específica. Ao usar * ou table_name.*, o AS não pode renomear as colunas de saída.

Cláusula FROM

A cláusula FROM especifica uma ou mais tabelas de source. Múltiplas tabelas de source produzem um produto cartesiano (junção cruzada) de todas as linhas. Geralmente, uma cláusula WHERE com condições de junção restringe o resultado a um pequeno subconjunto do produto cartesiano.

A cláusula FROM oferece suporte aos seguintes elementos:

Cláusula WHERE

A cláusula opcional WHERE filtra linhas do resultado.

WHERE condition

Cláusula GROUP BY

A cláusula opcional GROUP BY condensa linhas que compartilham os mesmos valores de expressão de agrupamento em uma única linha de resumo.

GROUP BY grouping_element [, ...]

A expressão grouping_element pode ser o nome de uma coluna de entrada, o nome ou número ordinal de uma coluna de saída da lista SELECT, ou uma expressão formada por valores de colunas de entrada. Se um nome for ambíguo, o GROUP BY o interpreta como nome de coluna de entrada em vez de nome de coluna de saída.

Se um elemento de agrupamento contiver GROUPING SETS, ROLLUP ou CUBE, a cláusula GROUP BY define múltiplos conjuntos de agrupamento independentes. Isso equivale a um UNION ALL de subconsultas com cláusulas GROUP BY individuais para cada conjunto de agrupamento.

As funções de agregação calculam sobre todas as linhas em cada grupo, produzindo um único valor por grupo. Sem uma cláusula GROUP BY, a consulta trata todas as linhas selecionadas como um único grupo.

Anexe uma cláusula FILTER a uma chamada de função de agregação para incluir apenas as linhas que correspondem à condição de filtro na entrada dessa agregação.

Quando uma cláusula GROUP BY ou qualquer função de agregação estiver presente, as expressões da lista SELECT não podem referenciar colunas não agrupadas, exceto dentro de uma função de agregação ou quando a coluna não agrupada for funcionalmente dependente das colunas agrupadas. Existe uma dependência funcional quando as colunas agrupadas incluem a chave primária (ou um subconjunto dela) da tabela que contém a coluna não agrupada.

Todas as funções de agregação são calculadas antes de quaisquer expressões escalares na cláusula HAVING ou na lista SELECT. Uma expressão CASE não pode pular o cálculo de uma função de agregação.

Cláusula HAVING

A cláusula HAVING filtra grupos produzidos pelo GROUP BY. A sintaxe é igual à do WHERE, mas o HAVING opera em linhas agrupadas em vez de linhas individuais.

HAVING condition [, ...]

Use o HAVING para filtrar resultados de agregação. Por exemplo, HAVING sum(length) < interval '5 hours' remove grupos onde o comprimento total é de 5 horas ou mais.

CUBE, ROLLUP e GROUPING SETS

Essas subcláusulas estendem o GROUP BY para definir múltiplos conjuntos de agrupamento em uma única consulta.

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

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

GROUPING SETS ( grouping_element [, ...] )

Cláusula DISTINCT

O SELECT DISTINCT remove todas as linhas duplicadas do conjunto de resultados, mantendo uma linha por grupo de duplicatas.

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT

O COUNT DISTINCT conta o número de valores distintos em uma coluna. Um valor que aparece várias vezes é contado apenas uma vez. Valores NULL são excluídos da contagem.

Contagem exata:

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

Contagem aproximada:

O COUNT DISTINCT exato pode consumir muitos recursos. O Hologres oferece approx_count_distinct como uma alternativa aproximada que consome menos recursos:

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

Cláusula UNION

O UNION calcula a união de conjuntos das linhas retornadas por duas instruções SELECT.

select_statement UNION [ ALL | DISTINCT ] select_statement

Cláusula INTERSECT

O INTERSECT calcula a interseção de conjuntos das linhas retornadas por duas instruções SELECT.

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

Cláusula EXCEPT

O EXCEPT calcula a diferença de conjuntos: linhas no resultado do SELECT esquerdo que não estão no resultado do SELECT direito.

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

Cláusula ORDER BY

A cláusula opcional ORDER BY classifica as linhas de resultado por uma ou mais expressões.

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

As linhas são classificadas primeiro pela expressão mais à esquerda, depois pelas expressões subsequentes para desempate. Linhas iguais em todas as expressões retornam em uma ordem dependente da implementação.

Cada expressão pode ser:

• Nome ou número ordinal de uma coluna de saída da lista SELECT.

• Expressão arbitrária formada por valores de colunas de entrada.

O número ordinal refere-se à posição da coluna de saída (da esquerda para a direita), útil para colunas sem nomes exclusivos. Atribuir um nome com AS é geralmente preferível.

Quando uma expressão ORDER BY é um nome simples que corresponde tanto a um nome de coluna de saída quanto a um nome de coluna de entrada, o ORDER BY usa o nome da coluna de saída. Isso difere do GROUP BY, que usa o nome da coluna de entrada na mesma situação. A inconsistência existe para compatibilidade com o padrão SQL.

Direção da classificação:

• Adicione ASC (ascendente, o padrão) ou DESC (descendente) após qualquer expressão.

• Alternativamente, use uma cláusula USING com um operador de ordenação que seja membro menor que ou maior que de uma família de operadores B-tree. ASC é geralmente equivalente a USING <, e DESC a USING >.

Ordenação de NULL:

• NULLS LAST: NULLs são classificados após todos os valores não NULL (padrão para ASC).

• NULLS FIRST: NULLs são classificados antes de todos os valores não NULL (padrão para DESC).

• Por padrão, NULLs são tratados como maiores que valores não NULL.

• Com uma cláusula USING, a posição padrão de NULL depende se o operador é menor que ou maior que.

As opções de ordenação aplicam-se apenas à expressão que elas seguem. Por exemplo, ORDER BY x, y DESC não é o mesmo que ORDER BY x DESC, y DESC.

Limitação: Quando ORDER BY é aplicado a um resultado UNION, INTERSECT ou EXCEPT, apenas nomes de colunas de saída ou números ordinais são permitidos — não expressões.

Cláusula LIMIT

A cláusula LIMIT restringe o número de linhas retornadas. FETCH FIRST é uma sintaxe equivalente do padrão SQL.

LIMIT { count | ALL }
OFFSET start

Sintaxe alternativa do padrão SQL:

FETCH FIRST count ROWS ONLY

Quando ambos são especificados, start linhas são puladas antes de contar count linhas para retorno.

Use uma cláusula ORDER BY com LIMIT para resultados previsíveis. Sem ORDER BY, diferentes execuções podem retornar subconjuntos diferentes — este é um comportamento esperado, não um bug.

O planejador de consultas considera o LIMIT no plano, portanto, diferentes valores de LIMIT e OFFSET podem gerar ordens de linhas diferentes. Force uma ordem determinística com ORDER BY para paginação consistente.

Consulta básica

Recupere todas as colunas de uma tabela:

SELECT * FROM distributors ORDER BY name;

Junção de duas tabelas

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 com 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 (exato e aproximado)

Exato:

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

Aproximado:

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