Recursive query with sum in Postgres

问题:

I must store a lot of projects in my DB. Each project can have child projects. The structure looks like a tree:

                                     Project
                            /           |             \
                    ProjectChild1    ProjectChild2    [...]    ProjectChild[n]
                    /       |
 ProjectChildOfChild1    ProjectChildOfChild2

The level of the tree is unknow. I'm thinking to create a table like this:

Table Projects:

project_ID id_unique PRIMARY_KEY
project_NAME text
project_VALUE numeric
project_PARENT id_unique

In this case, the column project_PARENT will store the id of the parent project, if exists.

For my application I need to retrieve the total value of a project, for this I need to sum the values of every project child and the root project.

I know that I need to use recursivity, but I don't know how to do this in Postgres.

回答1:

This is a simplified version of @a_horse's correct answer (after discussion with OP in comments).
Works with any (reasonably finite) number of levels in the recursion.

Total price for given project_id

WITH RECURSIVE cte AS (
   SELECT project_id AS project_parent, project_value
   FROM   projects
   WHERE  project_id = 1 -- enter id of the base project here !

   UNION  ALL 
   SELECT p.project_id, p.project_value
   FROM   cte
   JOIN   projects p USING (project_parent)
)
SELECT sum(project_value) AS total_value
FROM   cte;

To get total cost for all projects at once:

For all projects at once

WITH RECURSIVE cte AS (
   SELECT project_id, project_id AS project_parent, project_value
   FROM   projects
   WHERE  project_parent IS NULL  -- all base projects

   UNION  ALL 
   SELECT c.project_id, p.project_id, p.project_value
   FROM   cte c
   JOIN   projects p USING (project_parent)
)
SELECT project_id, sum(project_value) AS total_value
FROM   cte
GROUP  BY 1
ORDER  BY 1;

SQL Fiddle (with a correct test case).

回答2:

Something like this:

with recursive project_tree as (
   select project_id, 
          project_name,
          project_value,
          project_parent
   from projects
   where project_id = 42 -- << the id of the "base" project
   union all 
   select p.project_id,
          p.project_name,
          p.project_value,
          p.project_parent
   from projects p
     join project_tree t on t.project_id = p.project_parent
)
select sum(project_value)
from project_tree;

The first part of the union needs to select the project that you want to evaluate (the "child" project). The recursive join will the walk up the tree and retrieve all parent projects.