TL;DR我不知道如何编写在递归部分不使用聚合函数的递归 Postgres 查询。是否有其他方法可以编写如下所示的递归查询？
假设我们有一些运动：
CREATE TABLE sports (id INTEGER, name TEXT);
INSERT INTO sports VALUES (1, '100 meter sprint');
INSERT INTO sports VALUES (2, '400 meter sprint');
INSERT INTO sports VALUES (3, '50 meter swim');
INSERT INTO sports VALUES (4, '100 meter swim');
以及参加这些运动的运动员的一些单圈时间：
CREATE TABLE lap_times (sport_id INTEGER, athlete TEXT, seconds NUMERIC);
INSERT INTO lap_times VALUES (1, 'Alice', 10);
INSERT INTO lap_times VALUES (1, 'Bob', 11);
INSERT INTO lap_times VALUES (1, 'Claire', 12);
INSERT INTO lap_times VALUES (2, 'Alice', 40);
INSERT INTO lap_times VALUES (2, 'Bob', 38);
INSERT INTO lap_times VALUES (2, 'Claire', 39);
INSERT INTO lap_times VALUES (3, 'Alice', 25);
INSERT INTO lap_times VALUES (3, 'Bob', 23);
INSERT INTO lap_times VALUES (3, 'Claire', 24);
INSERT INTO lap_times VALUES (4, 'Alice', 65);
INSERT INTO lap_times VALUES (4, 'Bob', 67);
INSERT INTO lap_times VALUES (4, 'Claire', 66);
我们想要创建一些任意类别：
CREATE TABLE categories (id INTEGER, name TEXT);
INSERT INTO categories VALUES (1, 'Running');
INSERT INTO categories VALUES (2, 'Swimming');
INSERT INTO categories VALUES (3, '100 meter');
并使我们的体育成员成为这些类别：
CREATE TABLE memberships (category_id INTEGER, member_type TEXT, member_id INTEGER);
INSERT INTO memberships VALUES (1, 'Sport', 1);
INSERT INTO memberships VALUES (1, 'Sport', 2);
INSERT INTO memberships VALUES (2, 'Sport', 3);
INSERT INTO memberships VALUES (2, 'Sport', 4);
INSERT INTO memberships VALUES (3, 'Sport', 1);
INSERT INTO memberships VALUES (3, 'Sport', 4);
我们想要一个包含其他类别的“超级”类别：
INSERT INTO categories VALUES (4, 'Running + Swimming');
INSERT INTO memberships VALUES (4, 'Category', 1);
INSERT INTO memberships VALUES (4, 'Category', 2);
现在来了棘手的一点。
我们希望根据运动员在每项运动中的单圈时间进行排名：
SELECT sport_id, athlete,
RANK() over(PARTITION BY sport_id ORDER BY seconds)
FROM lap_times lt;
但我们也想在品类层面做到这一点。当我们这样做时，运动员的排名应该基于他们在该类别中所有运动中的平均排名。例如：
Alice is 1st in 100 meter sprint and 3rd in 400 meter sprint
-> average rank: 2
Bob is 2nd in 100 meter sprint and 1st in 400 meter sprint
-> average rank: 1.5
Claire is 3rd in 100 meter sprint and 2nd in 400 meter sprint
-> average rank: 2.5
Ranking for running: 1st Bob, 2nd Alice, 3rd Claire
对于“超级”类别，运动员的排名应基于其跨类别的平均排名，而不是这些类别中的基础运动。即它应该只考虑它的直接子项，而不是扩展到所有运动。
我尽力编写了一个查询来计算这些排名。这是一个递归查询，从底部的体育项目开始，向上遍历会员资格，计算类别和“超级”类别的排名。这是我的查询：
WITH RECURSIVE rankings(rankable_type, rankable_id, athlete, value, rank) AS (
SELECT 'Sport', sport_id, athlete, seconds, RANK() over(PARTITION BY sport_id ORDER BY seconds)
FROM lap_times lt
UNION ALL
SELECT 'Category', category_id, athlete, avg(r.rank), RANK() OVER (PARTITION by category_id ORDER BY avg(r.rank))
FROM categories c
JOIN memberships m ON m.category_id = c.id
JOIN rankings r ON r.rankable_type = m.member_type AND r.rankable_id = m.member_id
GROUP BY category_id, athlete
)
SELECT * FROM rankings;
但是，当我运行它时，我收到以下错误：
ERROR: aggregate functions are not allowed in a recursive query's recursive term
这是由于avg(r.rank)在查询的递归部分。 Postgresql 不允许在查询的递归部分调用聚合函数。有没有其他方法可以写这个？
如果我交换avg(r.rank), RANK() ...出去为了NULL, NULL执行查询，结果对于体育运动来说看起来是正确的，并且包含类别的预期行数。
我考虑过尝试使用嵌套查询将递归展开到两级或三级，因为这对我的用例来说很好，但我想在尝试之前先在这里问一下。
另一种选择可能是更改架构，使其灵活性降低，从而使体育运动不能属于多个类别。我不确定在这种情况下查询会是什么样子，但它可能更简单？
预先感谢，我真的很感激。
