mysql - SQL 分组项的最大平均值

Question

在线商店建模，用户可以在其中购买商品并对他们购买的商品进行评分。有四张表

购买(用户，商品)

用户(用户ID，名称)

价格(项目、评级)

商品(商品ID、描述、价格)

我想向每个用户返回一份他们未购买但平均评分最高的商品列表。

到目前为止我构建的代码有点笨拙，但似乎完成了一半的工作:

SELECT U.UserID, U.name, I.ItemID, I.Description, AVG(R.Rating) AS avgRate
FROM Item I, User U, Rates R 
WHERE R.Item=I.ItemID 
AND NOT EXISTS 
(SELECT Item  FROM Buys B  WHERE I.ItemID = B.item and U.UserID = B.user) 
group by U.UserID, item,I.ItemID Order by U.UserID, avgRate DESC

这给了我以下输出:

UserID   name   ItemID  Decription           avgRate
1        Jones   5      Computing Textbook   5
1        Jones   11     Tennis Ball          3.5
1        Jones   12     Tennis Raquet        4
2        Brown   5      Computing Textbook   5
2        Brown   11     Tennis Ball          3.5
2        Brown   12     Tennis Raquet        4

但我希望通过输出只为每个用户选择一项:

UserID  name    ItemID  Decription           avgRate
1       Jones   5       Computing Textbook   5
2       Brown   5       Computing Textbook   5

我尝试过限制排序，但它只给了我 1 行，而且你不能将 MAX() 函数与 AVG 一起使用...

Answer 1

假设您可能使用的同一用户的两种产品的最高平均值永远不会相同:

with cte1 (UserID,name,ItemID,Decription,avgRate)
as
(
SELECT U.UserID, U.name, I.ItemID, I.Description, AVG(R.Rating) AS avgRate
FROM Item I, User U, Rates R 
WHERE R.Item=I.ItemID 
AND NOT EXISTS 
(SELECT Item  FROM Buys B  WHERE I.ItemID = B.item and U.UserID = B.user) 
group by U.UserID, item,I.ItemID Order by U.UserID, avgRate DESC
)
,
cte2 (UserID,name,ItemID,Decription,avgRate,rank1) as
(
select * , rank() over (partition by name order by avgrate)from cte1
)
select UserID,name,ItemID,Decription,avgRate from cte2 where rank1 in
(
select max(rank1) from cte2 group by userid
) 
order by userid