在SQL Server中计算平均评分

Question

这是我的表格：

我想要获取包含服务描述“Plaster”或技能“Plaster”或属于类别“Plaster”的供应商记录，并计算这些供应商的平均评分。

注意：如果没有任何供应商的评论，则仍然应该返回那些记录。

这是我的查询语句：

select * from UserDetails u
  ,VendorInCategory v
  ,CategoryMaster c
  ,Review rv
where v.CategoryId=c.Id 
and u.Id=r.UserId 
and u.Id=rv.VendorId  
and v.VendorId=u.Id 
and ((u.ServiceDescription like '%Plaster%' ) 
or (u.Skills like '%Plaster%') 
or (c.Name like '%Plaster%'))

上述查询中的问题是，我没有得到那些没有评论的供应商。

但我也想得到那些没有评论但符合我的条件的供应商。

用户详细信息：

id     Servicedescription         Skills
1        Plaster                  plaster

2        construction             construvtion

3        plaster                  plaster

4        null                     null(not vendor)

5        null                     null(not vendor)

回顾

id     CustomerId     Vendorid    rating

1       4                1          3

2       5                1          3

预期输出：

VendorId     ServiceDescription     Skills       averagerating

1              plaster              plaster           3

3              plaster              plaster           0

注意：最终输出应按平均评分降序排列。

- user4155786

---

不良习惯：使用旧式JOIN语句。旧式的“逗号分隔表列表”已经在20多年前被“正确”的ANSI JOIN语法所取代，并且不建议使用，这是在ANSI-92 SQL标准中规定的。 - marc_s

你介意给我们提供样本数据和期望结果吗？ - Felix Pamittan

我已经发布了样例输入和期望输出，请查看。 - user4155786

有没有人能提供给我查询语句？我被这个问题困扰得很厉害。 - user4155786

http://sqlfiddle.com/#!3/f6566/1 - Suraj Singh

Answer 1

请试一下这个：

样例数据

create table UserDetails(
    Id int,
    ServiceDescription varchar(20),
    Skills varchar(20)
)
create table Review(
    Id int,
    CustomerId int,
    VendorId int,
    Rating int
)

insert into UserDetails values(1, 'Plaster', 'plaster'),(2, 'construction', 'construction'),(3, 'plaster', 'plaster');
insert into Review values(1, 4, 1, 3),(2, 5, 1, 3);

解决方案

select
    u.Id as VendorId,
    u.ServiceDescription,
    u.Skills,
    isnull(sum(r.rating)/count(r.rating), 0) as AverageRating
from UserDetails u
left join Review r
    on r.VendorId = u.id
where
    u.ServiceDescription like '%plaster%'
    or u.Skills like '%plaster%'
group by 
    u.Id,
    u.ServiceDescription,
    u.Skills
order by AverageRating desc

- Felix Pamittan

---

你可以用avg(r.rating)替换sum(s.rating)/count(r.rating)。 - Felix Pamittan

非常感谢您的帮助。非常感谢您，也请继续像这样提供帮助。正是因为像您这样的人，我才喜欢在Stack Overflow上发布我的问题。谢谢！ - user4155786

没问题。很高兴能帮助到您。 - Felix Pamittan

但是如果我想包括所有用户详细信息，例如全名、电子邮件等怎么办？ - user4155786

@mariapithia 没问题。 - Felix Pamittan

Answer 2

很多用户使用AVERAGE函数来计算一系列数据的平均值。但是如果您拥有汇总数据而不是单独的回答，并且需要计算平均值该怎么办？（例如，选定每个评级的人数，如产品的评级。）

如何计算加权平均数

假设您想获取每个产品的平均总体评分：

• 对于评分1，有（9）九人。
• 对于评分2，有（13）十三人。
• 对于评分3，有（1）一个人。

使用AVERAGE函数将导致 7.7的平均值。当然，这没有任何意义。我们应该期望在刻度范围内的平均值（1到5）。

为了正确计算每个问题的总体响应平均值，我们需要：

1. 将选择每个评级的个体数量乘以相应的评分值（1-5）
2. 将这些计算结果相加。
3. 将该结果除以问题总响应数。

示例数据：

create table #tableRatings(
    Id int,
    Rating numeric(18,6)
)

insert into #tableRatings values(1, 4.3),(2,3.3),(3,4.8);

解决方案：

    SELECT
    SUM(
    case 
        WHEN FLOOR(rating) = 1 THEN rating
        WHEN FLOOR(rating) = 2 THEN rating *2
        WHEN FLOOR(rating) = 3 THEN rating *3
        WHEN FLOOR(rating) = 4 THEN rating *4
        WHEN FLOOR(rating) = 5 THEN rating *5        
    end    
    ) / SUM(rating)
    FROM #tableRatings

结果：

3.733870

- Javier Cañon

---

嗨，我已经使用了上面的查询语句，它返回了特定值舍入后的结果，如何在使用上述查询时显示小数值。 - Om Bala

@OmBala请确保“rating”字段类型为带小数的数字；脚本不会更改它，只会将值乘以某个数... - Javier Cañon

Answer 3

使用聚合函数 AVG()：

尝试这个：

SELECT u.id, u.ServiceDescription, u.Skills, u.fullname, u.email, AVG(ISNULL(rv.rating, 0)) averagerating
FROM UserDetails u 
INNER JOIN VendorInCategory v ON v.VendorId=u.Id 
INNER JOIN CategoryMaster c ON v.CategoryId=c.Id 
LEFT JOIN Review rv ON u.Id=rv.VendorId  
WHERE (u.ServiceDescription LIKE '%Plaster%' OR u.Skills LIKE '%Plaster%' OR 
       c.Name LIKE '%Plaster%')
GROUP BY u.id, u.ServiceDescription, u.Skills, u.fullname, u.email
ORDER BY averagerating DESC;

编辑

实现此功能的其他解决方案：

SELECT u.id, u.ServiceDescription, u.Skills, u.fullname, u.email, 
       ISNULL(rv.averagerating, 0) averagerating
FROM UserDetails u 
INNER JOIN VendorInCategory v ON v.VendorId=u.Id 
INNER JOIN CategoryMaster c ON v.CategoryId=c.Id 
LEFT JOIN (SELECT rv.VendorId, AVG(rv.rating) averagerating FROM Review rv GROUP BY rv.VendorId) rv ON u.Id=rv.VendorId  
WHERE (u.ServiceDescription LIKE '%Plaster%' OR u.Skills LIKE '%Plaster%' OR 
       c.Name LIKE '%Plaster%')
ORDER BY ISNULL(rv.averagerating, 0) DESC;

- Saharsh Shah

---

非常感谢您的帮助。非常感谢您，还希望您能继续像这样帮助。正是因为有像您这样的人，我才喜欢在 Stack Overflow 上发布我的问题。 - user4155786

但是您不认为这是合适的吗？因为我想在我的选择子句中包括超过10个列。 - user4155786

@mariapithia 是的，如果我们想要任何聚合操作，那么我们必须这样做。 - Saharsh Shah

在group by子句中指定我在select子句中选择的每个字段不会有任何区别，对吧？ - user4155786

太好了。非常感谢您花时间为我提供解决方案。 - user4155786