Aprenda as etapas para escrever consultas recursivas em SQL usando expressões de tabela comuns e UNION ALL. Descubra como limitar, classificar e otimizar os resultados recursivos.

A simple example of a company structure can be written like this in SQL Server: declare @emp table (EmployeeID INT PRIMARY KEY,EmployeeName NVARCHAR(50), ManagerID INT) INSERT INTO @emp (EmployeeID, EmployeeName, ManagerID) VALUES (1, 'John', NULL),(2, 'Alice', 1),(3, 'Bob', 1),(4, 'Charlie', 2),(5, 'David', 2),(6, 'Eva', 3),(7, 'Frank', 3); WITH EmployeeHierarchyCTE AS ( SELECT EmployeeID,EmployeeName,ManagerID, 0 AS Level FROM @emp WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1 AS Level FROM @emp e INNER JOIN EmployeeHierarchyCTE eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchyCTE; Note the anchor to head (managerID is null)

Quais são as etapas para implementar consultas recursivas no SQL?

Alimentado por IA e pela comunidade do LinkedIn

As consultas recursivas são uma maneira poderosa de recuperar dados de tabelas hierárquicas ou autorreferenciais no SQL. Eles permitem que você percorra uma árvore ou uma estrutura de gráfico e retorne os resultados em uma única consulta, sem usar loops ou cursores. Neste artigo, você aprenderá as etapas para implementar consultas recursivas no SQL usando expressões de tabela comuns (CTEs) e a operadora UNION ALL.

Principais especialistas neste artigo

Selecionados pela comunidade a partir de 7 contribuições. Saiba mais

1 O que é uma consulta recursiva?

Uma consulta recursiva é um tipo de consulta que usa uma CTE para definir um caso base e um caso recursivo. Um CTE é um conjunto de resultados temporários que pode ser referenciado na mesma consulta. O caso base é o conjunto inicial de linhas que satisfaz uma condição. O caso recursivo é o conjunto de linhas que é derivado do conjunto anterior aplicando uma junção ou um filtro. A consulta recursiva repete o caso recursivo até que nenhuma outra linha seja gerada ou um limite seja atingido.

Adicione sua opinião

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Denunciar contribuição
CTE queries are a got to for making representations of hierarchical data. For example an org structure. The key for formulating is to anchor against the top record - so in this example the CEO, then resolve the reports to the CEO, etc as part of a top down traverse. It is important to set a recursion limit. It is useful to increment a level counter so the resulting query shows the recursion depth.

Traduzido

Gostei
Drashti Bhatt

Data Scientist | Machine Learning
Denunciar contribuição
Create Recursive CTE: Use WITH RECURSIVE to define a recursive common table expression (CTE). Base Query: Write a base query selecting the initial set of data with a specified condition (e.g., where parent_id is NULL). Recursive Query: In the recursive part, join the table with the recursive CTE to fetch related rows. Union Base and Recursive Queries: Combine the base and recursive queries using UNION ALL within the CTE. Select from Recursive CTE: Use a SELECT statement outside the CTE to retrieve the final result set. For optimizing the performance of recursive queries in SQL, tips are: Indexes, Analyze Execution Plan, Limit Result Set, Avoid Excessive Recursion, Use Proper Data Types, Partitioning (if applicable), and Batch Processing.

Traduzido

Gostei

2 Como escrever um CTE recursivo?

Para escrever uma CTE recursiva, você precisa usar a palavra-chave WITH seguida pelo nome da CTE e os nomes das colunas. Em seguida, você precisa usar o operador UNION ALL para combinar o caso base e o caso recursivo. O caso base e o caso recursivo devem ter o mesmo número e tipo de colunas. O caso recursivo deve se referir ao nome CTE na cláusula FROM. Por exemplo, a consulta a seguir retorna os ancestrais de um determinado funcionário em uma organização:

WITH ancestor (emp_id, emp_name, mgr_id, level) AS (
  -- base case: select the employee with emp_id = 100
  SELECT emp_id, emp_name, mgr_id, 1 AS level
  FROM employee
  WHERE emp_id = 100
  UNION ALL
  -- recursive case: select the manager of the previous employee
  SELECT e.emp_id, e.emp_name, e.mgr_id, a.level + 1 AS level
  FROM employee e
  JOIN ancestor a ON e.emp_id = a.mgr_id
)
-- select the CTE result
SELECT *
FROM ancestor;

Adicione sua opinião

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Denunciar contribuição
A simple example of a company structure can be written like this in SQL Server: declare @emp table (EmployeeID INT PRIMARY KEY,EmployeeName NVARCHAR(50), ManagerID INT) INSERT INTO @emp (EmployeeID, EmployeeName, ManagerID) VALUES (1, 'John', NULL),(2, 'Alice', 1),(3, 'Bob', 1),(4, 'Charlie', 2),(5, 'David', 2),(6, 'Eva', 3),(7, 'Frank', 3); WITH EmployeeHierarchyCTE AS ( SELECT EmployeeID,EmployeeName,ManagerID, 0 AS Level FROM @emp WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1 AS Level FROM @emp e INNER JOIN EmployeeHierarchyCTE eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchyCTE; Note the anchor to head (managerID is null)

Traduzido

Gostei

3 Como controlar a profundidade da recursão?

Às vezes, convém limitar o número de iterações ou o nível de recursão em uma consulta recursiva. Você pode fazer isso usando uma cláusula WHERE ou uma opção MAXRECURSION. Uma cláusula WHERE pode filtrar as linhas que excedem uma determinada condição, como nível > 5. Uma opção MAXRECURSÃO pode especificar o número máximo de recursões permitidas, como MAXRECURSÃO 10. Se a recursão exceder o limite, a consulta retornará um erro ou um aviso, dependendo do sistema de banco de dados.

Adicione sua opinião

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Denunciar contribuição
To set the depth of recursion in SQL Server there is the command OPTION (MAXRECURSION <number>) To enable bottomless set to 0

Traduzido

Gostei

4 Como ordenar o resultado recursivo?

Um dos desafios das consultas recursivas é ordenar o resultado de forma significativa. Como o operador UNION ALL não garante nenhum pedido, talvez seja necessário usar uma cláusula ORDER BY ou uma função de classificação para classificar as linhas. Uma cláusula ORDER BY pode classificar as linhas por uma ou mais colunas, como ASC de nível ou emp_nome DESC. Uma função de classificação pode atribuir uma classificação ou um número de linha a cada linha com base em uma partição e uma ordem, como ROW_NÚMERO() SOBRE (PARTIÇÃO POR NÍVEL ORDEM POR EMP_nome). Em seguida, você pode usar a classificação ou o número da linha para ordenar o resultado.

Adicione sua opinião

5 Como otimizar o desempenho recursivo?

As consultas recursivas podem ser caras em termos de recursos e tempo, especialmente se a profundidade de recursão for alta ou o tamanho dos dados for grande. Para otimizar o desempenho recursivo, você pode usar algumas técnicas, como indexação, filtragem, cache e particionamento. A indexação pode acelerar as operações de junção e de filtro no caso recursivo. A filtragem pode reduzir o número de linhas no caso base e no caso recursivo. O cache pode armazenar os resultados intermediários da CTE na memória ou no disco. O particionamento pode dividir os dados em subconjuntos menores e independentes que podem ser processados em paralelo.

Adicione sua opinião

ABDUL QADEER

Data Analyst | BI DEVELOPER | POWER BI | SQL | SSIS | Python
Denunciar contribuição
Ensure that columns used in WHERE clauses are appropriately indexed to speed up the filtering process. And indexing columns which are involved in the JOIN conditions can significantly improve the performance of recursive queries.

Traduzido

Gostei
Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Denunciar contribuição
To optimise performance ensure there is an index on the on the join fields/foreign key. Also set the recursion depth to prevent it blowing out.

Traduzido

Gostei

6 Veja o que mais considerar

Este é um espaço para compartilhar exemplos, histórias ou insights que não se encaixam em nenhuma das seções anteriores. O que mais gostaria de acrescentar?

Adicione sua opinião

Hoang Le

Technical Business Analyst | Data Analyst | Product Specialist | MBA, B.Eng in Computer Science
(editado)
Denunciar contribuição
Recursive query would be a very powerful tool for manipulating data where straightforward aggregation functions (e.g. sum, average, max) can't solve the need. Typical examples would be to find the hierarchy information in the same table (like the employee case shown in this article), or splitting the data records based on certain conditions (e.g. the continuous phone usage splitting into blocks of 15 minutes of the same user). Mastering this skill will help data analysts handle more complex data analysis scenarios.

Traduzido

Gostei

Desenvolvimento de banco de dados

+ Siga

Classificar este artigo

Criamos este artigo com a ajuda da IA. O que você achou?

É ótimo Não é muito bom

Denunciar este artigo

Ver todos

Quais são as etapas para implementar consultas recursivas no SQL?

1

2

3

4

5

6

1 O que é uma consulta recursiva?

2 Como escrever um CTE recursivo?

3 Como controlar a profundidade da recursão?

4 Como ordenar o resultado recursivo?

5 Como otimizar o desempenho recursivo?

6 Veja o que mais considerar

Desenvolvimento de banco de dados

Classificar este artigo

Agradecemos seu feedback

Outros artigos sobre Desenvolvimento de banco de dados

Leitura mais relevante

Quais são as etapas para implementar consultas recursivas no SQL?

1

2

3

4

5

6

1 O que é uma consulta recursiva?

2 Como escrever um CTE recursivo?

3 Como controlar a profundidade da recursão?

4 Como ordenar o resultado recursivo?

5 Como otimizar o desempenho recursivo?

6 Veja o que mais considerar

Desenvolvimento de banco de dados

Classificar este artigo

Agradecemos seu feedback

Conhecer outras competências