PostgreSQL如何计算同类型其余产品订单平均值并筛选符合条件产品

我有一张名为Products的表，结构如下：

+-----------+-----------+----------+
|ProductCode|ProductType|   ....   |
+-----------+-----------+----------+
|   ref01   |   BOOKS   |   ....   |
|   ref02   |   ALBUMS  |   ....   |
|   ref06   |   BOOKS   |   ....   |
|   ref04   |   BOOKS   |   ....   |
|   ref07   |   ALBUMS  |   ....   |
|   ref10   |   TOYS    |   ....   |
|   ref13   |   TOYS    |   ....   |
|   ref09   |   ALBUMS  |   ....   |
|   ref29   |   TOYS    |   ....   |
|   .....   |   .....   |   ....   |
+-----------+-----------+----------+

另有一张名为Sales的表，结构如下：

+-----------+-----------+----------+
|ProductCode|   Orders  |   ....   |
+-----------+-----------+----------+
|   ref01   |     15    |   ....   |
|   ref02   |     12    |   ....   |
|   ref06   |     20    |   ....   |
|   ref04   |     14    |   ....   |
|   ref07   |     11    |   ....   |
|   ref10   |     19    |   ....   |
|   ref13   |      3    |   ....   |
|   ref09   |      9    |   ....   |
|   ref29   |      5    |   ....   |
|   .....   |   .....   |   ....   |
+-----------+-----------+----------+

需要筛选出订单量高于同类型其余所有产品订单平均值的产品，使用PostgreSQL实现，且不能使用WITH、OVER、LIMIT、PARTITION关键字。

SELECT s.ProductCode, s.Orders
FROM Products p
INNER JOIN Sales s 
ON p.ProductCode = s.ProductCode
WHERE s.Orders > (
    SELECT AVG(s2.Orders)
    FROM Products p2
    INNER JOIN Sales s2 
    ON p2.ProductCode = s2.ProductCode
    WHERE p2.ProductType = p.ProductType
    AND p2.ProductCode != p.ProductCode
)

• 外层查询关联Products和Sales表，获取每个产品的类型、编码、对应订单量
• 关联子查询会匹配与外层当前产品同类型、且产品编码不一致的所有产品，计算这部分产品的平均订单量
• WHERE条件过滤出订单量大于同类型其他产品平均订单的产品，完全匹配需求的计算逻辑，且未使用禁用关键字。

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