Select com group by Total Frequencia + Assiduidade que deve ser comparada sempre a cada frequência anterior.

SQL

Delphi

Firebird

24/04/2023

Select com group by Total Frequencia + Assiduidade que deve ser comparada sempre a cada frequência anterior.

Preciso de ajuda em um projeto sobre futebol desde já agradeço, as consultas que preciso são: Total Frequencia + Assiduidade, sendo essa segunda mais complexa, exemplo:

Cada mês são 4 jogos (relatório é sempre puxado do total de 3 meses ou 90 dias), select abaixo já esta organizado por Total de frequencia, faltando organizar por assiduidade que deve ser comparada sempre a cada frequência anterior.

Exemplo:
Dois atletas empatam com 8 frequencias, sendo que o primeiro faltou o último jogo 07/04 e o segundo jogador não, então o segundo jogador fica na frente devido ordem de assiduidade, caso continuem empatados, precisa comparar o penultimo jogo que foi jogo 01/04, e assim por diante até chegar em um jogo que um faltou e esse ficará abaixo na ordem da relação: Total Frequencia + Assiduidade.

Tabelas resumidas são:

Jogador
ncod_jogador
cnome_joagador

Frequencias
ncod_jogador
nqtdeFrequencia
dt_Frequencia


Abaixo o select inicial que falta ordenar por assiduidade:


select
 frequencias.ncod_jogador,
 jogador.cnome_jogador
sum(nqtdeFrequencia) as Total_Freq

from frequencias
inner join jogador on (frequencias.ncod_jogador = jogador.ncod_jogador)
 where CAST(frequencias.dt_frequencia as date)
 BETWEEN cast( dateadd (day, -90,current_date) as date) and cast(current_date as date)

 Group by
 frequencias.ncod_jogador,
 jogador.cnome_jogador
Order by Total_Freq DESC;




Obrigado.
Joatan Lima

Joatan Lima

Curtidas 0

Melhor post

Frank Hosaka

Frank Hosaka

24/04/2023

Para mim, assiduidade e frequência é a mesma coisa.
Alterei a tabela assim
jogador (id,nome) -> (1,"Pelé"),(2,"Rivelino")
frequencia (id,id_jogador,data_jogo) -> (1,1,2023-04-01),(2,1,2023-04-12),(3,1,2023-04-24),(4,2,2023-04-24),(5,1,203-04-30)
Criei uma consulta com o nome de vw_frequencia assim:
CREATE 
VIEW `vw_frequencia` AS
    SELECT 
        `frequencia`.`id_jogador` AS `id_jogador`,
        `jogador`.`nome` AS `nome`,
        COUNT(`frequencia`.`data_jogo`) AS `frequencia`
    FROM
        (`frequencia`
        JOIN `jogador` ON ((`frequencia`.`id_jogador` = `jogador`.`id`)))
    GROUP BY `jogador`.`nome`
    ORDER BY `frequencia` DESC

E o resultado foi esse:
id_jogador nome frequencia
1	Péle	4
2	Rivelino	1


GOSTEI 1

Mais Respostas

Arthur Heinrich

Arthur Heinrich

24/04/2023

Estamos falando de uma análise de 90 dias (3 meses) em que cada mês tem 4 jogos, dando um total de 12 jogos.

Imagino fazer o seguinte:

1 - Identificamos as datas dos jogos em ordem crescente
2 - Criamos um rank "R" para as datas, de forma que a data mais antiga valha 1 e a mais recente valha 12
3 - Para cada jogador, criamos um número que identifica o peso de sua frequência, onde cada jogo vale 2^(R-1).

Desta forma, vamos construir um número binário de 12 bits, onde o bit será 1 caso tenha participado de um jogo.

Como a data foi ranqueada em ordem crescente, a data mais recente representa o bit mais significativo. Assim, o que você precisa é ordenar os jogadores pela ordem decrescente deste valor.
GOSTEI 1
Joatan Lima

Joatan Lima

24/04/2023

Para mim, assiduidade e frequência é a mesma coisa.
Alterei a tabela assim
jogador (id,nome) -> (1,"Pelé"),(2,"Rivelino")
frequencia (id,id_jogador,data_jogo) -> (1,1,2023-04-01),(2,1,2023-04-12),(3,1,2023-04-24),(4,2,2023-04-24),(5,1,203-04-30)
Criei uma consulta com o nome de vw_frequencia assim:
CREATE 
VIEW `vw_frequencia` AS
    SELECT 
        `frequencia`.`id_jogador` AS `id_jogador`,
        `jogador`.`nome` AS `nome`,
        COUNT(`frequencia`.`data_jogo`) AS `frequencia`
    FROM
        (`frequencia`
        JOIN `jogador` ON ((`frequencia`.`id_jogador` = `jogador`.`id`)))
    GROUP BY `jogador`.`nome`
    ORDER BY `frequencia` DESC

E o resultado foi esse:
id_jogador nome frequencia
1	Péle	4
2	Rivelino	1




Então essa parte já consegui fazer como informo acima, o que não estou conseguindo é quando vou comparar quem vai jogar já que o controle é: Qtde Freq + assiduidade.

Exemplo, conforme informado antes:
Dois atletas empatam com 8 frequencias (eles são os primeiros da relação) mais só tem vaga pra um entrar, sendo que o primeiro faltou o último jogo 07/04 e o segundo jogador não, então o segundo jogador fica na frente devido ordem de assiduidade, caso continuem empatados, preciso comparar o penultimo jogo que foi jogo 01/04, e assim por diante até chegar em um jogo que um faltou e esse ficará abaixo na ordem da relação:
Total Frequencia + Assiduidade (ultimas presenças).


GOSTEI 0
Joatan Lima

Joatan Lima

24/04/2023

Estamos falando de uma análise de 90 dias (3 meses) em que cada mês tem 4 jogos, dando um total de 12 jogos.

Imagino fazer o seguinte:

1 - Identificamos as datas dos jogos em ordem crescente
2 - Criamos um rank "R" para as datas, de forma que a data mais antiga valha 1 e a mais recente valha 12
3 - Para cada jogador, criamos um número que identifica o peso de sua frequência, onde cada jogo vale 2^(R-1).

Desta forma, vamos construir um número binário de 12 bits, onde o bit será 1 caso tenha participado de um jogo.

Como a data foi ranqueada em ordem crescente, a data mais recente representa o bit mais significativo. Assim, o que você precisa é ordenar os jogadores pela ordem decrescente deste valor.


Opa meu mano,
Por favor conseguiria fazer um exemplo, agraceria bastante se possível....
GOSTEI 0
Arthur Heinrich

Arthur Heinrich

24/04/2023

Estamos falando de uma análise de 90 dias (3 meses) em que cada mês tem 4 jogos, dando um total de 12 jogos.

Imagino fazer o seguinte:

1 - Identificamos as datas dos jogos em ordem crescente
2 - Criamos um rank "R" para as datas, de forma que a data mais antiga valha 1 e a mais recente valha 12
3 - Para cada jogador, criamos um número que identifica o peso de sua frequência, onde cada jogo vale 2^(R-1).

Desta forma, vamos construir um número binário de 12 bits, onde o bit será 1 caso tenha participado de um jogo.

Como a data foi ranqueada em ordem crescente, a data mais recente representa o bit mais significativo. Assim, o que você precisa é ordenar os jogadores pela ordem decrescente deste valor.


Opa meu mano,
Por favor conseguiria fazer um exemplo, agraceria bastante se possível....


Eu não conheço muito bem o firebird. Então, vou escrever uma query utilizando meu conhecimento em Oracle. Mesmo que não te atenda 100%, deve ser fácil adaptar para o firebird e imagino que dê para entender a lógica:

with
  dates as
    ( select distinct cast( dt_frequencia as date ) dt_frequencia
      from frequencias
      where
        cast(dt_frequencia as date) >= cast( dateadd (day, -90, current_date) as date) ),
  date_rank as
    ( select dt_frequencia, row_number() over(partition by 1 order by dt_frequencia ) r
      from dates ),
  frequencia as
    ( select f.ncod_jogador, count(1) jogos, sum( power( 2, dr.r-1 ) ) dr
      from
        date_rank dr,
        frequencias f
      where
        dr.dt_frequencia = cast(f.dt_frequencia as date)
      group by f.ncod_jogador )
select
  j.ncod_jogador,
  j.cnome_jogador,
  f.jogos as Total_Freq
from
  frequencia f,
  jogador j
where
  j.ncod_jogador = f.ncod_jogador
order by f.jogos desc, f.dr desc

GOSTEI 1
Joatan Lima

Joatan Lima

24/04/2023

Estamos falando de uma análise de 90 dias (3 meses) em que cada mês tem 4 jogos, dando um total de 12 jogos.

Imagino fazer o seguinte:

1 - Identificamos as datas dos jogos em ordem crescente
2 - Criamos um rank "R" para as datas, de forma que a data mais antiga valha 1 e a mais recente valha 12
3 - Para cada jogador, criamos um número que identifica o peso de sua frequência, onde cada jogo vale 2^(R-1).

Desta forma, vamos construir um número binário de 12 bits, onde o bit será 1 caso tenha participado de um jogo.

Como a data foi ranqueada em ordem crescente, a data mais recente representa o bit mais significativo. Assim, o que você precisa é ordenar os jogadores pela ordem decrescente deste valor.


Opa meu mano,
Por favor conseguiria fazer um exemplo, agraceria bastante se possível....


Eu não conheço muito bem o firebird. Então, vou escrever uma query utilizando meu conhecimento em Oracle. Mesmo que não te atenda 100%, deve ser fácil adaptar para o firebird e imagino que dê para entender a lógica:

with
  dates as
    ( select distinct cast( dt_frequencia as date ) dt_frequencia
      from frequencias
      where
        cast(dt_frequencia as date) >= cast( dateadd (day, -90, current_date) as date) ),
  date_rank as
    ( select dt_frequencia, row_number() over(partition by 1 order by dt_frequencia ) r
      from dates ),
  frequencia as
    ( select f.ncod_jogador, count(1) jogos, sum( power( 2, dr.r-1 ) ) dr
      from
        date_rank dr,
        frequencias f
      where
        dr.dt_frequencia = cast(f.dt_frequencia as date)
      group by f.ncod_jogador )
select
  j.ncod_jogador,
  j.cnome_jogador,
  f.jogos as Total_Freq
from
  frequencia f,
  jogador j
where
  j.ncod_jogador = f.ncod_jogador
order by f.jogos desc, f.dr desc



Cara vc é um genio, muito obrigado, sem palavras pra agradecer!!!
Os códigos são os mesmos, os que comparei pelo Ibexpert (ferramenta para manutenção do firebird) bateram, vou tentar adaptar ao sistema para os campos que passam parametros como:
Código Jogador
Tipo Jogador (Mensalista / Diarista)
Posição
Data inicial e Final

Existe um segundo que é o Analítico, nesse outro tenho que agrupar essa mesma query mas por posição de jogador (tabela Jogador campo: nCodPosicao), exemplo:
ZAG- Zagueiros
LAT- Laterais
VOL- Volantes
MEI - Meias
ATA - Atacantes
GOSTEI 0
Joatan Lima

Joatan Lima

24/04/2023

Estamos falando de uma análise de 90 dias (3 meses) em que cada mês tem 4 jogos, dando um total de 12 jogos.

Imagino fazer o seguinte:

1 - Identificamos as datas dos jogos em ordem crescente
2 - Criamos um rank "R" para as datas, de forma que a data mais antiga valha 1 e a mais recente valha 12
3 - Para cada jogador, criamos um número que identifica o peso de sua frequência, onde cada jogo vale 2^(R-1).

Desta forma, vamos construir um número binário de 12 bits, onde o bit será 1 caso tenha participado de um jogo.

Como a data foi ranqueada em ordem crescente, a data mais recente representa o bit mais significativo. Assim, o que você precisa é ordenar os jogadores pela ordem decrescente deste valor.


Opa meu mano,
Por favor conseguiria fazer um exemplo, agraceria bastante se possível....


Eu não conheço muito bem o firebird. Então, vou escrever uma query utilizando meu conhecimento em Oracle. Mesmo que não te atenda 100%, deve ser fácil adaptar para o firebird e imagino que dê para entender a lógica:

with
  dates as
    ( select distinct cast( dt_frequencia as date ) dt_frequencia
      from frequencias
      where
        cast(dt_frequencia as date) >= cast( dateadd (day, -90, current_date) as date) ),
  date_rank as
    ( select dt_frequencia, row_number() over(partition by 1 order by dt_frequencia ) r
      from dates ),
  frequencia as
    ( select f.ncod_jogador, count(1) jogos, sum( power( 2, dr.r-1 ) ) dr
      from
        date_rank dr,
        frequencias f
      where
        dr.dt_frequencia = cast(f.dt_frequencia as date)
      group by f.ncod_jogador )
select
  j.ncod_jogador,
  j.cnome_jogador,
  f.jogos as Total_Freq
from
  frequencia f,
  jogador j
where
  j.ncod_jogador = f.ncod_jogador
order by f.jogos desc, f.dr desc



Cara vc é um genio, muito obrigado, sem palavras pra agradecer!!!
Os códigos são os mesmos, os que comparei pelo Ibexpert (ferramenta para manutenção do firebird) bateram, vou tentar adaptar ao sistema para os campos que passam parametros como:
Código Jogador
Tipo Jogador (Mensalista / Diarista)
Posição
Data inicial e Final

Existe um segundo que é o Analítico, nesse outro tenho que agrupar essa mesma query mas por posição de jogador (tabela Jogador campo: nCodPosicao), exemplo:
ZAG- Zagueiros
LAT- Laterais
VOL- Volantes
MEI - Meias
ATA - Atacantes


O segundo que é o Analítico, crie em cima dessa query que você me passou e do mesmo relatorio, só não consegui ainda agrupar por posição, mas a ordem já esta correta pelo campo CSIGLA acrescetado número na frente: 1GL, 2LT, 3ZG, 4VL, 5ME e 6AT.
GOSTEI 0
POSTAR