Use the WITH clause in a SELECT statement - AnalyticDB: Central de Documentação do Alibaba Cloud

A cláusula WITH define uma expressão de tabela comum (CTE): um conjunto de resultados temporário e nomeado, com escopo limitado a uma única instrução SQL. Referencie a CTE como uma tabela na instrução SELECT subsequente. As CTEs simplificam consultas complexas ao substituir subconsultas profundamente aninhadas por blocos legíveis e reutilizáveis.

Funcionamento

Todas as CTEs em uma cláusula WITH são definidas antes da execução da consulta principal. A subconsulta na cláusula WITH executa apenas uma vez, o que pode melhorar o desempenho. Com a otimização de execução de CTE ativada, uma CTE referenciada várias vezes executa exatamente uma vez; todas as referências leem desse resultado compartilhado.

Sintaxe

WITH
  cte_name AS (subquery)
  [, cte_name2 AS (subquery2) ...]
SELECT ...
FROM cte_name [, cte_name2 ...];

Separe múltiplas CTEs com vírgula.
Cada CTE deve ser seguida por outra CTE ou pela instrução SQL principal.
CTEs definidas anteriormente na lista podem ser referenciadas por CTEs definidas posteriormente.

Observações de uso

Instruções CTE não oferecem suporte a paginação.
A cláusula WITH é compatível com instruções SELECT.

Exemplos

Substituir uma subconsulta aninhada

As duas consultas abaixo são equivalentes. A versão com CTE facilita a leitura e a manutenção.

-- Nested subquery
SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

-- Equivalent CTE
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

Definir múltiplas CTEs

Use uma única cláusula WITH para definir várias CTEs e aplicar JOIN entre elas na consulta principal.

WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.a = t2.a;

Encadear CTEs

Referencie CTEs anteriores dentro da mesma cláusula WITH.

WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;

Otimização de execução de CTE

Clusters do AnalyticDB for MySQL com versão de kernel 3.1.9.3 ou posterior oferecem suporte à otimização de execução de CTE. Esse recurso vem desativado por padrão. Para ativá-lo, defina o item de configuração CTE_EXECUTION_MODE. Quando ativada, uma subconsulta CTE referenciada várias vezes executa apenas uma vez; todas as referências leem desse resultado compartilhado, evitando computação redundante.

Importante

Ativar a otimização de execução de CTE pode reduzir o desempenho de algumas consultas. Caso observe uma queda significativa de performance, desative a otimização.

Ativar a otimização de execução de CTE

Para uma consulta específica, adicione a dica /*cte_execution_mode=shared*/ antes da instrução:

/*cte_execution_mode=shared*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

Para todas as consultas, execute:

SET adb_config cte_execution_mode=shared;

Desativar a otimização de execução de CTE

Para uma consulta específica, adicione a dica /*cte_execution_mode=inline*/ antes da instrução:

/*cte_execution_mode=inline*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

Para todas as consultas, execute:

SET adb_config cte_execution_mode=inline;