0

I´m trying to determine the average progress of each task group in a tasks tree. The problem is that only the leaf nodes (which are in a diferent table ‒ a child table ‒) actually have the progress field.

I have a Tasks (parent) table and an Activities (child) table. Both of them share IDs. I should point out that I´m using the ltree extension in my DB.

I´m trying to make a view showing the entire tree and a column with the average progress per node level. I have to start the calculations from the inner-most level and build my way up in the tree structure, but I can´t seem to get it right.

I´ve tried to approach this using recursive CTEs and window functions to no avail. Postgres doesn´t allow to use aggregate functions in the recursive term of a recursive cte, so that didn´t work.

These are my tables (simplified):

tasks:
=======
id -- SERIAL
parent_id -- INTEGER
project_id -- INTEGER
name -- VARCHAR
task_group -- BOOL
path -- LTREE

activities:
===========
id -- INTEGER
progress -- INTEGER

EDIT

This is a screenshot of the view as of right now. I´m able to get the average progress of all leaf nodes, but that´s not exactly what I need. The average progress should be calculated for each level in the tree, and then build it´s way up in the node hierarchy.

Tasks table

Any insight on this will be much appreciated. Thanks in advance.

Alex_89
  • 371
  • 5
  • 15
  • What is the content of `progress` and how should progress be calculated for upper nodes? – Laurenz Albe Mar 17 '21 at 07:27
  • Progress is a simple percentage. – Alex_89 Mar 17 '21 at 13:41
  • Each task group (node) should calculate the average progress percentage of its immediate children, which, in time, calculated *their* progress from the average of *their* immediate children's progress. – Alex_89 Mar 17 '21 at 13:47
  • It would be helpful to see some sample data plus the desired outcome. – Laurenz Albe Mar 17 '21 at 13:49
  • I just edited my question with a screenshot of the table as it´s displayed at the moment and a comment on how it *should* display the average progress. – Alex_89 Mar 17 '21 at 14:29
  • What is the connection between `tasks` and `activities`? – Laurenz Albe Mar 17 '21 at 14:35
  • Activities is another table (not shown in the screenshot) wich actually holds the progress values. It works as a "child" or "detailed" table of Tasks joined by IDs. But I think that´s irrelevant for the problem at hand. Even using just 1 table (the one in my edit) the problem remains the same: **task group** average progress should be calculated *after* the average progress of its immediate child nodes has been calculated, and so forth. Hence the need for a recursive query, in my opinion, and posibly a window function. – Alex_89 Mar 17 '21 at 15:19
  • How should I write a query like that if I don't know ho to find the progress that belongs to a task? – Laurenz Albe Mar 17 '21 at 15:27
  • It´s simple... A **task** (leaf node) has **activities**. Each activity has a progress. The **task** progress is the average progress of its activities. But again, nevermind the 2 tables... The reason I'm struggling with this is that I can't get the average to work as an upward cascade. I can easily calculate the average of the leaf nodes, but since the tree could have an infinite depth, in theory, I need to calculate each level's average progress in order to find its parent's average progress. – Alex_89 Mar 17 '21 at 15:40
  • I am afraid this is too complicated for me; sorry. – Laurenz Albe Mar 23 '21 at 05:38

0 Answers0