文章/答案/技术大牛

发布

问MYSQL按等级打分
EN

Database Administration用户

提问于 2013-04-19 02:52:20

回答 1查看 2.7K关注 0票数 3

我正在使用MYSQL创建一个评分系统来实现我的数据库。我想要做的是按其百分比对每个属性进行评级。下面是示例数据库：

ID, value
1, 3
2, 5
3, 2
4, 5

我想要的输出是：

ID, value, rank, score
1, 3, 2, 5
2, 5, 1, 10
3, 2, 3, 0
4, 5, 1, 10

分数的值将基于等级，因此它变成如下

10*(MAX(rank)-(rank))/(MAX(rank)-MIN(rank))

我已经完成了排名查询，但仍然坚持将其转换为分数。以下是我到目前为止所得到的查询：

SELECT `ID`, `value`, FIND_IN_SET( `value`, (
SELECT GROUP_CONCAT(DISTINCT `value` 
ORDER BY `value` DESC)
FROM table) 
) AS rank
FROM table;

谢谢各位:)

另一个问题：

| ID | VALUE1 | RANK1 | VALUE2 | RANK2 | 
----------------------------------------
|  2 |      5 |     1 |     20 |      2|
|  4 |      5 |     1 |     30 |      1|
|  1 |      3 |     2 |      5 |      4|
|  3 |      2 |     3 |      8 |      3|

如何生成像表格一样的多重排名？我试过了

SELECT
    @min_rank := 1 AS min_rank
  , @max_rank1 := (SELECT COUNT(DISTINCT value1) FROM table) AS max_rank1
  , @max_rank2 := (SELECT COUNT(DISTINCT value2) FROM table) AS max_rank2
  ;
SELECT
    ID
  , R1
  , TRUNCATE(5.0+5.0 * (@max_rank1 - R1) / (@max_rank1 - @min_rank), 2) AS Score1
  , R2
  , TRUNCATE(5.0+5.0 * (@max_rank2 - R2) / (@max_rank2 - @min_rank), 2) AS Score2
FROM (
  SELECT
      ID
    , value1
    , FIND_IN_SET( `value1`, (SELECT GROUP_CONCAT(DISTINCT `value1` ORDER BY `value1` DESC) FROM table)) AS R1
    , value2
    , FIND_IN_SET( `value2`, (SELECT GROUP_CONCAT(DISTINCT `value2` ORDER BY `value2` DESC) FROM table)) AS R2
  FROM table
) ranked_table;

它工作良好，排名低于170。我的数据库有一些值的近似200+排名，排名更大，当它返回时170将被视为0。在这种情况下，一些分数将被错误地计算。

mysql

回答 1

Database Administration用户

回答已采纳

发布于 2013-04-19 05:17:43

使用描述的这里在MySQL中对项目进行排序的技术，您可以使用派生表和一些带有用户变量的黑客来计算所需的分数。

这是您提供的示例数据的表：

CREATE TABLE blah (
    id INT
  , value INT
);

INSERT blah (id, value)
VALUES 
    (1, 3)
  , (2, 5)
  , (3, 2)
  , (4, 5)
;

下面是您需要的查询：

-- find the min and max ranks for later use
SELECT
    @min_rank := 1 AS min_rank
  , @max_rank := (SELECT COUNT(DISTINCT value) FROM blah) AS max_rank
;

-- use the calculated min and max ranks to calculate score
SELECT
    id
  , value
  , rank
  , 10.0 * (@max_rank - rank) / (@max_rank - @min_rank) AS score
FROM (
  SELECT
      id
    , value
    , @curr_rank := IF(@prev_rank = value, @curr_rank, @curr_rank + 1) AS rank
    , @prev_rank := value
  FROM blah, (SELECT @curr_rank := 0) r, (SELECT @prev_rank := NULL) p
  ORDER BY value DESC
) ranked_blah;

对于SQL Fiddle的第二个查询，我得到的输出如下：

| ID | VALUE | RANK | SCORE |
-----------------------------
|  2 |     5 |    1 |    10 |
|  4 |     5 |    1 |    10 |
|  1 |     3 |    2 |     5 |
|  3 |     2 |    3 |     0 |

请注意，如果您在value上有一个索引，这种方法将工作得最好。这将使您能够使用所描述的@max_rank技术@yper多维数据集快速找到COUNT。

票数 2

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/40415

复制

相似问题

问MYSQL按等级打分
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MYSQL按等级打分EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MYSQL按等级打分
EN