Consulta SELECT

Documentação da consulta SELECT

As consultas SELECT recuperam dados. Por padrão, os dados solicitados são retornados ao cliente, mas, quando usadas em conjunto com INSERT INTO, também podem ser encaminhados para outra tabela.

​

Sintaxe

[WITH expr_list(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table [(alias1 [, alias2 ...])] (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[WINDOW window_expr_list]
[QUALIFY expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

Todas as cláusulas são opcionais, exceto a lista obrigatória de expressões imediatamente após SELECT, que é abordada com mais detalhes abaixo.

Os detalhes de cada cláusula opcional são abordados em seções separadas, listadas na mesma ordem em que são executadas:

• cláusula WITH
• cláusula SELECT
• cláusula DISTINCT
• cláusula FROM
• cláusula SAMPLE
• cláusula JOIN
• cláusula PREWHERE
• cláusula WHERE
• cláusula WINDOW
• cláusula GROUP BY
• cláusula LIMIT BY
• cláusula HAVING
• cláusula QUALIFY
• cláusula LIMIT
• cláusula OFFSET
• cláusula UNION
• cláusula INTERSECT
• cláusula EXCEPT
• cláusula INTO OUTFILE
• cláusula FORMAT

​

Cláusula SELECT

As expressões especificadas na cláusula SELECT são calculadas após a conclusão de todas as operações nas cláusulas descritas acima. Essas expressões funcionam como se fossem aplicadas a linhas separadas no resultado. Se as expressões na cláusula SELECT contiverem funções de agregação, o ClickHouse processa as funções de agregação e as expressões usadas como seus argumentos durante a agregação GROUP BY.

Se você quiser incluir todas as colunas no resultado, use o símbolo de asterisco (*). Por exemplo, SELECT * FROM ....

​

Seleção dinâmica de colunas

A seleção dinâmica de colunas (também conhecida como expressão COLUMNS) permite corresponder algumas colunas em um resultado usando uma re2 expressão regular.

COLUMNS('regexp')

Por exemplo, considere a tabela:

CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog

A consulta a seguir seleciona dados de todas as colunas que contêm o símbolo a no nome.

SELECT COLUMNS('a') FROM col_names

┌─aa─┬─ab─┐
│  1 │  1 │
└────┴────┘

As colunas selecionadas não são retornadas em ordem alfabética.

Você pode usar várias expressões COLUMNS em uma consulta e aplicar funções a elas.

Por exemplo:

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names

┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐
│  1 │  1 │  1 │ Int8           │
└────┴────┴────┴────────────────┘

Cada coluna retornada pela expressão COLUMNS é passada para a função como um argumento separado. Além disso, você também pode passar outros argumentos para a função, se ela oferecer suporte a eles. Tenha cuidado ao usar funções. Se uma função não oferecer suporte ao número de argumentos que você passou para ela, o ClickHouse lança uma exceção.

Por exemplo:

SELECT COLUMNS('a') + COLUMNS('c') FROM col_names

Received exception from server (version 19.14.1):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.

Neste exemplo, COLUMNS('a') retorna duas colunas: aa e ab. COLUMNS('c') retorna a coluna bc. O operador + não pode ser aplicado a 3 argumentos, então o ClickHouse lança uma exceção com a mensagem correspondente.

As colunas que correspondem à expressão COLUMNS podem ter tipos de dados diferentes. Se COLUMNS não corresponder a nenhuma coluna e for a única expressão em SELECT, o ClickHouse lança uma exceção.

​

Asterisco

Você pode usar um asterisco em qualquer parte de uma consulta no lugar de uma expressão. Quando a consulta é analisada, o asterisco é expandido para uma lista de todas as colunas da tabela (exceto as colunas MATERIALIZED e ALIAS). Há apenas alguns casos em que o uso de um asterisco se justifica:

• Ao criar um dump de uma tabela.
• Em tabelas com poucas colunas, como as tabelas de sistema.
• Para obter informações sobre quais colunas existem em uma tabela. Nesse caso, defina LIMIT 1. Mas é melhor usar a consulta DESC TABLE.
• Quando há uma filtragem forte em um número pequeno de colunas usando PREWHERE.
• Em subconsultas (já que as colunas que não são necessárias para a consulta externa são excluídas das subconsultas).

Em todos os outros casos, não recomendamos usar o asterisco, pois ele só traz as desvantagens de um SGBD colunar, em vez das vantagens. Em outras palavras, não é recomendável usar o asterisco.

​

Valores extremos

Além dos resultados, você também pode obter os valores mínimo e máximo das colunas de resultado. Para isso, defina a configuração extremes como 1. Os valores mínimos e máximos são calculados para tipos numéricos, datas e data/hora. Para as demais colunas, são exibidos os valores padrão.

Duas linhas extras são calculadas — os mínimos e os máximos, respectivamente. Essas duas linhas extras são exibidas nos formatos XML, JSON*, TabSeparated*, CSV*, Vertical, Template e Pretty*, separadamente das demais linhas. Elas não são exibidas em outros formatos.

Nos formatos JSON* e XML, os valores extremos são exibidos em um campo separado chamado ‘extremes’. Nos formatos TabSeparated*, CSV* e Vertical, a linha aparece após o resultado principal e, se houver, após ‘totals’. Ela é precedida por uma linha vazia (depois dos demais dados). Nos formatos Pretty*, a linha é exibida como uma tabela separada após o resultado principal e, se houver, após totals. No formato Template, os valores extremos são exibidos de acordo com o template especificado.

Os valores extremos são calculados para as linhas antes de LIMIT, mas depois de LIMIT BY. No entanto, ao usar LIMIT offset, size, as linhas antes de offset são incluídas em extremes. Em requisições de stream, o resultado também pode incluir um pequeno número de linhas que passaram por LIMIT.

​

Observações

Você pode usar sinônimos (aliases AS) em qualquer parte de uma consulta.

As cláusulas GROUP BY, ORDER BY e LIMIT BY podem aceitar argumentos posicionais. Para habilitar isso, ative a configuração enable_positional_arguments. Assim, por exemplo, ORDER BY 1,2 ordenará as linhas da tabela pela primeira e depois pela segunda coluna.

​

Detalhes de implementação

Se a consulta omitir as cláusulas DISTINCT, GROUP BY e ORDER BY e as subconsultas IN e JOIN, ela será processada inteiramente em fluxo, usando O(1) de RAM. Caso contrário, a consulta poderá consumir muita RAM se os limites adequados não forem especificados:

• max_memory_usage
• max_rows_to_group_by
• max_rows_to_sort
• max_rows_in_distinct
• max_bytes_in_distinct
• max_rows_in_set
• max_bytes_in_set
• max_rows_in_join
• max_bytes_in_join
• max_bytes_before_external_sort
• max_bytes_ratio_before_external_sort
• max_bytes_before_external_group_by
• max_bytes_ratio_before_external_group_by

Para mais informações, consulte a seção “Configurações”. É possível usar ordenação externa (salvando tabelas temporárias em disco) e agregação externa.

​

Modificadores do SELECT

Você pode usar os seguintes modificadores em consultas SELECT.

Modificador	Descrição
APPLY	Permite invocar uma função para cada linha retornada por uma expressão de tabela externa da consulta.
EXCEPT	Especifica os nomes de uma ou mais colunas a serem excluídas do resultado. Todos os nomes de colunas correspondentes são omitidos da saída.
REPLACE	Especifica um ou mais aliases de expressão. Cada alias deve corresponder ao nome de uma coluna da instrução SELECT *. Na lista de colunas de saída, a coluna que corresponde ao alias é substituída pela expressão na cláusula REPLACE. Esse modificador não altera os nomes nem a ordem das colunas. No entanto, ele pode alterar o valor e o tipo do valor.

​

Combinações de modificadores

Você pode usar cada modificador separadamente ou combiná-los.

Exemplos:

Uso do mesmo modificador várias vezes.

SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers;

┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│                        2 │                        3 │
└──────────────────────────┴──────────────────────────┘

Uso de vários modificadores em uma única consulta.

SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;

┌─sum(plus(i, 1))─┬─sum(k)─┐
│             222 │    347 │
└─────────────────┴────────┘

​

SETTINGS na consulta SELECT

Você pode especificar as configurações necessárias diretamente na consulta SELECT. O valor da configuração é aplicado apenas a essa consulta e, após a execução, volta ao valor padrão ou ao valor anterior.

Para conhecer outras formas de definir configurações, veja aqui.

Para configurações booleanas definidas como true, você pode usar uma sintaxe abreviada, omitindo a atribuição de valor. Quando apenas o nome da configuração é especificado, ela é automaticamente definida como 1 (true).

Exemplo

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;