如何在Postgres中从连接表中累加子项出现次数？

Question

我需要计算使用一种技能的咨询顾问数量，通过联接表（consultant_skills），挑战是要将子项出现次数递归地累加到父项。

这里是我试图实现的复制内容。目前的结果为:

skill_id | count
2        | 2
3        | 1
5        | 1
6        | 1

但我需要递归计算父级的计数，预期结果如下：

skill_id | count
1        | 2 
2        | 2
3        | 1
4        | 2
5        | 2
6        | 1

有人知道我该怎么做吗？

- Giovanni Petris

---

请展示您尝试过的表模式、样本数据和查询语句。 - Bill Doughty

@BillDoughty 在描述中有：http://sqlfiddle.com/#!17/8727f2/2 - Andrey

抱歉，我错过了那个。谢谢。 - Bill Doughty

Answer 1

Sqlfiddle解决方案

你需要使用WITH RECURSIVE，如Mike所建议的那样。他的答案很有用，特别是在参考使用distinct消除顾问冗余计数时，但它并没有达到你要寻找的确切结果。

请参见上面的sqlfiddle中的工作解决方案。我相信这就是你要寻找的：

WITH RECURSIVE results(skill_id, parent_id, consultant_id)
AS (
  SELECT skills.id as skill_id, parent_id, consultant_id
  FROM consultant_skills
  JOIN skills on skill_id = skills.id
  
  UNION ALL
  
  SELECT skills.id as skill_id, skills.parent_id as parent_id, consultant_id
  FROM results
  JOIN skills on results.parent_id = skills.id
)
SELECT skill_id, count(distinct consultant_id) from results
GROUP BY skill_id
ORDER BY skill_id

在下面的查询中发生的是，我们将技能表递归地加入到自身中，并将上一个父ID作为新技能ID旋转，并在每次迭代中使用新的父ID。递归停止，因为最终父ID为NULL，没有JOIN，因为它是一个INNER JOIN。希望这样说得通。

- Bill Doughty

---

谢谢你发现了这个问题。我想我阅读理解有些问题 :-) - Mike Organek

它完美地运行了！非常感谢你，比尔，你救了我的一天。 - Giovanni Petris