-- leaf_id为末端分类的id,用于group by区分分支, distance为记录离leaf的距离
with RECURSIVE t(id, parent_id, en_name, cn_name, handler, leaf_id, distance)as(
    SELECT id, parent_id, en_name, cn_name, handler, id, 0
    FROM product_catalog end_point
    where end_point.cn_name ilike '维生素'  -- 末端分类筛选条件，值一定要非空

    union all
    SELECT parent.id, parent.parent_id, parent.en_name, parent.cn_name, parent.handler, t.leaf_id, t.distance +1
    FROM t, product_catalog parent
    where parent.id = t.parent_id
), tree as (
    SELECT
    row_number() over(partition by t.leaf_id ORDER BY t.distance desc) dept,
    *
    from t
)

SELECT
tree.leaf_id,
"string_agg"(tree.cn_name, '-'  ORDER BY tree.distance desc) crumbs,  -- 连接树层级关系
(array_remove("array_agg"(tree.handler ORDER BY tree.distance asc), NULL))[1] "handler"  -- handler排序组成数组，去掉null值, 取第一个
FROM tree
GROUP BY tree.leaf_id
HAVING true
and (array_agg(array[tree.dept::citext, tree.cn_name::citext])) @> array['2', '非药物类产品'::citext] -- 二级分类名称筛选条件
and (array_agg(array[tree.dept::citext, tree.cn_name::citext])) @> array['1', 'cato'::citext] -- 一级分类名称筛选条件