recursos, formato de comando e exemplos de expressões de tabela comuns - MaxCompute: Central de Documentação do Alibaba Cloud

Uma EXPRESSÃO DE TABELA COMUM (CTE) é um conjunto de resultados nomeado temporariamente que simplifica o SQL. O MaxCompute oferece suporte a CTEs SQL padrão para melhorar a legibilidade e a eficiência de execução das instruções SQL. Este tópico descreve os recursos, a sintaxe e os exemplos de CTEs.

Visão geral

Uma CTE pode ser considerada um conjunto de resultados temporário definido no escopo de execução de uma única instrução DML. Assim como uma tabela derivada, a CTE não é armazenada como um objeto e persiste apenas durante a consulta. O uso de CTEs durante o desenvolvimento melhora a legibilidade do SQL e simplifica a manutenção de consultas complexas.
A CTE é uma expressão de cláusula no nível da instrução que começa com WITH, seguida pelo nome da expressão. O MaxCompute oferece suporte a duas formas de CTE:
- CTE NÃO RECURSIVA: Uma CTE que não referencia a si mesma e não itera. Utilize este recurso para simplificar consultas que reutilizam a mesma lógica de subconsulta.
- CTE RECURSIVA: Uma CTE capaz de referenciar a si mesma iterativamente, habilitando recursos de consulta recursiva em SQL. É geralmente usada para percorrer dados hierárquicos, como organogramas.
CTE materializada: Ao definir uma CTE, use o MATERIALIZE HINT na instrução SELECT para armazenar em cache o resultado da CTE em uma tabela temporária. Referências subsequentes leem do cache, evitando problemas de limite de memória em cenários de CTEs profundamente aninhadas e melhorando o desempenho.

CTE NÃO RECURSIVA

Sintaxe

WITH
  <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query>
  )
  [, <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query2>
  )
  , ...]

Parâmetros

Parâmetro	Obrigatório	Descrição
`cte_name`	Sim	Nome da CTE. Deve ser único dentro da cláusula `WITH`. Qualquer referência posterior a `cte_name` na instrução lerá desta CTE.
`col_name`	Não	Nomes das colunas de saída para a CTE. Se omitidos, os nomes das colunas serão herdados da lista `SELECT` em `cte_query`.
`cte_query`	Sim	Uma instrução `SELECT` cujo conjunto de resultados define a CTE.

Exemplo

A consulta a seguir usa UNION ALL para combinar duas operações JOIN. Ambos os joins compartilham a mesma subconsulta à esquerda, que precisaria ser duplicada sem o uso de uma CTE:

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0) b
    ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0) b
    ON a.key = b.key AND b.key IS NOT NULL
) d;

Reescrever com uma CTE elimina a duplicação. A subconsulta a é definida uma única vez e reutilizada por ambos os joins:

WITH
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key = b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key = c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM d UNION ALL SELECT * FROM e;

CTE RECURSIVA

Sintaxe

WITH RECURSIVE <cte_name> [(col_name [, col_name] ...)] AS (
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

Parâmetros

Parâmetro	Obrigatório	Descrição
`RECURSIVE`	Sim	A cláusula da CTE recursiva deve começar com `WITH RECURSIVE`.
`cte_name`	Sim	Nome da CTE. Deve ser único dentro da cláusula `WITH` atual.
`col_name`	Não	Nomes das colunas de saída. Se omitidos, os nomes das colunas serão inferidos a partir de `initial_part`.
`initial_part`	Sim	Uma instrução `SELECT` que produz o conjunto de dados inicial (iteração 0). Não pode referenciar `cte_name`.
`recursive_part`	Sim	Uma instrução `SELECT` que referencia `cte_name` para calcular a próxima iteração com base na anterior.
`UNION ALL`	Sim	Conecta `initial_part` e `recursive_part`. `UNION` (sem `ALL`) não é suportado.

Limitações

CTEs recursivas não podem aparecer em subconsultas IN, EXISTS ou escalares.
O número máximo padrão de iterações é 10. Aumente o limite definindo odps.sql.rcte.max.iterate.num (valor máximo: 100).
Resultados intermediários não são salvos entre iterações. Se a tarefa falhar, a execução reinicia do começo. Para computações recursivas longas, limite o número de iterações ou armazene resultados intermediários em uma tabela temporária.
CTEs recursivas não são suportadas no modo Query Acceleration (MaxQA/MCQA).
- No modo MCQA, se interactive_auto_rerun=true estiver definido, a tarefa retorna ao modo normal. Caso contrário, a tarefa falha.
- No modo MaxQA, o fallback automático não é suportado. O job falha diretamente e deve ser enviado manualmente para um grupo de cotas de processamento em lote para nova tentativa.

Exemplos

Exemplo 1: Defina uma CTE recursiva chamada cte_name.

-- Method 1: Explicitly specify output column names
WITH RECURSIVE cte_name(a, b) AS (
  SELECT 1L, 1L                                   -- initial_part: iteration 0
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5    -- recursive_part: references previous iteration
)
SELECT * FROM cte_name ORDER BY a LIMIT 100;

-- Method 2: Infer column names from initial_part
WITH RECURSIVE cte_name AS (
  SELECT 1L AS a, 1L AS b
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5
)
SELECT * FROM cte_name ORDER BY a LIMIT 100;

Nota

Em recursive_part, defina uma condição de término para evitar um loop infinito. Neste exemplo, WHERE a + 1 <= 5 serve como critério de término. Se a condição WHERE não for atendida, o conjunto de dados gerado na iteração atual estará vazio e a iteração será interrompida.
Se os nomes das colunas de saída não forem especificados explicitamente, o sistema suporta inferência automática. Por exemplo, no Método 2, os nomes das colunas de saída de initial_part são usados como nomes das colunas de saída da CTE recursiva.

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
| 3          | 3          |
| 4          | 4          |
| 5          | 5          |
+------------+------------+

Exemplo 2: CTE recursiva em uma subconsulta (erro de compilação)

CTEs recursivas não são permitidas dentro de subconsultas IN, EXISTS ou escalares. A consulta a seguir falha na compilação:

WITH RECURSIVE cte_name(a, b) AS (
  SELECT 1L, 1L
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
SELECT x, x IN (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

Erro:

FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

Exemplo 3: Percorrer uma hierarquia organizacional

Crie uma tabela employees e insira dados:

CREATE TABLE employees(name STRING, boss_name STRING);
INSERT INTO TABLE employees VALUES
  ('zhang_3', null),
  ('li_4',    'zhang_3'),
  ('wang_5',  'zhang_3'),
  ('zhao_6',  'li_4'),
  ('qian_7',  'wang_5');

Defina uma CTE recursiva chamada company_hierarchy com três colunas de saída: o nome do funcionário, o nome do gerente e o nível na hierarquia:

WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
    SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL
    UNION ALL
    SELECT e.name, e.boss_name, h.level + 1
      FROM employees e
      JOIN company_hierarchy h ON e.boss_name = h.name
)
SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

A execução ocorre da seguinte forma:

Iteração 0 (initial_part): Selecione funcionários onde boss_name IS NULL, atribuindo level = 0. Resultado: ('zhang_3', NULL, 0).
Iteração 1 (recursive_part): Faz join de employees com a tabela de trabalho (iteração 0). A condição e.boss_name = h.name encontra funcionários gerenciados por zhang_3. Resultado: li_4 e wang_5 no level = 1.
Iteração 2: Encontra funcionários gerenciados por li_4 ou wang_5. Resultado: zhao_6 e qian_7 no level = 2.
Iteração 3: Nenhum funcionário tem zhao_6 ou qian_7 como gerentes. A tabela de trabalho fica vazia e a iteração é interrompida.

Resultado:

+---------+-----------+------------+
| name    | boss_name | level      |
+---------+-----------+------------+
| zhang_3 | NULL      | 0          |
| li_4    | zhang_3   | 1          |
| wang_5  | zhang_3   | 1          |
| zhao_6  | li_4      | 2          |
| qian_7  | wang_5    | 2          |
+---------+-----------+------------+

Exemplo 4: Dados cíclicos causam um loop infinito

Insira um registro na tabela employees do Exemplo 3 onde um funcionário é seu próprio gerente.
```
INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');
```
Este registro declara que o gerente de qian_7 é o próprio qian_7. Executar a CTE recursiva definida anteriormente resulta em um loop infinito. O sistema limita o número máximo de iterações. A consulta eventualmente falha.

Erro:
```
FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10
```

CTE materializada

Para CTEs não recursivas, o MaxCompute expande todas as CTEs inline ao gerar um plano de execução. Por exemplo:

WITH v1 AS (SELECT SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

Resultados:

+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Isso equivale a executar SIN(1.0) duas vezes:

SELECT a FROM (SELECT SIN(1.0) AS a)
UNION ALL
SELECT a FROM (SELECT SIN(1.0) AS a);

Em cenários complexos com CTEs profundamente aninhadas, se todas as CTEs forem expandidas nos nós folha mais básicos, o resultado será uma árvore de sintaxe muito grande. Isso pode causar falhas devido ao excesso de nós na árvore de sintaxe durante a geração do plano de execução e levar a problemas de limite de memória. Por exemplo:

WITH
  v1 AS (SELECT 1L AS a, 2L AS b, 3L AS c),
  v2 AS (SELECT * FROM v1 UNION ALL SELECT * FROM v1 UNION ALL SELECT * FROM v1),
  v3 AS (SELECT * FROM v2 UNION ALL SELECT * FROM v2 UNION ALL SELECT * FROM v2),
  v4 AS (SELECT * FROM v3 UNION ALL SELECT * FROM v3 UNION ALL SELECT * FROM v3),
  v5 AS (SELECT * FROM v4 UNION ALL SELECT * FROM v4 UNION ALL SELECT * FROM v4),
  v6 AS (SELECT * FROM v5 UNION ALL SELECT * FROM v5 UNION ALL SELECT * FROM v5),
  v7 AS (SELECT * FROM v6 UNION ALL SELECT * FROM v6 UNION ALL SELECT * FROM v6),
  v8 AS (SELECT * FROM v7 UNION ALL SELECT * FROM v7 UNION ALL SELECT * FROM v7),
  v9 AS (SELECT * FROM v8 UNION ALL SELECT * FROM v8 UNION ALL SELECT * FROM v8)
SELECT * FROM v9;

Para resolver esse problema, o MaxCompute oferece o recurso de CTE materializada, que armazena em cache os resultados da computação da CTE para referência pelo SQL fora da cláusula WITH, sem expansão total. Esse mecanismo evita efetivamente problemas de limite de memória causados pela expansão de CTEs aninhadas e melhora o desempenho das instruções CTE.

Exemplo de uso

Adicione a dica /*+ MATERIALIZE */ ao SELECT de nível superior de uma CTE não recursiva para armazenar seu resultado em uma tabela temporária. Referências subsequentes leem do cache em vez de executar a consulta novamente:

WITH v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- Result:
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Quando a dica está ativa, a aba Job Details no LogView mostra múltiplos Fuxi Jobs, confirmando que o resultado intermediário foi armazenado.

Limitações

A dica MATERIALIZE deve ser aplicada à instrução SELECT de nível superior de uma CTE não recursiva. CTEs recursivas não precisam da dica MATERIALIZE.
- Exemplo incorreto: Na CTE a seguir, a instrução de nível superior é UNION em vez de SELECT, portanto a dica MATERIALIZE não surte efeito.
```
WITH v1 AS (
  SELECT /*+ MATERIALIZE */ SIN(1.0) AS a
  UNION ALL
  SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
- Exemplo correto: Reescreva o exemplo incorreto acima envolvendo-o em uma subconsulta.
```
WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ * FROM 
           (SELECT SIN(1.0) AS a
            UNION ALL 
            SELECT SIN(1.0) AS a)
        ) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
Se a CTE usar uma função não determinística, como RAND ou uma UDF Java/Python não determinística, a materialização da CTE armazena em cache uma única avaliação da função. Referências subsequentes retornam o valor em cache, o que altera o comportamento semântico de consultas que esperam valores aleatórios independentes por chamada.
CTEs materializadas não são suportadas em MCQA(Query Acceleration 1.0) -deprecated. Se a tarefa for executada no modo MCQA com interactive_auto_rerun=true, ela retornará ao modo normal. Caso contrário, a tarefa falha.