有两个表:
表用户:
+----+-----------+
| id | user_name |
+----+-----------+
| 1 | Alice |
| 2 | Steve |
| 3 | Tommy |
+----+-----------+
表结果:
+----+---------+-------+-------------+
| id | user_id | score | timestamp |
+----+---------+-------+-------------+
| 1 | 1 | 22 | 1410793838 |
| 2 | 1 | 16 | 1410793911 |
| 3 | 2 | 9 | 1410793920 |
| 4 | 1 | 27 | 1410794007 |
| 5 | 3 | 32 | 1410794023 |
+----+---------+-------+-------------+
到目前为止,我拥有的是“前3名”,它的运行效果非常好,看起来像这样:
SELECT MAX(m.score) AS score, u.user_name
FROM result AS r
INNER JOIN user AS u ON r.user_id = u.id
GROUP BY r.user_id
ORDER BY r.score DESC
LIMIT 3;
+-------+-----------+
| score | user_name |
+-------+-----------+
| 32 | Tommy |
| 27 | Alice |
| 9 | Steve |
+-------+-----------+
该表实际上充满了数百个结果,这只是一个示例。我正在寻找一种紧凑的算法,以相对于所有其他用户的百分比来获得特定用户的排名。目标是输出“您处于最高5%/ 10%/ 20%/ 50%”或“您低于平均水平”之类的内容。虽然很容易确定某人是否低于平均水平(得分<AVG(得分)),但我不知道如何确定其他排名。
如果我一切都正确,那只是相对最大的计算:
SELECT
user_name,
MAX(score) AS max_score,
CASE
WHEN ROUND(100*MAX(score)/maximum, 2)>=95 THEN 'In top 5%'
WHEN ROUND(100*MAX(score)/maximum, 2)>=90 THEN 'In top 10%'
WHEN ROUND(100*MAX(score)/maximum, 2)>=75 THEN 'In top 25%'
WHEN ROUND(100*MAX(score)/maximum, 2)>=50 THEN 'In top 50%'
WHEN ROUND(100*MAX(score)/maximum, 2)>=0 THEN 'Below average'
END AS score_mark
FROM
`result`
INNER JOIN `user`
ON `result`.user_id=`user`.id
CROSS JOIN
(SELECT MAX(score) AS maximum FROM `result`) AS init
GROUP BY
user_id
因此,从所有表的最高分数开始进行计数,并针对特定用户进行分组。检查小提琴。
如下所述,这种计数方法涉及一种确定平均值的简单方法(即,其全部基于总最大值)。这可能不是必需的。我的意思是,如果问题是关于根据其他分数(而不是最大分数)计算相对位置的话,那就更复杂了:
SELECT
maxs.*,
@num:=@num+1 AS order_num,
CASE
WHEN 100*(@num-1)/(user_count-1) <= 5 THEN 'In top 5%'
WHEN 100*(@num-1)/(user_count-1) <= 10 THEN 'In top 10%'
WHEN 100*(@num-1)/(user_count-1) <= 25 THEN 'In top 25%'
WHEN 100*(@num-1)/(user_count-1) <= 50 THEN 'In top 50%'
WHEN 100*(@num-1)/(user_count-1) <= 100 THEN 'Below average'
END AS score_mark
FROM
(SELECT
user_name,
MAX(score) AS max_score
FROM
`result`
INNER JOIN `user`
ON `result`.user_id = `user`.id
GROUP BY
user_id
ORDER BY
max_score DESC) AS maxs
CROSS JOIN
(SELECT
@num:=0,
COUNT(DISTINCT user_id) AS user_count
FROM
`result`) AS init
-从现在开始,我们必须首先重新计算我们的头寸,然后在此基础上进行相对计算。这是相应的小提琴。但是,在这里,我使用线性公式将第一个位置计算为“零”,将最后一个位置计算为“ 100”。如果这不是故意的话(会有一些边际情况,例如“ 50%”中的“ 3”代表小提琴中的“ 5个总数”),那么您可以将除数更改为user_count
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句