Consulta SELECT - ClickHouse Documentation

Las consultas SELECT recuperan datos. De forma predeterminada, los datos solicitados se devuelven al cliente, pero, en combinación con INSERT INTO, pueden dirigirse a otra tabla.

Sintaxis

[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 las cláusulas son opcionales, excepto la lista obligatoria de expresiones inmediatamente después de SELECT, que se explica con más detalle a continuación. Los detalles de cada cláusula opcional se describen en secciones independientes, enumeradas en el mismo orden en que se ejecutan:

Cláusula SELECT

Las expresiones especificadas en la cláusula SELECT se calculan una vez finalizadas todas las operaciones de las cláusulas descritas anteriormente. Estas expresiones funcionan como si se aplicaran a filas separadas del resultado. Si las expresiones de la cláusula SELECT contienen funciones de agregación, ClickHouse procesa las funciones de agregación y las expresiones utilizadas como sus argumentos durante la agregación GROUP BY. Si desea incluir todas las columnas en el resultado, utilice el símbolo de asterisco (*). Por ejemplo, SELECT * FROM ....

Selección dinámica de columnas

La selección dinámica de columnas (también conocida como expresión COLUMNS) permite hacer coincidir algunas columnas de un resultado con una expresión regular re2.

COLUMNS('regexp')

Por ejemplo, considere la tabla:

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

La siguiente consulta selecciona datos de todas las columnas cuyo nombre contiene el símbolo a.

SELECT COLUMNS('a') FROM col_names

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

Las columnas seleccionadas no se devuelven en orden alfabético. Puede usar varias expresiones COLUMNS en una consulta y aplicarles funciones. Por ejemplo:

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

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

Cada columna devuelta por la expresión COLUMNS se pasa a la función como un argumento independiente. También puedes pasar otros argumentos a la función si los admite. Ten cuidado al usar funciones. Si una función no admite la cantidad de argumentos que le has pasado, ClickHouse lanza una excepción. Por ejemplo:

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.

En este ejemplo, COLUMNS('a') devuelve dos columnas: aa y ab. COLUMNS('c') devuelve la columna bc. El operador + no puede aplicarse a 3 argumentos, por lo que ClickHouse genera una excepción con el mensaje correspondiente. Las columnas que coinciden con la expresión COLUMNS pueden tener distintos tipos de datos. Si COLUMNS no coincide con ninguna columna y es la única expresión de SELECT, ClickHouse genera una excepción.

Asterisco

Se puede poner un asterisco en cualquier parte de una consulta en lugar de una expresión. Cuando se analiza la consulta, el asterisco se expande a una lista de todas las columnas de la tabla (excepto las columnas MATERIALIZED y ALIAS). Solo hay unos pocos casos en los que se justifica usar un asterisco:

Al crear un volcado de una tabla.
En tablas que contienen solo unas pocas columnas, como las tablas del sistema.
Para obtener información sobre qué columnas hay en una tabla. En este caso, establezca LIMIT 1. Pero es mejor usar la consulta DESC TABLE.
Cuando hay un filtrado intenso sobre un número reducido de columnas mediante PREWHERE.
En subconsultas (ya que las columnas que no son necesarias para la consulta externa se excluyen de las subconsultas).

En todos los demás casos, no recomendamos usar el asterisco, ya que solo aporta las desventajas de un SGBD columnar en lugar de sus ventajas. En otras palabras, no se recomienda usar el asterisco.

Valores extremos

Además de los resultados, también puede obtener los valores mínimos y máximos de las columnas del resultado. Para ello, establezca la opción extremes en 1. Los valores mínimos y máximos se calculan para tipos numéricos, fechas y fechas con hora. Para las demás columnas, se muestran los valores predeterminados. Se calculan dos filas adicionales: una con los mínimos y otra con los máximos. Estas dos filas adicionales se muestran en los formatos XML, JSON*, TabSeparated*, CSV*, Vertical, Template y Pretty*, por separado del resto de las filas. No se muestran en otros formatos. En los formatos JSON* y XML, los valores extremos se muestran en un campo independiente llamado ‘extremes’. En los formatos TabSeparated*, CSV* y Vertical, la fila aparece después del resultado principal, y después de ‘totals’ si está presente. Va precedida de una fila vacía (después de los demás datos). En los formatos Pretty*, la fila se muestra como una tabla independiente después del resultado principal, y después de totals si está presente. En el formato Template, los valores extremos se muestran según la plantilla especificada. Los valores extremos se calculan para las filas anteriores a LIMIT, pero posteriores a LIMIT BY. Sin embargo, al usar LIMIT offset, size, las filas anteriores a offset se incluyen en extremes. En las solicitudes de flujo, el resultado también puede incluir un pequeño número de filas que pasaron por LIMIT.

Notas

Puede utilizar sinónimos (alias AS) en cualquier parte de una consulta. Las cláusulas GROUP BY, ORDER BY y LIMIT BY admiten argumentos posicionales. Para habilitarlos, active el ajuste enable_positional_arguments. Por ejemplo, ORDER BY 1,2 ordenará las filas de la tabla primero por la primera columna y luego por la segunda.

Detalles de implementación

Si la consulta omite las cláusulas DISTINCT, GROUP BY y ORDER BY, así como las subconsultas IN y JOIN, se procesará completamente en flujo, utilizando una cantidad O(1) de RAM. De lo contrario, podría consumir mucha RAM si no se especifican las restricciones adecuadas:

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 obtener más información, consulte la sección “Configuración”. Es posible utilizar ordenación externa (guardando tablas temporales en disco) y agregación externa.

Modificadores de SELECT

Puede utilizar los siguientes modificadores en las consultas SELECT.

Modificador	Descripción
`APPLY`	Permite invocar una función para cada fila devuelta por una expresión de tabla externa en una consulta.
`EXCEPT`	Especifica los nombres de una o más columnas que se excluirán del resultado. Todos los nombres de columna coincidentes se omiten de la salida.
`REPLACE`	Especifica uno o más alias de expresión. Cada alias debe coincidir con un nombre de columna de la sentencia `SELECT *`. En la lista de columnas de salida, la columna que coincide con el alias se sustituye por la expresión de ese `REPLACE`. Este modificador no cambia los nombres ni el orden de las columnas. Sin embargo, puede cambiar el valor y el tipo de valor.

Combinaciones de modificadores

Puedes usar cada modificador por separado o combinarlos. Ejemplos: Uso del mismo modificador varias veces.

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

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

Uso de varios modificadores en una misma consulta.

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

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

SETTINGS en la consulta SELECT

Puede especificar la configuración necesaria directamente en la consulta SELECT. El valor de la configuración se aplica solo a esta consulta y se restablece al valor predeterminado o al valor anterior una vez ejecutada la consulta. Para conocer otras formas de establecer configuraciones, consulte aquí. En el caso de las configuraciones booleanas establecidas en true, puede usar una sintaxis abreviada omitiendo la asignación del valor. Cuando solo se especifica el nombre de la configuración, se establece automáticamente en 1 (true). Ejemplo

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

​Sintaxis

​Cláusula SELECT

​Selección dinámica de columnas

​Asterisco

​Valores extremos

​Notas

​Detalles de implementación

​Modificadores de SELECT

​Combinaciones de modificadores

​SETTINGS en la consulta SELECT