Tecnologia da Informação
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;
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:
- Converter Char Argv[] Em Int
atoi(argv[1]) converte o argumento da posição [1] do vetor argv[] em inteiro. fonte: http://linguagemc.com.br/argumentos-em-linha-de-comando/...
- 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...
- Postgresql - Calcular Subtotal / Total - Equivalente Ao With Rollup No Mysql
Neste artigo, vamos mostrar 2 exemplos de como utilizar a função "SUM" para calcular o subtotal / total em uma consulta. Para quem usa o Mysql, a consulta que vamos fazer é semelhante ao "WITH ROLLUP". Caso ainda não conheça a função...
- Postgresql - Função Lpad
Neste artigo, vamos mostrar 2 exemplos, da utilização da função LPAD. Utilizamos a função LPAD para completar uma string do lado esquerdo com determinado(s) caractere(s). O script dos exemplos, também estão no GitHub. SINTAXE LPAD (string,...
- Postgresql - Função Substring
A função substring retorna parte (um pedaço) de uma string. Seu retorno depende dos argumentos passados. Os argumentos passados podem ser: posição de caracteres de uma string;expressão regular no padrão POSIX;Neste artigo serão demostrados...
Tecnologia da Informação
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:
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
@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;
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:
Exibir a classificação dos atletas por grupo ("A", "B", "C").
SOLUÇÃO
Podemos resolver este problema de duas formas:
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.
@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;
Deixe o seu comentário ou sugestão.
Gostou? Siga no Google + ou Facebook.
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.
loading...
- Converter Char Argv[] Em Int
atoi(argv[1]) converte o argumento da posição [1] do vetor argv[] em inteiro. fonte: http://linguagemc.com.br/argumentos-em-linha-de-comando/...
- 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...
- Postgresql - Calcular Subtotal / Total - Equivalente Ao With Rollup No Mysql
Neste artigo, vamos mostrar 2 exemplos de como utilizar a função "SUM" para calcular o subtotal / total em uma consulta. Para quem usa o Mysql, a consulta que vamos fazer é semelhante ao "WITH ROLLUP". Caso ainda não conheça a função...
- Postgresql - Função Lpad
Neste artigo, vamos mostrar 2 exemplos, da utilização da função LPAD. Utilizamos a função LPAD para completar uma string do lado esquerdo com determinado(s) caractere(s). O script dos exemplos, também estão no GitHub. SINTAXE LPAD (string,...
- Postgresql - Função Substring
A função substring retorna parte (um pedaço) de uma string. Seu retorno depende dos argumentos passados. Os argumentos passados podem ser: posição de caracteres de uma string;expressão regular no padrão POSIX;Neste artigo serão demostrados...