根据MySQL中的多列确定排名 [英] Determine Rank based on Multiple Columns in MySQL
问题描述
我有一个包含3个字段的表，我想根据user_id和game_id对列进行排名.
这是SQL Fiddle: http://sqlfiddle.com/#!9/883e9d/1
我已经有桌子了
user_id | game_id | game_detial_sum |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |
预期输出:
user_id | game_id | game_detial_sum | user_game_rank |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |
到目前为止，我的努力:
SET @s := 0;
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs
(来自OP 评论):排序基于game_detail
game_detail的顺序
在 docs 和@Gordon Linoff的观察结果:
涉及用户变量的表达式的求值顺序为 不明确的.例如，不能保证SELECT @a，@a:= @ a + 1 首先评估@a，然后执行赋值.
我们需要评估行号，并将user_id值分配给同一表达式中的@u变量.
SET @r := 0, @u := 0;
SELECT
@r := CASE WHEN @u = dt.user_id
THEN @r + 1
WHEN @u := dt.user_id /* Notice := instead of = */
THEN 1
END AS user_game_rank,
dt.user_id,
dt.game_detail,
dt.game_id
FROM
( SELECT user_id, game_id, game_detail
FROM game_logs
ORDER BY user_id, game_detail DESC
) AS dt
结果
| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1 | 6 | 260 | 11 |
| 2 | 6 | 100 | 10 |
| 1 | 7 | 1200 | 10 |
| 2 | 7 | 500 | 11 |
| 3 | 7 | 260 | 12 |
| 4 | 7 | 50 | 13 |
MySQL 文档中的有趣注释，我最近发现的:
以前的MySQL版本可以为一个值分配一个值 SET以外的语句中的用户变量.此功能是 MySQL 8.0支持向后兼容，但受制于 在将来的MySQL版本中删除.
另外，由于有一个SO成员，MySQL团队也访问了此博客:://mysqlserverteam.com/row-numbering-ranking-how-to-to-use-less-user-variables-in-mysql-queries/
通常的观察结果是，在同一查询块中使用ORDER BY评估用户变量并不保证值始终正确.因此，MySQL优化程序 可能会出现，并更改我们的假定的评估顺序.
解决此问题的最佳方法是升级到MySQL 8+，并使用 在DB Fiddle上查看
I have a table which has 3 fields, I want to rank column based on user_id and game_id.
Here is SQL Fiddle : http://sqlfiddle.com/#!9/883e9d/1
the table already I have :
user_id | game_id | game_detial_sum |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |
expected output :
user_id | game_id | game_detial_sum | user_game_rank |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |
My efforts so far :
SET @s := 0;
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs
Edit: (From OP Comments): Ordering is based on the descending order of game_detail
order of game_detail
In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.
Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.
Edit: Based on MySQL docs and @Gordon Linoff's observation:
The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.
We will need to evaluate row number and assign the user_id value to @u variable within the same expression.
SET @r := 0, @u := 0;
SELECT
@r := CASE WHEN @u = dt.user_id
THEN @r + 1
WHEN @u := dt.user_id /* Notice := instead of = */
THEN 1
END AS user_game_rank,
dt.user_id,
dt.game_detail,
dt.game_id
FROM
( SELECT user_id, game_id, game_detail
FROM game_logs
ORDER BY user_id, game_detail DESC
) AS dt
Result
| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1 | 6 | 260 | 11 |
| 2 | 6 | 100 | 10 |
| 1 | 7 | 1200 | 10 |
| 2 | 7 | 500 | 11 |
| 3 | 7 | 260 | 12 |
| 4 | 7 | 50 | 13 |
An interesting note from MySQL Docs, which I discovered recently:
Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.
Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/
General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.
Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:
Schema (MySQL v8.0)
SELECT user_id,
game_id,
game_detail,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY game_detail DESC) AS user_game_rank
FROM game_logs
ORDER BY user_id, user_game_rank;
Result
| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6 | 11 | 260 | 1 |
| 6 | 10 | 100 | 2 |
| 7 | 10 | 1200 | 1 |
| 7 | 11 | 500 | 2 |
| 7 | 12 | 260 | 3 |
| 7 | 13 | 50 | 4 |
这篇关于根据MySQL中的多列确定排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
