🔍 O que é a CTE no ClickHouse?
No ClickHouse é possível realizar a utilização de Common Table Expressions (CTE), que funcionam como subconsultas nomeadas reutilizáveis dentro de uma consulta SELECT.
O ClickHouse substitui automaticamente as expressões definidas na CTE em todos os pontos onde são referenciadas ao longo da consulta. Isso evita a repetição de código e melhora a legibilidade e a manutenção das consultas.
📌 Características de uma CTE no ClickHouse
✅ Reutilização de Código: Permite evitar a repetição de subconsultas complexas.
✅ Escopo:As CTEs são visíveis apenas dentro da consulta atual e em suas subconsultas.
✅ Sem Recursão: O ClickHouse impede que uma CTE referencie a si mesma diretamente.
✅ Execução Independente: Cada referência a um CTE executa sua subconsulta novamente, podendo resultar em valores diferentes em execuções consecutivas se os dados forem dinâmicos.
🛠 Exemplo de Uso
WITH cte_numbers AS (
SELECT num
FROM generateRandom('num UInt64', NULL)
LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);
🎯 O que acontece aqui? Como os CTEs no ClickHouse executam a subconsulta a cada referência, os valores gerados podem variar entre diferentes chamadas dentro da mesma consulta. Por isso, o resultado dessa consulta pode variar.
🏗 Exemplos Práticos
📌 Exemplo 1: Usando Expressão Constante como "Variável"
WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
EventDate = toDate(ts_upper_bound) AND
EventTime <= ts_upper_bound;
🎯 Vantagem: Evita a repetição da mesma expressão ao longo da consulta.
📌 Exemplo 2: Usando CTE para Evitar Redundância na Cláusula SELECT
WITH sum(bytes) AS s
SELECT
formatReadableSize(s),
table
FROM system.parts
GROUP BY table
ORDER BY s;
🎯 Vantagem: Evita duplicar a expressão sum(bytes), melhorando a legibilidade.
📌 Exemplo 3: Usando CTE para Subconsulta Escalar
WITH (
SELECT sum(bytes)
FROM system.parts
WHERE active
) AS total_disk_usage
SELECT
(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;
🎯 Caso de Uso: Obtém o percentual de ocupação de disco de cada tabela em relação ao total.
📌 Exemplo 4: Reutilizando Expressão em uma Subconsulta
WITH test1 AS (
SELECT i + 1, j + 1
FROM test1)
SELECT * FROM test1;
🎯 Aplicabilidade: ÚTil para cenários em que é necessário processar os dados antes da consulta final.
🎯 Conclusão
A utilização de CTE no ClickHouse é uma ferramenta poderosa para estruturar consultas SQL de forma mais clara e eficiente. Embora os CTEs no ClickHouse não sejam cacheados como em outros bancos de dados, sua flexibilidade permite organizar consultas complexas e evitar a repetição de código.
WITH Clause. Clickhouse.com. Disponível em:
https://clickhouse.com/docs/en/sql-reference/statements/select/with. Acesso em: 05 Jan 2024.