This is a bit of a Brain Twister!
Using Postgres, I am trying to recursively
calculate the average
progress values of all descendants
items. Parent Items have a progress calculation of PARENT
and children have a progressCalculation of CHILD
; however, children can also be parents having a progressCalculation of PARENT
and in turn have their own children with progressCalculation of CHILD
and so on and so forth until there are no more descendants.
Setup:
- Create Item Table In Postgres
CREATE TABLE item (
id INT,
title VARCHAR(255),
progress INT,
"progressCalculation" VARCHAR(255),
"parentItemId" INT
);
- Insert Items
insert into item (id,title,progress,"progressCalculation","parentItemId")
values (1,'Item 1',NULL,'PARENT',NULL),
(2,'Item 1.1',100,'CHILD',1),
(3,'Item 1.2',100,'CHILD',1),
(4,'Item 1.3',100,'CHILD',1),
(5,'Item 1.4',NULL,'PARENT',1),
(6,'Item 1.4.1',50,'CHILD',5),
(7,'Item 1.4.2',50,'CHILD',5);
What I've tried
select
id,
title,
CASE
when "progressCalculation" = 'PARENT'
then (select round(avg(COALESCE(progress,0))) from item i where item."id" = i."parentItemId")
else progress
END as progress,
"progressCalculation",
"parentItemId"
from
item;
this only works for one level deep! it should be recursive no matter how many levels it needs to go.
I really need help setting up a CTE (Common Table Expression) using RECURSIVE query.
The output should look like the following:
Essentially Item 1 is the GRANDPARENT
, Item 1.4 is PARENT
and items 1.4.1 and 1.4.2 are CHILDREN
(grandchildren)
Any help would be GREATLY appreciated!