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.

ORACLE - Calcular total /subtotal com ROLLUP

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função "SUM" acompanhada do comando "ROLLUP" para calcular o subtotal / total em uma consulta.

Caso tenha interesse, faça o download ou veja os scripts deste artigo no github.
Você também pode executar os scripts e visualizar as tabelas no sqlfiddle. O script foi testado no Oracle 11g R2.

1º  Exemplo

Vamos calcular as despesas com fornecedor para cada segmento de uma empresa.
Iremos exibir o subtotal por segmento: "Papelaria e informática", "Marcenaria", "Serralheria" e "Limpeza e higiêne".
Também vamos exibir no final da consulta o total geral para todos os segmentos.
Neste exemplo, utilizaremos a tabela "tb_fornecedor". Veja a imagem da tabela a seguir:

Solução

Vamos utilizar a função "SUM" acompanhada de "GROUP BY" para fazer a quebra por segmento. Além disso vamos utilizar o "ROLLUP" para exibir o subtotal embaixo de cada quebra e o total geral no último registro.

Veja a sentença abaixo:

SELECT
    segmento,
    produto,
    SUM(valor)
FROM tb_fornecedor
GROUP BY ROLLUP(segmento, produto);

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

2º  Exemplo

Para facilitarmos a interpretação do resultado da consulta vamos alterar o conteúdo da primeira coluna:

• Nos registros que exibirem o subtotal, a primeira coluna será acompanhada da palavra "Subtotal" e do nome do segmento;
• O ultimo registro da primeira coluna se chamará "Total Geral";
• Nos outros registros o nome do segmento será mantido;

Solução

Veja a sentença abaixo: 

SELECT
CASE
WHEN (segmento IS NULL AND produto IS NULL) 
  THEN 'Total Geral'
WHEN (segmento IS NOT NULL AND produto IS NULL) 
  THEN  'Subtotal' || ' - ' || segmento  
ELSE segmento
END AS segmento,
produto,
SUM(valor)
FROM tb_fornecedor GROUP BY ROLLUP (segmento, produto);

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

Foi utilizado o comando "CASE WHEN" na primeira coluna para verificar:

• se o segmento e o produto eram nulos,  caso fossem, houve a substituição por "Total Geral";
• se o segmento não era nulo e o produto era nulo, caso fossem, houve a substituição por "Subtotal" concatenado com o nome do segmento;
• se nenhuma condição acima fosse verdadeira, mantivemos o conteúdo da coluna segmento;

Observação: utilizamos o operador de concatenação "||" para unir a palavra "Subtotal" ao conteúdo da coluna segmento;

Também podemos indicar o total geral na primeira coluna e os subtotais na segunda coluna.  Veja a sentença abaixo:

SELECT
CASE
WHEN (segmento IS NULL AND produto IS NULL) 
  THEN 'Total Geral'
ELSE segmento
END AS segmento,
CASE
WHEN (segmento IS NOT NULL AND produto IS NULL) 
  THEN 'Subtotal' || ' - ' || segmento
ELSE produto 
END AS produto,
SUM(valor) AS valor
FROM tb_fornecedor
GROUP BY ROLLUP (segmento, produto);

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

3º  Exemplo

Neste exemplo, vamos mostrar como ordenar as colunas "segmento" e "produto", mantendo o "Total Geral" no último registro e o subtotal no final de cada segmento.

Solução

Vamos utilizar "ORDER BY" em uma subquery (subconsulta).

Veja a sentença abaixo: 

SELECT
COALESCE(segmento, 'TOTAL GERAL')  AS segmento,
CASE
 WHEN ((segmento IS NOT NULL) AND (produto IS NULL))
  THEN CONCAT('Subtotal - ', segmento)
  WHEN (segmento IS NOT NULL AND produto IS NOT NULL)
  THEN produto
END AS produto,
valor FROM
(
 SELECT
 segmento, 
 CASE
  WHEN(segmento IS NOT NULL) THEN 0
  ELSE 1
 END AS segmento_ordem, 
 produto, 
 CASE
  WHEN(produto IS NOT NULL) THEN 0
 ELSE 1
 END  AS produto_ordem,
 SUM(valor) AS valor
 FROM tb_fornecedor
 GROUP BY ROLLUP(segmento, produto)
) TOTAL
ORDER BY
segmento,
segmento_ordem,
produto_ordem,
produto;

A subquery que criamos está destacada na cor azul e está posicionada entre parenteses, esta subquery recebeu o alias (apelido) de TOTAL.

Observação: perceba que utilizamos duas vezes o "CASE WHEN" na subquery:

• o primeiro para que o "TOTAL GERAL" fosse exibido no último registo da coluna "segmento" independente da ordem alfabética;
• o segundo para que o "Subtotal" fosse exibido no final de cada segmento da coluna "produto" independente da ordem alfabética;

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

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