根据VB.NET中的平均评分给出排名 [英] Give ranking as per average rating in VB.NET

问题描述

我有一个项目，假设有50家医院。没有任何用户可以评价这家医院。现在根据投票到某个特定医院的时间，我得到了特定医院的平均评分。现在根据平均评分，我想给他们排名。这是我坚持的地方。因为每当新用户投票任何医院时，平均评级就会波动。每个医院都有六种不同的评级（10）所以他们总共有（60）。根据特定医院的每一个评级（总和）除以医院投票的时间数* 6（因为6种类型的评级）。这就是我构建我的sql Query以获得平均评分的方式。





现在我怎么能用平均排名所有医院收视率。我只需要了解逻辑。



我尝试过：

I have a project where suppose there are 50 hospitals. No any user can rate this hospitals. Now based on number of time voted to one particular hospital I get the average rating of particular hospitals. Now based on average rating I want to give them rankings. This is where I stucked. Because Average ratings get fluctuate every time when any new user vote any hospitals. Every hospitals has six different kind of rating from (10) So they will have total of (60). Base on every single rating (sum) to particular hospital gets divided by number of time that hospital voted * 6 (because 6 types of ratings). This is how I structured my sql Query to get avg rating which is working.


Now how Can I Rank all hospitals with their avg ratings. I just need to understand the logic.

What I have tried:

SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0) AverageRating, COUNT(ID) RatingCount FROM ratings WHERE hospitalID = '1111'

推荐答案

您可以通过分配对结果进行排名一个行号和按计算的平均值排序。



不幸的是MySQL没有内置的行号功能（但是）但是我发现了OMG的一个巧妙的技巧这个链接的小精灵[ ^ ]



演示：

我创建了一个表：

You can rank the results by assigning a row number and ordering by the calculated average.

Unfortunately MySQL doesn't have a built-in row number function (yet) but I found a neat trick by OMG Ponies on this link[^]

Demo:
I created a table thus:

create table myTable
(
  id int not null auto_increment,
  aValue char(30),
  primary key (id)
);

INSERT INTO myTable (aValue) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

数据并不重要，但我注意到值不是按字母顺序排列。

此查询

The data isn't that important but I took care that the values were not in alpha order.
This query

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM myTable t, 
       (SELECT @rownum := 0) r
order by aValue desc;

生成这些结果

generates these results

id  aValue  rank
5   whale    1  
3   penguin  2 
6   ostrich  3 
4   lax      4 
1   dog      5 
2   cat      6 

但如果我所做的只是更改订单

but if all I do is change the order

order by aValue asc;

我得到

I get

id  aValue  rank
2   cat      1 
1   dog      2 
4   lax      3 
6   ostrich  4 
3   penguin  5 
5   whale    6 

即等级是动态的



[OP评论后编辑]

我认为您的查询需要如下所示：

I.e. the "rank" is dynamic


I think your query needs to look like this:

SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0) AverageRating, 
COUNT(ID) RatingCount,
       @rownum := @rownum + 1 AS rank
  FROM ratings t, 
       (SELECT @rownum := 0) r
WHERE hospitalID = '1111'
order by 1 desc

注意 - 这是未经测试的，因为我没有任何样本数据可供使用。





现在我已经看到了示例数据，这个解决方案有效。

首先，我必须创建一个临时表，因为您已将数值数据存储为varchar类型。您永远不应该这样做 - 始终使用适当的数据类型来处理列中的数据。 Varchar / char应仅用于字母数字数据（例如名称）。

Note - this is untested as I don't have any sample data to work from.


Now that I've seen the sample data, this solution works.
Firstly I had to create a "temporary" table because you have stored numeric data as type varchar. You should never do this - always use the appropriate datatype for the data that will be in the column. Varchar/char should only be used for alphanumeric data (like names for example).

CREATE TABLE rateTemp
(
  `hospitalID` varchar(100) NOT NULL,
  `AverageRating` DECIMAL(15,2),
  RatingCount INT
);
INSERT INTO rateTemp
SELECT hospitalID, CAST(IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0) AS decimal(15,2)) AverageRating, 
COUNT(ID) RatingCount
FROM ratings t
GROUP BY hospitalID

然后，我可以使用上面使用的技术生成每家医院的排名并将结果加回到原始表格

I can then use the technique I used above to generate the rank of each hospital and join that result back to the original table

SELECT DISTINCT A.hospitalID, B.AverageRating, B.RatingCount, rank
FROM 
ratings A
INNER JOIN 
  (SELECT @rownum := @rownum + 1 AS rank, hospitalID, AverageRating, RatingCount
    FROM rateTemp t, (SELECT @rownum := 0) r
    ORDER BY AverageRating DESC
) B ON A.hospitalID=B.hospitalID
WHERE A.hospitalID = '446795'

对于这个例子，我得到了结果R ank = 4.

注意 - 如果不修复原始表模式，则每次都必须重新创建rateTemp表。如果你确实修复了你的表模式，那么你可以完全摆脱rateTemp

For this example I got the result Rank = 4.
Note - if you don't fix your original table schema then you will have to recreate the rateTemp table each time. If you do fix your table schema then you can probably get rid of rateTemp altogether

这篇关于根据VB.NET中的平均评分给出排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！