Postgres：列"all_comments.id"必须出现在 GROUP BY 子句中或在聚合函数中使用

Question

我使用with recursive来计算所有子体的数量。这将是下面的SQL查询，但如果我只为group by指定root_id，我会得到我得到以下错误。

column "all_comments.id" must appear in the GROUP BY clause or be used in an aggregate function

我不明白all_comments.id为什么会出现这个错误。此外，如果我将all_comments.id添加到group by，则不会发生此错误，但是all_descendants.id的最初目的并不重要，并且返回所有子体的行。

我不想从SELECT中删除此列，因为我们还需要检索id和其他列。

我能做些什么来解决这个问题？非常感谢您的帮助。

with recursive all_comments as (
select id, parent_id, id as root_id
from videos_productvideocomment
where parent_id is null
union all
select c.id, c.parent_id, p.root_id
from videos_productvideocomment c
join all_comments p on c.parent_id = p.id
)
select id, root_id, count(*) -1 as all_descendants_count
from all_comments
group by root_id;

样本数据和所需输出

Table "public.videos_productvideocomment"
Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
id           | uuid                     |           | not null |
created_at   | timestamp with time zone |           | not null |
updated_at   | timestamp with time zone |           | not null |
text         | text                     |           | not null |
commented_at | timestamp with time zone |           | not null |
edited_at    | timestamp with time zone |           | not null |
parent_id    | uuid                     |           |          |
user_id      | uuid                     |           | not null |
video_id     | uuid                     |           | not null |

[
{
id: "uuid1",
text: "...",
reply_count: 10,
},
{
id: "uuid5",
text: "...",
reply_count: 3,
},
]

---编辑---使用窗口功能

with recursive all_comments as (
select id, parent_id, id as root_id
from videos_productvideocomment
where parent_id is null
union all
select c.id, c.parent_id, p.root_id
from videos_productvideocomment c
join all_comments p on c.parent_id = p.id
)
select id, parent_id, root_id, count(root_id) OVER(PARTITION BY root_id) -1 as all_descendants_count
from all_comments

获得的结果

[
{
id: "uuid1",
parent_id: null,
text: "...",
reply_count: 10,
},
{
id: "uuid5",
parent_id: null,
text: "...",
reply_count: 3,
},
{
id: "uuid8",
parent_id: "uuid1",
text: "...",
reply_count: 0,
},
...
]

我们只需要得到parent_id为null的那些，但如果我们添加parent_id是null的地方，那么all_descendants_count将为0。

Answer 1

在对整个数据集进行计数后添加一个过滤步骤，例如

with recursive all_comments as (
select id, parent_id, id as root_id
from videos_productvideocomment
where parent_id is null
union all
select c.id, c.parent_id, p.root_id
from videos_productvideocomment c
join all_comments p on c.parent_id = p.id
)
select *
from (
select id, parent_id, root_id, count(root_id) OVER(PARTITION BY root_id) -1 as all_descendants_count
from all_comments
) t
where id = root_id