Mysql - rank de valores (numerar registros de acordo com o valor de uma coluna)

Neste artigo, vamos apresentar 7 exemplos de como exibir as posições de acordo com valores de uma coluna, ou explicando de uma forma mais simples, como ranquear os valores de uma coluna.

No 1º exemplo não levamos em consideração os empates, a partir 2º exemplo os empates serão considerados.

Caso tenha interesse, faça o download dos exemplos ou veja o script no github.
Você também pode executar os exemplos através do sqlfiddle

1º) Exemplo

Queremos exibir a classificação dos atletas em uma corrida de acordo com o tempo gasto para completar a prova.
Para exibir a classificação vamos utilizar a tabela "tb_corrida" exibida abaixo:

SOLUÇÃO:

Podemos resolver este problema de duas formas:

1ª FORMA:  declarar uma variável e utiliza-lá em um select. No nosso exemplo, vamos utilizar a variável @posicao. A variável posição é incrementada 1 unidade a cada registro.
Os registros foram ordenados de acordo com o tempo gasto através da clausula "ORDER BY".

SET @posicao:=0;
SELECT
    @posicao:=@posicao+1 AS posicao,
    inscricao,
    grupo,
    TIME_FORMAT(tempo_gasto, '%H:%i:%s') AS tempo_gasto
FROM tb_corrida
ORDER BY tempo_gasto;

Após a execução da sentença acima, teremos o resultado a seguir:

2ª FORMA:  também é possível, usar uma variável dentro de uma subquery sem a necessidade de declara-lá no inicio através da clausula "SET".
No nosso caso, criamos uma subquery que recebeu o alias (apelido) de "classificacao". Fizemos um "CROSS JOIN" da subquery "classificacao" com a tabela  "tb_corrida".
Neste exemplo, vamos utilizar a variável @posicao

SELECT
    @posicao := @posicao + 1 AS posicao,
    tb_corrida.inscricao,
    tb_corrida.grupo,
    TIME_FORMAT(tb_corrida.tempo_gasto, '%H:%i:%s') AS tempo_gasto
FROM
(SELECT @posicao := 0) classificacao CROSS JOIN tb_corrida
ORDER BY tempo_gasto;

2º) Exemplo

Queremos exibir a classificação dos atletas em uma corrida de acordo com o tempo gasto para completar a prova, levando em consideração o empate. Vamos utilizar a mesma tabela do 1º exemplo "tb_corrida".

SOLUÇÃO:

Podemos resolver este problema de duas formas:

1ª FORMA: declarar duas variáveis e utilizá-las em um select. No nosso exemplo, vamos utilizar a variável @posicao e a variável @tempo_gasto.
Caso o valor do tempo for igual ao tempo anterior manteremos o valor da variável @posicao, caso contrário o valor da variável @posicao será  incrementado em uma 1 unidade.
 

SET @posicao:=0;
SET @tempo_gasto:=NULL;
SELECT
@posicao:=
    CASE
        WHEN CAST(@tempo_gasto AS TIME)=tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
grupo,
@tempo_gasto:=  CAST(tempo_gasto AS TIME) as tempo_gasto
FROM tb_corrida
ORDER BY tempo_gasto;

Após a execução da sentença acima,  teremos o resultado da imagem seguir:

2ª FORMA: também é possível, usar as variáveis dentro de uma subquery, sem a necessidade declará-las através da clausulá "SET"
Devemos fazer um "CROSS JOIN" da subquery "classificacao" com a tabela tabela "tb_corrida".

SELECT
@posicao:=
    CASE
        WHEN CAST(@tempo_gasto AS TIME) =tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
grupo,
@tempo_gasto:= tempo_gasto as tempo_gasto
FROM
(SELECT @posicao:=0, @tempo_gasto:=CAST(NULL AS time)) classificacao 
CROSS JOIN tb_corrida
ORDER BY tempo_gasto;

3º) Exemplo

Exibir a classificação dos atletas por grupo ("A", "B", "C").

SOLUÇÃO

Podemos resolver este problema de duas formas:

1ª FORMA

Observe que os registros foram ordenados por grupo e tempo gasto.
Cada vez que se inicia um novo grupo é atribuído o valor 1 a variável @posicao, para que a contagem seja reiniciada.
Veja o trecho destacado em azul.

SET @posicao:=0;
SET @grupo:='';
SET @tempo_gasto:=cast('00:00:00' AS time);
SELECT
@posicao:=
    CASE
        WHEN @grupo<>grupo THEN 1
        WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
@grupo := grupo as grupo,
@tempo_gasto:= CAST(tempo_gasto AS TIME)  as tempo_gasto
FROM tb_corrida
ORDER BY grupo, tempo_gasto;

Após a execução da sentença acima, teremos o resultado da imagem seguir:

2ª FORMA: também podemos resolver o terceiro exemplo utilizando subquery.

SELECT
@posicao:=
    CASE
        WHEN @grupo<>grupo THEN 1
        WHEN  CAST(@tempo_gasto AS TIME) = tempo_gasto  THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
@grupo := grupo AS grupo,
@tempo_gasto:=  CAST(tempo_gasto AS TIME) as tempo_gasto
FROM
(SELECT @posicao := 0, @tempo_gasto := CAST(NULL AS time), @grupo:='') classificacao
CROSS JOIN tb_corrida
ORDER BY grupo, tempo_gasto;

4º) Exemplo

Exibir a classificação dos atletas até a 4ª posição.

SOLUÇÃO:

Para resolver este problema os registros foram ordenados por tempo gasto.
Foi criada uma subquery, a qual chamamos de "tb_colocacoes".
A seguir, foram filtrados os registros com posição menor ou igual a 4.

SET @posicao:= 0;
SET @tempo_gasto:= NULL;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN CAST(@tempo_gasto AS time) = tempo_gasto THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    inscricao,
    grupo,
    @tempo_gasto:= CAST(tempo_gasto AS TIME) as tempo_gasto
    FROM tb_corrida
    ORDER BY tempo_gasto
) tb_colocacoes WHERE posicao <= 4;

Após a execução da sentença acima,  teremos o seguinte resultado:

5º) Exemplo

Os atletas que alcançaram até a 2ª colocação por grupo

SOLUÇÃO:

Para resolver este problema os registros foram ordenados por grupo e tempo gasto.
Foi utilizada uma subquery, a qual chamamos de "tb_colocacoes".
A seguir, foram filtrados os registros com posição menor ou igual a 2.

SET @posicao :=0;
SET @grupo :='';
SET @tempo_gasto := NULL;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN @grupo<>grupo THEN 1
            WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    inscricao,
    @grupo := grupo as grupo,
    @tempo_gasto:=   CAST(tempo_gasto AS TIME) AS tempo_gasto
    FROM tb_corrida
    ORDER BY grupo, tempo_gasto
) tb_colocacoes  WHERE posicao <= 2;

Após a execução da sentença acima, teremos o resultado da imagem seguir:

6º) Exemplo

Vamos listar o ranking de vendedores de uma loja, de acordo com o total de mercadorias vendidas. Neste exemplo, vamos utilizar uma nova tabela chamada "tb_vendas". Veja a imagem da tabela "tb_vendas" a seguir:

SOLUÇÃO

Veja que antes de criar o ranking com as posições, tivemos que somar o total para cada vendedor, esta soma foi armazenada em uma subquery que recebeu o alias (apelido) de "total vendas".
Perceba que neste exemplo, estamos ordenando os totais por vendedor de forma decrescente (do maior para o menor)
 "ORDER BY total_vendedor DESC", pois os vendedores que venderão mais serão os primeiros.  

SET @posicao:=0;
SET @total_vendedor:= 0.0;
SELECT
@posicao:=
    CASE
        WHEN @total_vendedor=total_vendedor THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
vendedor_id,
@total_vendedor:= total_vendedor as total_vendedor
FROM
(
    SELECT vendedor_id, SUM(valor) AS total_vendedor FROM
    tb_vendas GROUP BY vendedor_id
) total_vendas
ORDER BY total_vendedor DESC;

Após a execução da sentença acima, teremos o resultado da imagem seguir:

7º) Exemplo

 Listar os vendedores que foram classificados até 3º lugar. Neste exemplo, também vamos utilizar a tabela "tb_vendas".

SOLUÇÃO

Armazenamos todas as posições na subquery "vendas_gerais" e filtramos os vendedores com a posição menor ou igual a 3.

SET @posicao:=0;
SET @total_vendedor:= 0.0;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN @total_vendedor=total_vendedor THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    vendedor_id,
    @total_vendedor:= total_vendedor as total_vendedor
    FROM
    (
        SELECT vendedor_id, SUM(valor) AS total_vendedor FROM
        tb_vendas GROUP BY vendedor_id
    ) total_vendas
    ORDER BY total_vendedor DESC

) vendas_gerais WHERE posicao <= 3;

Após a execução da sentença acima, teremos o resultado da imagem seguir:

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.