Lære Recursive CTEs for Sequences and Paths | Recursive CTEs and Hierarchical Data

Sveip for å vise menyen

Recursive Common Table Expressions (CTEs) unlock powerful techniques for working with sequences and traversing paths in hierarchical or graph-like data. By leveraging their ability to reference themselves, you can generate series of numbers or dates, and also perform complex traversals such as finding all dependencies in a project plan or all possible paths in a network. This chapter explores how recursive CTEs can be applied to these scenarios, providing you with practical approaches to sequence generation and pathfinding.

To generate a sequence of numbers or dates, recursive CTEs start with an anchor row and then repeatedly add new rows by incrementing the value until a stopping condition is met. This is especially useful when you need to build a range of dates for reporting, scheduling, or filling gaps in your data.


              12345678910111213
            
-- Generate a calendar of dates for July 2024
WITH RECURSIVE calendar AS (
    -- Anchor member: first day of July 2024
    SELECT DATE '2024-07-01' AS calendar_date
    UNION ALL
    -- Recursive member: add one day to the previous date
    SELECT calendar_date + INTERVAL '1 day'
    FROM calendar
    WHERE calendar_date + INTERVAL '1 day' <= DATE '2024-07-31'
)
SELECT calendar_date
FROM calendar
ORDER BY calendar_date;

This query constructs a list of all dates in July 2024. The anchor member initializes the sequence with July 1st. The recursive member adds one day at each step, continuing until the last day of the month is reached. This pattern can be adapted to generate numeric sequences or other types of series as needed. Recursive CTEs are not limited to simple sequences—they are also invaluable for exploring relationships and dependencies within your data.

When dealing with project management, you often encounter tasks that depend on the completion of other tasks. These dependencies form a directed acyclic graph, where each task may have multiple prerequisites. Recursive CTEs let you trace all direct and indirect dependencies for a given task, which is essential for understanding project timelines, identifying bottlenecks, or performing impact analysis.


              123456789101112131415161718192021222324252627282930
            
-- Find all tasks that a given task depends on (directly or indirectly)
WITH RECURSIVE dependency_path AS (
    -- Anchor: start with the chosen task (e.g., Deployment, task_id = 6)
    SELECT
        td.task_id,
        td.depends_on_task_id,
        1 AS level
    FROM task_dependencies td
    WHERE td.task_id = 6

    UNION ALL

    -- Recursive: find dependencies of dependencies
    SELECT
        td.task_id,
        td.depends_on_task_id,
        dp.level + 1
    FROM task_dependencies td
    INNER JOIN dependency_path dp ON td.task_id = dp.depends_on_task_id
)
SELECT
    dp.task_id,
    pt.task_name AS dependent_task,
    dp.depends_on_task_id,
    pt2.task_name AS prerequisite_task,
    dp.level
FROM dependency_path dp
JOIN project_tasks pt ON dp.task_id = pt.task_id
JOIN project_tasks pt2 ON dp.depends_on_task_id = pt2.task_id
ORDER BY dp.level, dp.task_id, dp.depends_on_task_id;

In this example, the recursive CTE starts with the "Deployment" task and follows each dependency backward, uncovering all prerequisite tasks in the chain. The level column tracks how far each dependency is from the original task. This approach can be extended to explore any hierarchical or dependency structure in your database, such as supply chains, organizational charts, or product categories.

Recursive CTEs are thus a versatile tool for both generating data and traversing complex relationships. They allow you to express queries that would otherwise require procedural code or multiple passes over your data.

1. How can recursive CTEs be used to generate a sequence of dates?

2. Fill in the blanks to complete a recursive CTE that finds all dependencies for a task.

3. What is a common pitfall when writing recursive CTEs?

Alt var klart?

Takk for tilbakemeldingene dine!

Seksjon 2. Kapittel 4

Spør AI

Spør om hva du vil, eller prøv ett av de foreslåtte spørsmålene for å starte chatten vår

Seksjon 2. Kapittel 4