working - select mysql




Determine a classificação com base em várias colunas no MySQL (3)

Eu tenho uma tabela que tem 3 campos, quero classificar coluna com base em user_id e game_id.

Aqui está o SQL Fiddle: http://sqlfiddle.com/#!9/883e9d/1

a mesa já tenho:

 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                | 

saída esperada:

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              |

Meus esforços até agora:

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:[email protected]s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

Edit: (De OP Comments ): Ordenação é baseada na ordem decrescente de game_detail

ordem de game_detail


A melhor solução no MySQL, anterior à versão 8.0, é a seguinte:

select gl.*, 
       (@rn := if(@lastUserId = user_id, @rn + 1,
                  if(@lastUserId := user_id, 1, 1)
                 )
        ) as user_game_rank
from (select gl.*
      from game_logs gl
      order by gl.user_id, gl.game_detail desc
     ) gl cross join
     (select @rn := 0, @lastUserId := 0) params;

A ordenação é feita em uma subconsulta. Isso é necessário a partir do MySQL 5.7. As atribuições de variáveis ​​são todas em uma expressão, portanto, diferentes ordens de avaliação de expressões não importam (e o MySQL não garante a ordem de avaliação de expressões).


Em uma Tabela Derivada (subconsulta dentro da cláusula FROM ), game_detail nossos dados de modo que todas as linhas que possuem os mesmos valores de user_id se juntem, com ordenação adicional entre elas com base em game_detail na ordem Decrescente.

Agora, usamos esse conjunto de resultados e usamos expressões condicionais CASE..WHEN para avaliar a numeração de linhas. Será como uma técnica de looping (que usamos no código do aplicativo, por exemplo: PHP). Armazenaríamos os valores da linha anterior nas variáveis ​​definidas pelo usuário e, em seguida, verificaríamos o valor da linha atual em relação à linha anterior. Eventualmente, vamos atribuir o número da linha de acordo.

Edit: Baseado em docs MySQL e na observação do @Gordon Linoff:

A ordem de avaliação para expressões envolvendo variáveis ​​do usuário é indefinida. Por exemplo, não há garantia de que SELECT @a, @a: = @ a + 1 avalie @a primeiro e depois realize a atribuição.

Precisaremos avaliar o número da linha e atribuir o valor user_id à variável @u dentro da mesma expressão.

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 

Resultado

| 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      |

Ver no DB Fiddle

Uma nota interessante do MySQL docs , que descobri recentemente:

Versões anteriores do MySQL tornaram possível atribuir um valor a uma variável de usuário em instruções diferentes de SET. Esta funcionalidade é suportada no MySQL 8.0 para compatibilidade com versões anteriores, mas está sujeita a remoção em uma versão futura do MySQL.

Além disso, graças a um colega membro do SO, deparei com este blog pelo MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

A observação geral é que o uso de ORDER BY com avaliação das variáveis ​​do usuário no mesmo bloco de consulta não garante que os valores estejam corretos sempre. Como, o otimizador do MySQL pode entrar em vigor e alterar a nossa ordem presumida de avaliação.

A melhor abordagem para esse problema seria atualizar para o MySQL 8+ e utilizar a funcionalidade Row_Number() :

Esquema (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;

Resultado

| 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              |

Ver no DB Fiddle


SELECT user_id, game_id, game_detail, 
       CASE WHEN user_id = @lastUserId 
            THEN @rank := @rank + 1 
            ELSE @rank := 1 
       END As user_game_rank,
       @lastUserId := user_id
FROM game_logs
cross join (select @rank := 0, @lastUserId := 0) r
order by user_id, game_detail desc

Demonstração SQLFiddle





sql-rank