php - 执行 "weighted"SQL 查询

Question

在我当前的设置中，我有两个表:product 和 rating。

产品表

• 产品编号
• 评分

product 表包含一大堆附加信息，但对于这个问题，我只关注这两个字段。

评级表

• 产品编号
• 评分
• user_id(评价者)
• is_admin - 关于评分的用户是否为管理员的 bool 值

我们首先收集管理员评分的原因是，与普通用户 (40%) 相比，我们希望管理员评分 (60%) 略高一些。 product 表中的 rating 列等于所有管理员评分的 AVG。评分一般在 1 到 5 之间。

因此对于每个产品，我们必须考虑四种情况:

RATINGS BY     TOTAL
USER  ADMIN    RATING
----  -----
 no    no   = 0
 yes   no   = AVG of user ratings (`ratings` table) 
 yes   yes  = 0.6 AVG of admin ratings (`product_table`) + 0.4 AVG of user ratings (`ratings` table) 
 no    yes  = AVG of admin ratings (`product_table`)

当前检索数据集的 SQL 查询如下所示:

$sql = "SELECT p.product_id, 
(COALESCE(p.rating,0)+COALESCE(j.sum,0)) / (COALESCE(p.rating/p.rating,0) 
   + COALESCE(j.tot,0)) AS rating  
FROM product p  
LEFT JOIN   
   (SELECT SUM(rating) AS sum , 
      COUNT(rating) AS tot, 
      product_id FROM rating 
   WHERE is_admin_rating=FALSE GROUP BY product_id) j 
ON (p.product_id = j.product_id) LEFT JOIN product_description pd 
   ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s 
   ON (p.product_id = p2s.product_id)";

此查询随后会附加各种不同的排序选项(评级为默认值)，此外我们还使用 LIMIT 对搜索结果进行“分页”。

有没有办法将加权评级合并到查询中？还是我必须将其分解为多个查询？

Answer 1

由于这显然看起来像一个基于 Web 的系统，我强烈建议对产品表进行轻微的反规范化和添加 5 列

UserRatings, UserCount, AdminRatings, AdminCount, FinalRating

当任何条目添加或更新到评级表时，您可以应用一个简单的更新触发器，例如

update Product p,
       ( select r.product_id,
                sum( is_admin_rating=FALSE, 1, 0 ) as UserCount,
                sum( is_admin_rating=FALSE, rating, 0 ) as UserRatings,
                sum( is_admin_rating=TRUE, 1, 0 ) as AdminCount,
                sum( is_admin_rating=TRUE, rating, 0 ) as AdminRatings
            from Ratings r
            where r.product_id = ProductIDThatCausedThisTrigger
            group by r.product_id ) as PreSum
   set p.UserCount = PreSum.UserCount,
       p.UserRatings = PreSum.UserRatings,
       p.AdminrCount = PreSum.AdminCount,
       p.AdminRatings = PreSum.AdminRatings,
       p.FinalRating = case when PreSum.UserCount = 0 and PreSum.AdminCount = 0
                               then 0
                            when PreSum.UserCount = 0 
                               then PreSum.AdminRatings / PreSum.AdminCount
                            when PreSum.AdminCount = 0 
                               then PreSum.UserRatings / PreSum.UserCount
                            else
                               ( PreSum.UserRatings / PreSum.UserCount * .4 )
                              / ( PreSum.AdminRatings / PreSum.AdminCount * .6 )
                            end
   where p.product_id = PreSum.product_id

这样一来，您将永远不必单独连接到评级表并进行聚合，聚合只会随着数据的积累而变慢。然后您的查询就可以只使用这些表而不必担心合并，每个表的计数和它们的评分都会在那里。

FinalRatings 的情况/时间基本上完成了所有操作，因为用户计数和管理员计数的组合可以是 0/0、+/0、0/+ 或 +/+

So, if no count for either, the case/when sets rating to 0
if only the user count has a value, just get that average rating (userRatings / userCounts)
if only the admin count has a value, get admin avg rating (adminRatings / adminCounts)
if BOTH have counts, you are taking the respective averages * .4 and * .6 respectively.  This would be the one factoring adjustment you might want to  tweak.

尽管查询本身看起来有些怪异和令人困惑，但如果您查看“PreSum”查询，您只是针对刚刚评级的 1 个产品和触发器的基础执行此操作。然后，根据单个产品 ID 加入的结果进行简单更新。

让它发挥作用可能会为您提供更好的长期解决方案。