I have a table Products which looks like the following:
----------- ----------- ----------
|ProductCode|ProductType| .... |
----------- ----------- ----------
| ref01 | BOOKS | .... |
| ref02 | ALBUMS | .... |
| ref06 | BOOKS | .... |
| ref04 | BOOKS | .... |
| ref07 | ALBUMS | .... |
| ref10 | TOYS | .... |
| ref13 | TOYS | .... |
| ref09 | ALBUMS | .... |
| ref29 | TOYS | .... |
| ..... | ..... | .... |
----------- ----------- ----------
Another table Sales which looks like the following:
----------- ----------- ----------
|ProductCode| Orders | .... |
----------- ----------- ----------
| ref01 | 15 | .... |
| ref02 | 12 | .... |
| ref06 | 20 | .... |
| ref04 | 14 | .... |
| ref07 | 11 | .... |
| ref10 | 19 | .... |
| ref13 | 3 | .... |
| ref09 | 9 | .... |
| ref29 | 5 | .... |
| ..... | ..... | .... |
----------- ----------- ----------
I am trying to find the products that were ordered more than the average of all other products of the same type.
By manually calculating, the result would be something like:
----------- ----------- ----------
|ProductCode| Orders | .... |
----------- ----------- ----------
| ref02 | 12 | .... |
| ref06 | 20 | .... |
| ref07 | 11 | .... |
| ref10 | 19 | .... |
| ..... | ..... | .... |
----------- ----------- ----------
So if looking in the type ALBUMS and product ref02, then I need to find the average of Orders of ALL OTHER ALBUMS.
In this case, it is the average of ref06 and ref04, but there are more in the actual table. So what I need to do is the following:
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.
I know how to and was able to find the average of orders for all products under the same type, but I have no idea how to find the average of orders for all other products under the same type.
I am using PostgreSQL, but cannot use any of these key words: WITH, OVER, LIMIT, PARTITION.
CodePudding user response：
We can try using AVG() as an analytic function here:
WITH cte AS (
SELECT p.ProductCode, p.ProductType, s.Orders,
AVG(s.Orders) OVER (PARTITION BY p.ProductType) AS AvgOrders
FROM Products p
INNER JOIN Sales s ON s.ProductCode = p.ProductCode
)
SELECT ProductCode, ProductType, Orders
FROM cte
WHERE Orders > AvgOrders;
Demo
CodePudding user response：
This is what I would use in Postgres 11 or later:
A solution with a window function using a custom frame definition:
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
I expect this to perform best.
Read the manual for details on window functions.
This is what you are asking for:
A solution without CTE, window function, or LIMIT (works in any modern Postgres version):
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
Either produces your desired result.
db<>fiddle here
About LATERAL: