PostgreSQL - 聚合递归的高效实现

Question

我有一个存储了许多带边界的“区域”的PostgreSQL 9.1数据库，我需要计算出每个区域的有效边界。其中一些区域直接存储边界（作为PostGIS几何体），而其他区域由多个子区域组成，需要将它们聚合在一起。

对于每个子区域，都有一个“操作”，确定它是添加到前一个区域还是从中减去或与其相交。这意味着顺序也很重要，因此需要一个序列号。

我有一个聚合函数可以解决这个问题，但问题是结构是递归的 - 子区域本身可能由子区域组成。

一个简化的模式：

CREATE TABLE area
    (id integer NOT NULL
    , border geometry NULL
    );
CREATE TABLE area_part
    (parent_area_id integer NOT NULL
    , sequence integer NOT NULL
    , operation text NOT NULL
    , child_area_id integer NOT NULL
    );

聚合函数签名（它期望按sequence排序的行）：

CREATE AGGREGATE aggregate_geometry(area geometry, operation text)
-- RETURNS geometry

我创建了一个普通的PL/pgSQL函数，它可以递归调用自身并且可以正常工作，但是由于它执行了许多子查询，所以速度很慢。有什么更有效率的方法吗？

我还尝试使用递归CTE编写查询：

WITH RECURSIVE area_rec AS
(
    SELECT *
    FROM area
    WHERE id = the_if_of_interest

    UNION ALL

    SELECT c.*
    FROM area_rec rec
    JOIN area_part p ON rec.id = p.parent_area_id
    JOIN area c ON p.child_area_id = c.id
)
SELECT *
FROM area_rec

这对于返回给定区域所需的所有行来说是可以的，但我不知道如何将这些值插入到我的聚合函数中。我需要某种“聚合递归函数”！

- EMP

---

你可以将其视为ST_Union。实际函数更复杂，使用了一些与问题无直接关联的其他内容，但它接受一组几何对象并返回一个几何对象，就像聚合版本的ST_Union一样。 - undefined

你难道不能在CTE的（外部）SELECT部分直接使用你的函数吗？ - user330315

@a_horse_with_no_name 你说得没错！实际数据比这个复杂得多，所以我一开始没有考虑到，但可能有办法让它起作用。我会尝试一下这个想法，谢谢！ - undefined

我在说“限制”时的意思是“选择”。[停止计时]通常情况下，如果你想准确定位整个集合中的正确子集，使用递归CTE会导致一些问题。在你的情况下，似乎更像是与函数“耦合”出了问题。能否发布函数的“签名”？它需要什么？它引用了什么？ - undefined

好的，编辑了问题，所以它变得稍微复杂一些，但更接近真实数据。 - undefined

Answer 1

你需要分两个阶段解决这种问题。第一步是创建一个可以进行聚合的数据集（递归、公共表达式），第二步是聚合。对于聚合，我的建议是使用窗口函数。然后，如果需要，您可以将其包含在另一个公共表达式中以进行进一步的后处理。请记住，公共表达式可以嵌套到任意深度，但您不应该需要超过两个级别。您希望保持尽可能简单。

如果没有这个函数，我真的不知道查询会是什么样子，但这应该足以让你开始了。

- Chris Travers

---

在这里，你可以找到一个简单的例子来开始：https://dev59.com/_GYs5IYBdhLWcg3wDPet#13192208 - undefined