递归查询中不允许使用聚合函数。是否有其他方法可以编写此查询？

Question

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，则查询会执行，对于运动项目，结果看起来正确，并且包含类别的预期行数。

我考虑尝试使用嵌套查询将递归展开到两个或三个级别，因为这对我的用例来说没问题，但我想在尝试之前先在这里问一下。

另一个选择可能是更改模式，使得运动项目不能属于多个类别。我不确定在那种情况下查询会是什么样子，但可能会更简单？

非常感谢您的帮助。

- Chris

---

我可能首先会构建类别树，然后在一个单独的CTE中进行聚合操作，with recursive cat_tree as (...), aggregates as (...) select * from aggregates。 - user330315

Answer 1

虽然不太美观，但我找到了一种解决方案：

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', *, rank() OVER(PARTITION by category_id ORDER BY avg_rank) FROM (
    SELECT DISTINCT category_id, athlete, avg(r.rank) OVER (PARTITION by category_id, athlete) AS avg_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
  ) _
)
SELECT * FROM rankings;

在查询的递归部分，我使用了一个窗口函数分组来代替调用 GROUP BY 并计算 avg(r.rank)。这会产生与计算平均排名相同的效果。

缺点是这个计算会比必要的次数发生。如果我们可以使用 GROUP BY 然后 avg(r.rank)，那么比起先用 avg(r.rank) 再用 GROUP BY 更有效率。

由于嵌套查询结果中有重复数据，我使用 DISTINCT 进行过滤，然后外层查询基于这些平均值对每个 category_id 中所有运动员计算一个 RANK()。

如果有更好的方法，我仍然很乐意听取建议。谢谢

- Chris

Answer 2

如您所述，聚合函数可以通过使用distinct + analytics来模拟。同样的效果也可以通过仅使用analytics来实现——只需为每个组筛选1行即可。

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_rank, rank() OVER(PARTITION by category_id ORDER BY avg_rank) FROM (
    SELECT category_id, athlete, avg(r.rank) OVER (PARTITION by category_id, athlete) AS avg_rank,
           row_number() over (partition by category_id, athlete order by '') rn
    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
  ) _
  where rn = 1  
)
SELECT * FROM rankings;

这种方法与之前的方法基本相同，但看起来有些尴尬。

我不认为聚合函数不能在引用递归成员的查询块中使用有根本性原因，但这是 PG 的限制。MSSQL 和 Oracle 也存在相同的限制，但与 PG 不同的是，这两个 RBDMSs 也不允许在递归成员中使用 distinct。

- Dr Y Wit

---

谢谢您提供这个信息 - 虽然现在已经过去了几年，我已经忘记了很多细节。很高兴您可以只选择单行以避免我所描述的缺点。 - Chris