Erfahren Sie mehr über die Schritte zum Schreiben rekursiver Abfragen in SQL mithilfe allgemeiner Tabellenausdrücke und UNION ALL. Erfahren Sie, wie Sie die rekursiven Ergebnisse einschränken, sortieren und optimieren können.

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)

Was sind die Schritte zum Implementieren rekursiver Abfragen in SQL?

Bereitgestellt von KI und der LinkedIn Community

Rekursive Abfragen sind eine leistungsstarke Methode zum Abrufen von Daten aus hierarchischen oder selbstreferenziellen Tabellen in SQL. Sie ermöglichen es Ihnen, eine Struktur oder eine Diagrammstruktur zu durchlaufen und die Ergebnisse in einer einzigen Abfrage zurückzugeben, ohne Schleifen oder Cursor zu verwenden. In diesem Artikel lernen Sie die Schritte zum Implementieren rekursiver Abfragen in SQL mithilfe gängiger Tabellenausdrücke kennen (CTEs) und dem UNION ALL-Operator.

Top-Expert:innen in diesem Artikel

Von der Community unter 7 Beiträgen ausgewählt. Mehr erfahren

1 Was ist eine rekursive Abfrage?

Eine rekursive Abfrage ist ein Abfragetyp, der einen CTE verwendet, um einen Basisfall und einen rekursiven Fall zu definieren. Ein CTE ist ein temporäres Resultset, auf das innerhalb derselben Abfrage verwiesen werden kann. Der Basisfall ist der anfängliche Satz von Zeilen, der eine Bedingung erfüllt. Der rekursive Fall ist der Satz von Zeilen, der durch Anwenden einer Verknüpfung oder eines Filters vom vorherigen Satz abgeleitet wird. Die rekursive Abfrage wiederholt den rekursiven Fall, bis keine Zeilen mehr generiert werden oder ein Grenzwert erreicht ist.

Fügen Sie Ihre Sichtweise hinzu

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Beitrag melden
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.

Übersetzt

Gefällt mir
Drashti Bhatt

Data Scientist | Machine Learning
Beitrag melden
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.

Übersetzt

Gefällt mir

2 Wie schreibt man einen rekursiven CTE?

Um einen rekursiven CTE zu schreiben, müssen Sie das Schlüsselwort WITH gefolgt vom Namen des CTE und den Spaltennamen verwenden. Anschließend müssen Sie den UNION ALL-Operator verwenden, um den Basisfall und den rekursiven Fall zu kombinieren. Der Basisfall und der rekursive Fall müssen die gleiche Anzahl und den gleichen Typ von Spalten aufweisen. Die rekursive Groß-/Kleinschreibung muss sich auf den CTE-Namen in der FROM-Klausel beziehen. Die folgende Abfrage gibt z. B. die Vorfahren eines bestimmten Mitarbeiters in einer Organisation zurück:

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;

Fügen Sie Ihre Sichtweise hinzu

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Beitrag melden
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)

Übersetzt

Gefällt mir

3 Wie steuert man die Rekursionstiefe?

Es kann vorkommen, dass Sie die Anzahl der Iterationen oder den Grad der Rekursion in einer rekursiven Abfrage begrenzen möchten. Sie können dies tun, indem Sie eine WHERE-Klausel oder eine MAXRECURSION-Option verwenden. Eine WHERE-Klausel kann die Zeilen herausfiltern, die eine bestimmte Bedingung überschreiten, z. B. level > 5. Eine MAXRECURSION-Option kann die maximal zulässige Anzahl von Rekursionen angeben, z. B. MAXRECURSION 10. Wenn die Rekursion den Grenzwert überschreitet, gibt die Abfrage je nach Datenbanksystem einen Fehler oder eine Warnung zurück.

Fügen Sie Ihre Sichtweise hinzu

Bevan Ward

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

Übersetzt

Gefällt mir

4 Wie ordnet man das rekursive Ergebnis?

Eine der Herausforderungen bei rekursiven Abfragen besteht darin, das Ergebnis sinnvoll zu ordnen. Da der UNION ALL-Operator keine Reihenfolge garantiert, müssen Sie möglicherweise eine ORDER BY-Klausel oder eine Rangfolgefunktion verwenden, um die Zeilen zu sortieren. Eine ORDER BY-Klausel kann die Zeilen nach einer oder mehreren Spalten sortieren, z. B. Ebene ASC oder emp_Name DESC. Eine Rangfolgefunktion kann jeder Zeile basierend auf einer Partition und einer Reihenfolge, z. B. ROW, einen Rang oder eine Zeilennummer zuweisen_ZAHL() ÜBER (PARTITION NACH EBENE REIHENFOLGE NACH emp_Name). Sie können dann den Rang oder die Zeilennummer verwenden, um das Ergebnis zu sortieren.

Fügen Sie Ihre Sichtweise hinzu

5 Wie kann die rekursive Leistung optimiert werden?

Rekursive Abfragen können in Bezug auf Ressourcen und Zeit teuer sein, insbesondere wenn die Rekursionstiefe hoch oder die Datengröße groß ist. Um die rekursive Leistung zu optimieren, können Sie einige Techniken wie Indizierung, Filterung, Zwischenspeicherung und Partitionierung verwenden. Die Indizierung kann die Verknüpfung und die Filtervorgänge im rekursiven Fall beschleunigen. Durch Filtern kann die Anzahl der Zeilen im Basisfall und im rekursiven Fall reduziert werden. Durch die Zwischenspeicherung können die Zwischenergebnisse des CTE im Arbeitsspeicher oder auf dem Datenträger gespeichert werden. Durch die Partitionierung können die Daten in kleinere und unabhängige Teilmengen unterteilt werden, die parallel verarbeitet werden können.

Fügen Sie Ihre Sichtweise hinzu

ABDUL QADEER

Data Analyst | BI DEVELOPER | POWER BI | SQL | SSIS | Python
Beitrag melden
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.

Übersetzt

Gefällt mir
Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Beitrag melden
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.

Übersetzt

Gefällt mir

6 Hier erfahren Sie, was Sie sonst noch beachten sollten

Dies ist ein Bereich, in dem Beispiele, Geschichten oder Erkenntnisse geteilt werden können, die in keinen der vorherigen Abschnitte passen. Was möchten Sie noch hinzufügen?

Fügen Sie Ihre Sichtweise hinzu

Hoang Le

Technical Business Analyst | Data Analyst | Product Specialist | MBA, B.Eng in Computer Science
(bearbeitet)
Beitrag melden
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.

Übersetzt

Gefällt mir

Datenbankentwicklung

+ Folgen

Diesen Artikel bewerten

Wir haben diesen Artikel mithilfe von KI erstellt. Wie finden Sie ihn?

Sehr gut Geht so

Diesen Artikel melden

Alle anzeigen

Was sind die Schritte zum Implementieren rekursiver Abfragen in SQL?

1

2

3

4

5

6

1 Was ist eine rekursive Abfrage?

2 Wie schreibt man einen rekursiven CTE?

3 Wie steuert man die Rekursionstiefe?

4 Wie ordnet man das rekursive Ergebnis?

5 Wie kann die rekursive Leistung optimiert werden?

6 Hier erfahren Sie, was Sie sonst noch beachten sollten

Datenbankentwicklung

Diesen Artikel bewerten

Vielen Dank für Ihr Feedback

Weitere Artikel zu Datenbankentwicklung

Relevantere Lektüre

Was sind die Schritte zum Implementieren rekursiver Abfragen in SQL?

1

2

3

4

5

6

1 Was ist eine rekursive Abfrage?

2 Wie schreibt man einen rekursiven CTE?

3 Wie steuert man die Rekursionstiefe?

4 Wie ordnet man das rekursive Ergebnis?

5 Wie kann die rekursive Leistung optimiert werden?

6 Hier erfahren Sie, was Sie sonst noch beachten sollten

Datenbankentwicklung

Diesen Artikel bewerten

Vielen Dank für Ihr Feedback

Andere Kenntnisse ansehen