树结构中的递归和，其中树结构是字符串

Question

订单项与来自树结构的类别路径一起存储。也就是说，类别2是类别1的子类别，类别3是2等的子类别。

这是一个PostgreSQL数据库。

create table order_item (
id bigserial not null,
quantity int not null,
category_path text
);
insert into order_item (quantity, category_path) VALUES 
(5, 'Category 1'),
(7, 'Category 1||Category 2'),
(3, 'Category 1||Category 2||Category3'),
(9, 'Category 1||Category 2||Category3'),
(2, 'Category 4'),
(11, null),
(4, null);

select category_path, sum(quantity) from order_item group by category_path order by category_path;
category_path                          | quantity |
---------------------------------------------------
Category 1                             |        5 |
Category 1||Category 2                 |        7 |
Category 1||Category 2||Category3"     |       12 |
Category 4                             |        2 |
<null>                                 |       15 |

我想得到的是包含子类别的数量栏。

category_path                          | quantity | quantityIncludingSubCategories |
-----------------------------------------------------------------------------------
Category 1                             |        5 |                            24  |
Category 1||Category 2                 |        7 |                            19  |
Category 1||Category 2||Category3"     |       12 |                            12  |
Category 4                             |        2 |                             2  |
<null>                                 |       11 |                            11  |

我发现这个帖子很相似，但没有运气。树结构中的递归和

我试过用CTE解决这个问题，但似乎做不好。欢迎任何建议：(

Answer 1

您已经有了每个节点的路径，因此不需要递归。一种直接的方法使用相关的子查询或横向连接，并在路径上进行模式匹配：

select oi.*, x.quantity1
from order_item oi
cross join lateral (
select sum(oi1.quantity) quantity1
from order_item oi1
where oi1.category_path like oi.category_path || '%'
) x

DB Fiddle演示