PostgreSQL统计子项数量

Question

树的深度没有限制。例如：

+----+-------+--------------------+
| id | name  | parent_id |  value |
+----+-------+--------------------+
|  1 | test1 |           |    0   |
|  2 | test2 |     1     |    0   |
|  3 | test3 |     2     |    5   |
|  4 | test4 |     1     |    0   |
|  5 | test5 |     4     |    5   |
|  6 | test6 |     4     |    0   |
|  7 | test7 |     6     |    10  |
+----+-------+--------------------+

我想要获取一个人的所有子元素的总值。 就像这样：

+----+-------+--------------------+
| id | name  | parent_id |  value |
+----+-------+--------------------+
|  1 | test1 |           |    20  |  =  test2.value + test4.value
|  2 | test2 |     1     |    5   |  =  test3.value
|  3 | test3 |     2     |    5   |  
|  4 | test4 |     1     |    15  |  =  test5.value + test6.value
|  5 | test5 |     4     |    5   |
|  6 | test6 |     4     |    10  |  =  test7.value
|  7 | test7 |     6     |    10  |
+----+-------+--------------------+

有什么建议吗？谢谢！

- Danfi

---

不了解您的其他需求，我无法确定正确的模式是什么，但是您的实现是在数据库中存储树形结构的一种相当幼稚的方式。已经发明了一些设计模式，使得做各种聪明的事情变得更加容易，包括您所问的问题。例如，请查看物化路径设计模式。请参阅http://www.rampant-books.com/book_0601_sql_coding_styles.htm或其他参考资料。 - J.Spiral

@hims056：有什么不清楚的吗？跟随parent_id链接并递归地求和count值。 - mu is too short

@hims056 第一个表是我现在拥有的，第二个表是我想要创建视图所需的。 - Danfi

我快速谷歌搜索并找到了这篇文章，几乎完全符合您的使用情况... http://www.ibstaff.net/fmartinez/?p=18 - J.Spiral

可能是是否可以制作递归SQL查询？的重复问题。 - splash

Answer 1

这是一个递归查询，希望能解决你的问题：

WITH RECURSIVE tree (id, parent_id, cnt) AS (
    -- start from bottom-level entries
    SELECT id, parent_id, 0::bigint AS cnt
    FROM tbl t
    WHERE NOT EXISTS (
        SELECT id
        FROM tbl
        WHERE parent_id = t.id
    )

    UNION ALL

    -- join the next level, add the number of children to that of already counted ones
    SELECT t.id, t.parent_id, tree.cnt + (
            SELECT count(id) 
            FROM tbl 
            WHERE parent_id = t.id
        )
    FROM tbl t JOIN tree ON t.id = tree.parent_id
)
SELECT tree.id, max(tree.cnt) AS number_of_children
FROM tree 
-- use the JOIN if you need additional columns from tbl
-- JOIN tbl ON tree.id = tbl.id 
-- use the WHERE clause if you want to see root nodes only
-- WHERE parent_id IS NULL
GROUP BY tree.id
ORDER BY tree.id
;

我也创建了一个SQLFiddle。

- dezso

---

谢谢你的回答。我犯了一个错误，我真正想要的是总值而不是计数。抱歉。 - Danfi

很抱歉，您的实现有误。请考虑一个根节点具有三个子节点的树，其中前两个具有两个子节点，而最后一个没有子节点。根据您的查询，根节点有5个子节点（后代），而实际上有7个。 - lared

Answer 2

请参见 F.35.1.5. connectby 函数示例：

http://www.postgresql.org/docs/current/static/tablefunc.html

- Andrey

Answer 3

WITH RECURSIVE tree (id, parent_id) AS (
    SELECT id, parent_id
    FROM tbl
    UNION ALL
SELECT t.id, t.parent_id
FROM tbl t JOIN tree ON t.id = tree.parent_id
)
SELECT id, count(*) FROM tree
GROUP BY id
ORDER BY id;

- Igor Volkov

---

请提供解释 - Arda Kazancı

上述代码可以使用递归和CTE来帮助计算根元素的子项数量。 - Igor Volkov