代码之家 › 专栏 › 技术社区 › Potato Science

使用递归cte的级联和层次结构

recursive-query postgresql

Potato Science · 技术社区 · 7 年前

我正试图用postgres执行递归cte,但我对此束手无策。就性能问题而言表1 所以这不应该是个问题。

表1(费用):

id | parent_id | name
------------------------------
1 | null | A
2 | null | B
3 | 1 | C
4 | 1 | D

表2(费用金额):

ref_id | amount
-------------------------------
3 | 500
4 | 200

预期结果:

id, name, amount
-------------------------------
1 | A | 700
2 | B | 0
3 | C | 500
4 | D | 200

查询

WITH RECURSIVE cte AS (
  SELECT
    expenses.id,
    name,
    parent_id,
    expense_amount.total
  FROM expenses
  WHERE expenses.parent_id IS NULL
  LEFT JOIN expense_amount ON expense_amount.expense_id = expenses.id
  UNION ALL

  SELECT
    expenses.id,
    expenses.name,
    expenses.parent_id,
    expense_amount.total
  FROM cte
  JOIN expenses ON expenses.parent_id = cte.id
  LEFT JOIN expense_amount ON expense_amount.expense_id = expenses.id
)
SELECT
  id,
  SUM(amount)
FROM cte
GROUP BY 1
ORDER BY 1

后果

id | sum
--------------------
1 | null
2 | null
3 | 500
4 | 200

2 回复 | 直到 7 年前

a_horse_with_no_name 7 年前

只能对根行执行条件求和():

with recursive tree as (
   select id, parent_id, name, id as root_id
   from expense
   where parent_id is null
   union all
   select c.id, c.parent_id, c.name, p.root_id
   from expense c 
     join tree p on c.parent_id = p.id
)
select e.id, 
       e.name,
       e.root_id,
       case 
         when e.id = e.root_id then sum(ea.amount) over (partition by root_id)
         else amount
       end as amount
from tree e
  left join expense_amount ea on e.id = ea.ref_id
order by id;

我更喜欢先做递归部分,然后将相关表连接到递归查询的结果,但也可以在CTE内连接到expense\u amount。

联机示例: http://rextester.com/TGQUX53703

然而,以上仅在顶级父级上聚合,而不用于任何中间非叶行。

如果您还想看到中间聚合,那么这会变得更复杂一些(对于大型结果来说可能不是很容易扩展,但您说过您的表没有那么大)

with recursive tree as (
   select id, parent_id, name, 1 as level, concat('/', id) as path, null::numeric as amount
   from expense
   where parent_id is null
   union all
   select c.id, c.parent_id, c.name, p.level + 1, concat(p.path, '/', c.id), ea.amount
   from expense c 
     join tree p on c.parent_id = p.id
     left join expense_amount ea on ea.ref_id = c.id
)
select e.id, 
       lpad(' ', (e.level - 1) * 2, ' ')||e.name as name,
       e.amount as element_amount,
       (select sum(amount) 
        from tree t
        where t.path like e.path||'%') as sub_tree_amount,
       e.path
from tree e
order by path;

联机示例: http://rextester.com/MCE96740

查询建立属于(子)树的所有ID的路径,然后使用标量子选择来获取属于节点的所有子行。当递归查询的结果不能保存在内存中时,这个子选择将使其变得非常慢。

我用了 level 列创建树结构的“可视化”显示-这有助于我调试语句并更好地理解结果。如果您需要程序中某个元素的真实名称,您显然只会使用 e.name 而不是用空格预挂起它。

timmur 7 年前

由于某种原因,我无法使您的查询生效。下面是我的尝试,它适用于您提供的特定表(父子表,没有孙子表),无需递归。 SQL Fiddle

    --- step 1: get parent-child data together 
    with parent_child as(
        select t.*, amount
        from
          (select e.id, f.name as name, 
              coalesce(f.name, e.name) as pname
            from expense e
            left join expense f
            on e.parent_id = f.id) t
        left join expense_amount ea 
        on ea.ref_id = t.id
    )

    --- final step is to group by id, name
    select id, pname, sum(amount)
        from
          (-- step 2: group by parent name and find corresponding amount
           -- returns A, B
            select e.id, t.pname, t.amount
              from expense e
              join (select pname, sum(amount) as amount
                    from parent_child
                    group by 1) t
              on t.pname = e.name
          -- step 3: to get C, D we union and get corresponding columns
          -- results in all rows and corresponding value
          union
          select id, name, amount
            from  expense e
          left join expense_amount ea
          on e.id = ea.ref_id
          ) t
    group by 1, 2 
    order by 1;