如何在PostgreSQL中查找所有其他产品的平均值 [英] How to find the average of all other products in 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    |   ....   |
|   .....   |   .....   |   ....   |
+-----------+-----------+----------+

我正在尝试查找订购数量超过相同类型所有其他产品的平均值的产品。

通过手动计算，结果如下所示：

+-----------+-----------+----------+
|ProductCode|   Orders  |   ....   |
+-----------+-----------+----------+
|   ref02   |     12    |   ....   |
|   ref06   |     20    |   ....   |
|   ref07   |     11    |   ....   |
|   ref10   |     19    |   ....   |
|   .....   |   .....   |   ....   |
+-----------+-----------+----------+

因此，如果查看类型ALBUMS和产品ref02，则需要找到所有其他ALBUMS订单的平均值。 在本例中，它是ref06和ref04的平均值，但实际表中有更多。因此，我需要做的是：

Since product ref02 is 'ALBUMS', and ref07 and ref09 are also 'ALBUMS'. 
      So their average is (11+9)/2=10 <12.

Since product ref06 is 'BOOKS', and **ref01** and ref04 are also 'BOOKS'.                     
      So their average is (15+14)/2=14.5 <20.

Since product ref07 is 'ALBUMS', and **ref02** and ref09 are also 'ALBUMS'.           
      So their average is (12+9)/2=10.5<11.

Since product ref10 is 'TOYS', and ref13 and ref29 are also 'TOYS'           
      So their average is (3+5)/2=4<19.

The rest does not satisfy the condition thus will not be in the result.

我知道并且能够找到同一类型下所有产品的订单平均值，但我不知道如何找到同一类型下所有其他产品的订单平均值。

我使用的是PostgreSQL，但无法使用以下任何关键字：WITH、OVER、LIMIT、PARTITION。

推荐答案

我将在postgres 11或更高版本中使用以下内容：
使用自定义框架定义的具有窗口函数的解决方案：

SELECT product_code, orders
FROM  (
   SELECT product_code, s.orders
        , avg(orders) OVER (PARTITION BY p.product_type
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            EXCLUDE CURRENT ROW) AS avg_orders
   FROM   product p
   JOIN   sales s USING (product_code)
   ) sub
WHERE  orders > avg_orders 
ORDER  BY product_code;  -- optional

我希望这会表现得最好。
有关窗口函数的详细信息，请阅读the manual。

这就是您所要求的：
没有CTE、窗口函数或LIMIT(适用于任何现代Postgres版本)的解决方案：

SELECT product_code, s.orders
FROM   product p
JOIN   sales s USING (product_code)
JOIN   LATERAL (
   SELECT avg(orders) AS avg_orders
   FROM   product p1
   JOIN   sales s1 USING (product_code)
   WHERE  p1.product_type =  p.product_type
   AND    p1.product_code <> p.product_code
   ) a ON a.avg_orders < s.orders
ORDER  BY product_code;  -- optional

任一项都会产生您想要的结果。

小提琴here

关于LATERAL：

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

这篇关于如何在PostgreSQL中查找所有其他产品的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！