SQL中使用PIVOT计算AVG结果异常的问题求助

我有Category、Assign、org三张数据表，需要生成指定透视表输出。尝试以下SQL查询后，发现使用PIVOT计算AVG无法得到正确结果，平均值未显示在对应分类列中。

原查询代码 #

SELECT *
FROM (select     avg(hd.SCORE) over(partition by hd.org_id) Avg_Item
, hd.org_id,d.cateogory,hd.score FROM Category d JOIN Assign hd ON d.catid=hd.catid) first
PIVOT (
  AVG(score)
  FOR (cateogory)
  IN ('cat1' as "cat1",'cat2' as "cat2",'cat3' as "cat3",'cat4' as "cat4")
)PIV;

数据表结构 #

Category表 #

Assign表 #

org表 #

期望输出 #

原查询存在几个关键问题：

1. 拼写错误：cateogory应为category（缺失字母r），导致PIVOT无法正确匹配分类列。
2. 冗余窗口函数：子查询中avg(hd.SCORE) over(partition by hd.org_id)是按组织计算全局平均值，和需求中「按组织+分类的分组平均值」逻辑冲突，干扰数据结果。
3. 连接逻辑缺失：仅关联Category和Assign表，未左连接org表，导致无评分记录的组织（如CDE、FGH）无法出现在结果中。
4. PIVOT列不全：原查询仅包含cat1到cat4，缺少期望输出中的cat5。

修正后的SQL #

SELECT o.org_name,
       NVL(p.cat1, '') AS cat1,
       NVL(p.cat2, '') AS cat2,
       NVL(p.cat3, '') AS cat3,
       NVL(p.cat4, '') AS cat4,
       NVL(p.cat5, '') AS cat5
FROM org o
LEFT JOIN (
    -- 先按组织+分类分组计算平均分
    SELECT 
        hd.org_id,
        d.category,
        AVG(hd.score) AS avg_score
    FROM Category d
    JOIN Assign hd ON d.catid = hd.catid
    GROUP BY hd.org_id, d.category
) src
PIVOT (
    MAX(avg_score)  -- 分组后每个组织+分类仅一条记录，MAX/AVG效果一致
    FOR category IN (
        'cat1' AS cat1,
        'cat2' AS cat2,
        'cat3' AS cat3,
        'cat4' AS cat4,
        'cat5' AS cat5
    )
) p ON o.org_id = p.org_id
ORDER BY o.org_id;

关键说明 #

1. 先通过子查询按org_id和category分组，计算每个组织对应分类的独立平均分，确保数据逻辑符合需求。
2. 使用LEFT JOIN关联org表，保证所有组织都能出现在结果中，即使无对应评分记录。
3. PIVOT时用MAX(avg_score)，因为分组后每个组织+分类组合仅一条平均值记录，MAX/AVG均可实现行转列。
4. 用NVL（或对应数据库的COALESCE）将NULL值转为空字符串，匹配期望输出格式。
5. 修正category拼写错误，并补充cat5的PIVOT列。

内容的提问来源于stack exchange，提问作者Jeremy