I am currently designing a catalogue system. It has 2 types of items: categories and items themselves. There also may be nested categories and some items may have a parent which is always some category.
So the table looks like:

create table items
(
    id        uuid,
    parent    uuid,
    name      text,
    type      text,
    cost      int,

    primary key (id),
    constraint constraint_on_parent
        foreign key (parent)
            references items (id)
);

Another thing to mention is that all the category items have a cost equals to null (well, after all, you cannot buy a category itself, right?).
Now I need to come up with an sql query which would, given an item id, return itself and all its children in case it is a category. Also if it is a category, then I would like to get the average price of its children (also applies to the sub-categories).
So far I have managed to create a recursive query which retrieves itself and the children:

with recursive query as (
    select id, name, type, cost
    from items
    where id=$item_id
    union all
    select it.id, it.name, it.type, it.cost
    from items it inner join query q on q.id = it.parent
)
select id, name
from children

However, now I am wondering, how can I change it to calculate the mean price of thecategory and its subcateogries recursively?
Also, I am using PostgreSQL 14.

Edit 1: Sample data and desired output
Sample data

Desired ouput
When run the described query against uuid3 I expect to get something like

This output looks like the 3 last rows of the sample data, except for the first row, the category, having a price equal to the mean price of its children
When run the described query against uuid1 I expect to get

Here subcategory1 price is the mean of item2 and item3 costs, root price is the mean of item1, item2 and items3 costs.
Also, if the category has no items in it, its price should remain null

CodePudding user response：

You can use ARRAY to store path to the item

with recursive query as (
    select id, name, type, cost, array[id] path
    from items
    where id='uuid1'
    union all
    select it.id, it.name, it.type, it.cost, array_append(path, it.id)
    from items it inner join query q on q.id = it.parent
)
select  t1.id, t1.name, t1.type, avg(t2.cost) cost
from query t1
left join query t2 on t2.path @> Array[t1.id]
group by t1.id, t1.name, t1.type;

db<>fiddle

Returns

id  name    type    cost
uuid1   root    category    200.0000000000000000
uuid2   item1   item    100.0000000000000000
uuid3   subcategory1    category    250.0000000000000000
uuid4   item2   item    200.0000000000000000
uuid5   item3   item    300.0000000000000000