获取连接三个表的聚合平均值，并将它们显示在第一个表中的每个值旁边

Question

我有三个表，您也可以在SQL fiddle中找到：

CREATE TABLE Sales (
Product_ID VARCHAR(255),
Sales_Value VARCHAR(255),
Sales_Quantity VARCHAR(255)
);
INSERT INTO Sales
(Product_ID, Sales_Value, Sales_Quantity)
VALUES 
("P001", "500", "200"),
("P002", "600", "100"),
("P003", "300", "250"),
("P004", "900", "400"),
("P005", "800", "600"),
("P006", "200", "150"),
("P007", "700", "550");

CREATE TABLE Products (
Product_ID VARCHAR(255),
Product_Name VARCHAR(255),
Category_ID VARCHAR(255)
);
INSERT INTO Products
(Product_ID, Product_Name, Category_ID)
VALUES 
("P001", "Shirt", "C001"),
("P002", "Dress", "C001"),
("P003", "Hoodie", "C002"),
("P004", "Ball", "C002"),
("P005", "Ski", "C002"),
("P006", "Boot", "C003"),
("P007", "Flip-Flop", "C003");

CREATE TABLE Categories (
Category_ID VARCHAR(255),
Category_Name VARCHAR(255)
);
INSERT INTO Categories
(Category_ID, Category_Name)
VALUES 
("C001", "Fashion"),
("C002", "Sport"),
("C003", "Shoes");

第一个表包含每个产品的Sales
第二个表包含每个product的详细信息
第三个表包含categories。

---

现在，我想显示所有产品以及每个产品旁边的average_sales_price_per_category
结果应该是这样的：

Product_ID      Category      average_sales_price_per_category
P001             Fashion               3.66
P002             Fashion               3.66
P003             Sport                 1.60
P004             Sport                 1.60
P005             Sport                 1.60
P006             Shoes                 1.28
P007             Shoes                 1.28

我试着用这个问题的答案，但我得到了Error:

SELECT s.Product_ID, c.Category_Name,
(SELECT SUM(SS.Sales_Value) / SUM(SS.Sales_Quantity)
FROM Sales SS 
WHERE SS.Category_ID = S.Category_ID
) AS average_sales_price
FROM Sales s 
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN Categories c ON c.Category_ID = p.Category_ID;

错误

Unknown column 'SS.Category_ID' in 'where clause'

我需要在代码中更改什么才能获得预期的结果？

Answer 1

查询的问题是类别在销售级别不可用。

如果您运行的是MySQL 8.0，您可以将聚合和窗口功能组合如下：

SELECT 
p.Product_ID, 
c.Category_Name,
SUM(SUM(s.Sales_Value)) OVER(PARTITION BY c.Category_ID)
/ SUM(SUM(s.Sales_Quantity)) OVER(PARTITION BY c.Category_ID)
AS average_sales_price
FROM Sales s 
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN Categories c ON c.Category_ID = p.Category_ID
GROUP BY p.Product_ID, c.Category_ID, c.Category_Name

DB Fiddle上的演示：

|Product_ID|Category_Name|average_sales_price||---------------|---------------|------------------||P001|时尚|3.6666666666665||P002|时尚|3.6666666666665||P003 |运动版|1.6||P004 |运动版|1.6||P005|运动|1.6||P006|鞋|12857142857142858||P007 |鞋|1.28857142857142858 |

Answer 2

您的表在内部子查询中不可见，以避免内部子查询的where条件您可以在分组的聚合子查询上使用联接

SELECT
s.Product_ID,
Price_Category.average_sales_price_per_category
FROM Sales s
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN
(SELECT 
c.Category_ID,
c.Category_Name,
SUM(s.Sales_Value) / SUM(s.Sales_Quantity) AS average_sales_price_per_category
FROM Sales s 
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN Categories c ON c.Category_ID = p.Category_ID
GROUP BY 1) Price_Category ON Price_Category.Category_ID = p.Category_ID;

SQL Fiddle

Answer 3

首先，在Sales表中没有任何Category_Id列。

针对不匹配情况的概率，将JOIN子句替换为LEFT JOIN子句，并在末尾添加GROUP BY c.Category_Name, c.Category_ID：

SELECT c.Category_Name,c.Category_ID,
SUM(S.Sales_Value) / SUM(S.Sales_Quantity) AS average_sales_price       
FROM Sales s 
LEFT JOIN Products p ON p.Product_ID = s.Product_ID
LEFT JOIN Categories c ON c.Category_ID = p.Category_ID
GROUP BY c.Category_Name, c.Category_ID;

其中不需要相关的子查询。

演示