mysql - SQL - 根据用户类型加权的产品评论分数

Question

我有一个产品评论场景，我们有两个级别的用户，高级用户和初级用户。高级用户评论的权重应该是初级用户评论的两倍。如果高级用户评论产品 10，初级用户评论产品 1，则得分将为 ((10x2) + 1)/3 = 7。

任何人都可以帮我编写一些 SQL 来执行此操作吗？我正在使用 MySQL 5.5。

这是一个示例数据库

create database products;
use products;
CREATE TABLE Product (ProductID int(10)  AUTO_INCREMENT, name varchar(20), primary key(ProductID));
CREATE TABLE Review (ReviewID int AUTO_INCREMENT, ProductID int, UserID int, performanceScore int, valueScore int, primary key(ReviewID));
CREATE TABLE User (UserId int AUTO_INCREMENT, StateID int, name varchar(20), primary key(UserId));
insert into product values (1, 'car1'), (2, 'car2'), (3, 'car3');
insert into review values (1, 1, 1, 10, 10), (2, 1, 2, 1, 1), (3, 1, 3, 5, 5), (4, 2, 1, 5, 5);
insert into user values (1, 2, 'SENIOR'), (2, 1, 'JUNIOR'), (3, 0, 'INACTIVE');
// NB: three reviews of car 1, one by an senior user (stateid 2), one by junior user (stateid 1), one by inactive user

下面是一些计算但没有加权的代码

SELECT p.*, SUM(CASE WHEN u.stateID NOT IN (1) THEN 1 ELSE 0 END) AS reviewCount,
CASE WHEN u.stateID >= 1 THEN (avg(r.performanceScore) + avg(r.valueScore))/2 ELSE NULL END as score
FROM product p
LEFT OUTER JOIN review r ON p.ProductID = r.ProductId
LEFT JOIN user u ON u.userId = r.userId
GROUP BY p.ProductID

伪代码是这样的

$score = 
  (
    if (user.stateID = SENIOR) 2xperformanceScore + 2*valueScore
    if (user.stateID = JUNIOR) performanceScore + valueScore
  )
  / (2xSeniorUser + 1xJuniorUsers)

任何帮助或提示将不胜感激:)

Answer 1

我认为这样做会: SQL Fiddle

查询 1:

SELECT p.*, SUM(CASE WHEN u.stateID NOT IN (1) THEN 1 ELSE 0 END) AS reviewCount,
GROUP_CONCAT(r.performanceScore) performanceScore, -- this is just for your information
GROUP_CONCAT(r.valueScore) valueScore,             -- this is just for your information
GROUP_CONCAT(CASE u.name WHEN 'SENIOR' THEN 2 WHEN 'JUNIOR' THEN 1 ELSE 0 END) users, -- this is just for your information
SUM((r.performanceScore + r.valueScore) * CASE u.name WHEN 'SENIOR' THEN 2.0 WHEN 'JUNIOR' THEN 1.0 ELSE 0.0 END)/
SUM(CASE u.name WHEN 'SENIOR' THEN 2 WHEN 'JUNIOR' THEN 1 ELSE 0 END)as score
FROM product p
LEFT OUTER JOIN review r ON p.ProductID = r.ProductId
LEFT JOIN user u ON u.userId = r.userId
GROUP BY p.ProductID

Results :

| PRODUCTID | NAME | REVIEWCOUNT | PERFORMANCESCORE | VALUESCORE | USERS |    SCORE |
|-----------|------|-------------|------------------|------------|-------|----------|
|         1 | car1 |           2 |           6,10,1 |     5,10,2 | 0,2,1 | 14.33333 |
|         2 | car2 |           1 |                5 |          5 |     2 |       10 |
|         3 | car3 |           0 |           (null) |     (null) |     0 |   (null) |