sql - 如何编写PostgreSQL查询以获取同一表中子行的平均值？

我在PostgreSQL的现有表中工作，在同一个表中有子和父行。我不是数据库专家，所以很难编写返回子行平均值并用父行显示的查询。
我创建了函数，但查询执行时间较长，所以我需要在不使用函数的情况下编写查询。编写此查询以使其运行更快的最佳方法是什么？
请看下面的图片了解我的桌子
我当前的查询

SELECT task_id, tak_or_project_name,
    case when c.parent_id = 0 Then getProjectScore(task_id, 'score1') ELSE score1   end as score1,
    case when c.parent_id = 0 Then  getProjectScore(task_id, 'score2') ELSE score2  end as score2,
    case when c.parent_id = 0 Then getProjectScore(task_id, 'score3')  ELSE score3  end as score3
FROM tbl_task c

最佳答案

就这么做，也许能帮你

Select a.task_id, a.task_or_project_name,
    case when a.Parent_id = 0 then isnull(PScore1, Score1) else Score1 end as Score1,
    case when a.Parent_id = 0 then isnull(PScore2, Score2) else Score2 end as Score2,
    case when a.Parent_id = 0 then isnull(PScore3, Score3) else Score3 end as Score3,
    a.Parent_id
    from tbl_task as a
    left join (
        select Parent_id,
               sum(Score1)/count(score1) as PScore1,
               sum(Score2)/count(score2) as PScore2,
               sum(Score3)/count(score3) as PScore3
        from tbl_task as inner
        where Parent_Id !=0
        group by Parent_Id
    ) as parent on a.task_id = parent.Parent_Id