获取加入第二个表的聚合平均值并将它们显示在第一个表中的每个值旁边 [英] Get aggregated average values joining a second table and display them next to each value in first table

问题描述

我有两个表，您也可以在 SQL fiddle:

I have two tables which you can also find in the SQL fiddle:

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


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.
第二个表包含Categories.

The first table contains the Sales for each product.
The second table contains the Categories.

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

Now, I want to display all products and the average_sales_price_per_category next to each product.
The result should look like this:

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

我尝试采用 这个问题 但我认为 OVER 子句在我的 MySQL 版本中不可用:

I tried to go with the solution from this question but I think the OVER clause is not available in my MySQL version:

SELECT 
s.Product_ID, 
c.Category_Name,
((SUM(s.Sales_Value * s.Sales_Quantity) over (partition BY c.Category_ID) /
SUM(s.Sales_Value) over (partition BY c.Category_ID)) as average_sales_price
FROM Sales s
JOIN Categories c ON c.Category_ID = s.Category_ID;

错误:

检查与您的 MySQL 服务器版本相对应的手册，以了解在第 4 行处使用 near 'over (partition BY c.Category_ID)/SUM(s.Sales_Value) over (partition BY c.Cate') 的正确语法

check the manual that corresponds to your MySQL server version for the right syntax to use near 'over (partition BY c.Category_ID) / SUM(s.Sales_Value) over (partition BY c.Cate' at line 4

我还可以使用哪些其他 SQL 来获得预期结果?

What other SQL can I use to get the expected result?

推荐答案

对于旧版本，可以使用关联子查询:

For older version, you can use correlated sub-query :

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 
     Categories c 
     ON c.Category_ID = s.Category_ID;

这里是 SQL Fiddle.

这篇关于获取加入第二个表的聚合平均值并将它们显示在第一个表中的每个值旁边的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！