Catando Dados da Copa do Mundo Catar 2022

Salve, salve galera!!! Tudo beleza??

2022 foi um ano bastante intenso com pouco tempo para estar aqui escrevendo novos artigos. Para não passar em branco, dediquei um tempinho para falar sobre a Copa do Mundo que ocorreu, ou melhor até o inicio desse post está ocorrendo porém quando publicar esse post, já será de conhecimento de todos o mais novo Campeão do Mundo.

Pois bem, o que pretendo trazer para você é um desafio que consistem em montar um projeto de banco de dados para armazenar os dados da Copa, isso mesmo, vamos modelar um banco de dados, gerar os scripts, criar mecanismos de atualização dos dados, fazer análises nesses dados e muito mais, prometo que vai ser bem bacana!

Montei um PPT onde elaborei um cenário como se fosse uma especificação do que o sistema pretende armazenar. Clique aqui e faça o download!

Antes de iniciar gostaria de destacar os pontos que vamos trabalhar:

• Modelagem de Dados usando o Toad SQL;

• Criação dos scripts usando SQL Server Management Studio (SSMS);

• Criação de um projeto de banco de dados com Visual Studio;

• Análise dos dados usando Python (Irei usar a IDE Anaconda).

Tudo pronto? Mãos a obra então!

Primeiro passo é abrir o PPT com as regras definidas pelo nosso cliente!

Após uma análise preliminar na documentação, destaquei que nosso modelo de dados a principio deverá ter 11 tabelas.

• Seleções

• Estádios

• Arbitragem

• Grupos

• Jogos/Partidas

• Jogadores

• Gols

• Cartões (Amarelos e Vermelhos)

• Substituições

• Escalações

• Estatísticas dos Jogos/Partidas

Como falei, a principio...Concorda comigo? Vamos seguindo analisando a documentação.

Após identificar as entidades, precisamos analisar criteriosamente os detalhes que estão relacionados a cada uma delas. Para realizar isso vamos voltar no PPT.

Essa análise irá ajudar a identificar o que considero de "três principais características" quando estamos modelando nosso banco de dados, ou seja, é base para construção das nossas tabelas. Vamos aos pontos:

• Identificação de Tabelas e as Coluna (Atributo de cada tabela);

• Tipo de Dado (Fica claro quando analisamos a volumetria esperada além de influenciar muito na hora de provisionar um servidor de BD);

• Regras de Negócio (Outro ponto muito importante).

Pode ser e até considero normal que você nunca acerta no começo, ou seja, seu modelo pode sofrer alterações por um bom tempo até se tornar um modelo maduro. Isso ocorreu quando estava montando do meu lado!

Além do que comentei até aqui, destaco alguns pontos que são fundamentais e que estarei dividindo em dois blocos:

Bloco 1:

• Antes de iniciar qualquer atividade, dedique um bom tempo para analisar bastante os requisitos pois eles possuem informações vitais;

• Precisa pensar fora da caixinha, ou seja, após ler e reler os requisitos, busque identificar algo que não foi levantado e faça questionamentos;

• Esse requisito é para montar um sistema, ou seja, pensem como usuários, esteja na cadeira de usuário, isso irá ajudar e muito;

• Um ponto muito importante, pense e aplique conhecimentos de banco de dados.

Bloco 2:

• Cada tabela e coluna do banco de dados deverá possuir uma breve descrição. Pode parecer irrelevante, porém ajuda bastante a identificar o que está armazenado em uma tabela ou coluna;

• Sempre que for construir um modelo de dados, é de extrema importância conhecer a volumetria de dados (dados iniciais, crescimento mensal, anual) isso auxilia na definição do datatype ideal para as colunas, além de ser um fator de alto impacto quando estamos falando de provisionamento de servidor. Essa análise deverá ser apresenta junto com o modelo de dados;

• É um erro muito primário criar uma tabela sem PK, ou seja, todas as tabelas deverão possuir uma PK, reforçando e sendo redundante, criar tabela sem PK é erro primário, não cometa!;

• Caso alguma coluna possua relacionamento com outra, um índice deverá ser criado, isso garante performance;

• Procure identificar sempre seus relacionamentos dando a eles nomes que deixam de certa forma clara o que estão fazendo. Isso auxilia muito na hora de diagnosticar/debugar um erro;

• Não saiam criando índice para tudo, pois ao mesmo tempo que ele pode ser seu amigo, ele também pode assumir o papel de vilão da história;

Após essa sessão de blá-blá-blá...bora meter a mão e começar a criar alguma coisa!

Bora lá então...

Após análise dos requisitos eis o resultado do mapeamento realizado!

O passo seguinte após a visão macro é realizar a análise de volumetria sobre os dados iniciais e incrementais.

Com o mapeamento de tabelas e volumetria realizados, vamos dedicar um tempo para entender o requisito mais a fundo de cada tabela para construção do modelo.

Concluída a etapa anterior, eis que surge o modelo de dados!

Para construir o modelo, usei a versão gratuita do Toad que você encontra clicando aqui no link.

O próximo passo é gerar o script e criar nosso banco de dados com nossas tabelinhas!

Olha que lindo...o projeto está começando a ganhar forma...

Tudo pronto!

Banco e tabelas criadas!

No final desse post estarei disponibilizando o caminho do git para que você possa ter acesso a todos os scripts usados.

Pois bem, banco e tabelas criadas. Avançamos quase 40% do nosso projeto.

Vou criar um outro ambiente, ou seja, um novo banco de dados para estar trabalhando com dois ambientes, um de desenvolvimento e um de produção.

Para ter um controle melhor sobre o que está sendo desenvolvido e o que está sendo aplicado em produção, estarei usando o Visual Studio para poder gerar um projeto de banco de dados, realizar merge de objetos e dados, gerar scripts de modificações e aplicar de forma sincronizada.

Criando um projeto no Visual Studio

Criando um novo banco de dados que será o nosso banco de produção.

Obs: Como é um ambiente de teste/estudos, não estarei dedicando tempo para configurar um banco de dados usando as melhores prática. Apenas selecionei criar um banco de dados e informei o seu nome. A ideia é apresentar como podemos sincronizar os dois bancos de dados.

Pronto! Todos os bancos estão disponíveis, bora começar!

Para realizar nossa primeira sincronização basta seguir os passos conforme imagem abaixo.

Feito isso, vamos criar as conexões com os bancos de dados. Segue o fluxo....

Conexões criadas com sucesso!

1 - Banco de Desenvolvimento

2 - Banco de Produção

Observe na imagem abaixo que nenhum dos objetos existe no nosso banco COPA_PRD que estão assinalados para criação.

Após execução do Update Target, as tabelas foram criadas no banco de produção.

Após sincronizar realizei nova comparação e nenhuma alteração foi detectada.

Finalizada a criação da estrutura do nosso banco de dados de desenvolvimento e produção chegou o momento de inserir os dados.

E da mesma forma como realizamos com a estrutura das tabelas, os dados serão transportados para o ambiente de produção via Visual Studio porém agora usando a opção Data Compare.

Antes de iniciar o processo de carga, preciso explicar alguns gatilhos (trigger) que foram desenvolvidos para atualizar algumas informações de forma automática.

- Percentual do público no estádio.

- Identificar o tempo de jogo que ocorreu a substituição.

- Contabilizar o número de vitórias, derrotas e empates, o total de gols marcados pela seleção mandante e visitante além de identificar o vencedor da partida.

- Contabilizar o número de gols pró, contra, gols ocorridos na prorrogação e na disputa de pênalti e identificar em que tempo de jogo o gol ocorreu.

- Contabilizar os cartões amarelos e vermelhos além de identificar em que tempo de jogo eles ocorreram.

Pronto, agora podemos partir para inserir os registros em nossa base.

Pensando nesse momento bacana, durante o andamento do mundial do Catar, fui coletando as informações e armazenando para poder trabalhar com você.

Analisando os dados após o encerramento da fase de grupos, percebi que ainda existia a necessidade de automatizar outras 5 fases do mundial.

• Oitavas de Final

• Quartar de Final

• Semi Final

• Disputa de Terceiro Lugar

• Final

Com as regras em mãos (veja abaixo) bastava persisti-las em blocos de programação.

Analisando com detalhes a tabela acima, montei 4 procedures para serem executadas conforme sequência:

1 - Procedure copa.SP_MONTA_OITAVAS_FINAL

Deverá ser executada apenas após a conclusão dos jogos da terceira rodada da fase de grupos.

Onde após os jogos que ocorreram entre os dias 20/11/2022 e 03/12/2022 (Fase de Grupo) a tabela de jogos das Oitavas de Final ficou dessa forma:

2 - Procedure copa.SP_MONTA_QUARTAS_FINAL

Essa procedure pode ser executada durante a fase de Oitavas de Final ou após o seu término.

Inicialmente, sem a conclusão de nenhuma partida das Oitavas de Final, ela apresenta o resultado a seguir:

Ou podemos executar conforme andamento dos jogos das Oitavas de final. Inclui os dado do jogo entre Holanda e EUA.

Observe que os demais que ainda não foram concluídos permanecem do mesmo jeito.

3 - Procedure copa.SP_MONTA_SEMI_FINAL

Essa procedure tem o mesmo comportamento da anterior. Pode ser ser executada durante ou após a conclusão da fase de Quartas de Final.

Seguindo o exemplo acima onde incluí os dados do jogo da Holanda, observe que como não lancei os dados do seu jogo nas Quartas de Final, ela aparece como uma possível integrante na fase Semi Final.

4 - Procedure copa.SP_MONTA_FINAL

Também com o mesmo comportamento das demais.

Essa procedure é responsável por definir as seleções que irão disputar o Terceiro e Quarto lugar e as que irão para a Finalíssima do dia 18/12/2022, coroando a seleção campeão da Copa do Mundo do Catar.

Nesse momento que escrevo esse post está a ocorrer a partida que irá definir o Terceiro e Quarto lugar. Como a Croácia eliminou o Brasil, estarei torcendo pelo Marrocos, e você, torceu pra qual seleção?

Agora meu amigo, já avançamos para 75% do nosso objetivo.

No próximo passo apresento algumas views que desenvolvi para facilitar a visualização dos dados.

VW_SELECAOGRUPO

Nessa view você consegue avaliar como foi o desempenho das 32 seleções divididas em 8 grupo na fase inicial do Mundial.

VW_ESCALACAOJOGO

Nessa view você consegue visualizar como cada equipe iniciou cada partida do Mundial.

VW_JOGO

Nessa view você consegue analisar todos os jogos que ocorreram, identificar os vencedores, gols pró, contra, saldo de gol, cartões recebidos, em qual estádio ocorreu, público presente e quem foi o trio de arbitragem.

VW_GOL

Essa view permite analisar os gols ocorridos no Mundial, analisar a seleção mais letal, a mais vazada, o artilheiro em que tempo das partidas os gols ocorreram, pênaltis concluídos e perdidos.

VW_CARTAO

Essa view permite analisar a equipe com maior incidência de penalizações, a média de cartões por partida e fase.

VW_SUBSTITUICAO

Essa view permite analisar todas as mexidas realizadas durante as partidas. Analisar as substituições que foram realizadas por motivo de lesão.

VW_ESTATISTICAJOGO

Essa visão permite analisar todas as estatísticas das partidas. Finalizações, faltas, impedimentos, pênaltis, cartões, desarmes e outros.

Para você representar os dados da Copa do Catar 2022 basta executar os scripts conforme sequência abaixo:

00_CREATE_DATABASE.sql

01_CREATE_SCHEMA.sql

02_CREATE_TABELAS.sql

03_CREATE_TRIGGER.sql

04_CREATE_PROCEDURE.sql

05_SCRIPT_INSERT_SELECAO.sql

06_SCRIPT_INSERT_ESTADIO.sql

07_SCRIPT_INSERT_GRUPO.sql

08_SCRIPT_INSERT_GRUPO_VS_SELECAO.sql

09_SCRIPT_INSERT_JOGO.sql

10_SCRIPT_INSERT_JOGADOR.sql

11_SCRIPT_INSERT_ARBITRAGEM.sql

12_SCRIPT_INSERT_CARTAO_FASE_GRUPO.sql

13_SCRIPT_INSERT_GOL_FASE_GRUPO.sql

14_SCRIPT_INSERT_SUBSTITUICAO_FASE_GRUPO.sql

15_SCRIPT_INSERT_ESTATISTICAJOGO_FASE_GRUPO.sql

16_SCRIPT_INSERT_ENCERRAMENTOJOGO_FASE_GRUPO.sql

17_SP_MONTA_OITAVAS_FINAL.sql

18_SCRIPT_INSERT_CARTAO_OITAVA_FINAL.sql

19_SCRIPT_INSERT_GOL_OITAVA_FINAL.sql

20_SCRIPT_INSERT_SUBSTITUICAO_OITAVA_FINAL.sql

21_SCRIPT_INSERT_ESTATISTICAJOGO_OITAVA_FINAL.sql

22_SCRIPT_INSERT_ENCERRAMENTOJOGO_OITAVA_FINAL.sql

23_SP_MONTA_QUARTAS_FINAL.sql

24_SCRIPT_INSERT_CARTAO_QUARTA_FINAL.sql

25_SCRIPT_INSERT_GOL_QUARTA_FINAL.sql

26_SCRIPT_INSERT_SUBSTITUICAO_QUARTA_FINAL.sql

27_SCRIPT_INSERT_ESTATISTICAJOGO_QUARTA_FINAL.sql

28_SCRIPT_INSERT_ENCERRAMENTOJOGO_QUARTA_FINAL.sql

29_SP_MONTA_SEMI_FINAL.sql

30_SCRIPT_INSERT_CARTAO_SEMI_FINAL.sql

31_SCRIPT_INSERT_GOL_SEMI_FINAL.sql

32_SCRIPT_INSERT_SUBSTITUICAO_SEMI_FINAL.sql

33_SCRIPT_INSERT_ESTATISTICAJOGO_SEMI_FINAL.sql

34_SCRIPT_INSERT_ENCERRAMENTOJOGO_SEMI_FINAL.sql

35_SP_MONTA_FINAL.sql

36_SCRIPT_INSERT_CARTAO_TERCEIRO_LUGAR.sql

37_SCRIPT_INSERT_GOL_TERCEIRO_LUGAR.sql

38_SCRIPT_INSERT_SUBSTITUICAO_TERCEIRO_LUGAR.sql

39_SCRIPT_INSERT_ESTATISTICAJOGO_TERCEIRO_LUGAR.sql

40_SCRIPT_INSERT_ENCERRAMENTOJOGO_TERCEIRO_LUGAR.sql

41_SCRIPT_INSERT_CARTAO_FINAL.sql

42_SCRIPT_INSERT_GOL_FINAL.sql

43_SCRIPT_INSERT_SUBSTITUICAO_FINAL.sql

44_SCRIPT_INSERT_ESTATISTICAJOGO_FINAL.sql

45_SCRIPT_INSERT_ENCERRAMENTOJOGO_FINAL.sql

46_SCRIPT_INSERT_ESCALACAO.sql

47_SCRIPT_CREATE_VIEW.sql

Scripts estão disponíveis no nosso github, clique aqui.

Algumas informações sobre a copa do Mundo do Catar 2022.

O Mundial do Catar bateu o recorde de gols. Será um recorde que não será batido, uma vez que no Mundial de 2026 iremos contar com 48 seleções. Ao todo foram 518 finalizações ao gol, onde 172 viraram gols dando uma média de 1 gol a cada 3 finalizações o que gerou no final uma média de 2,69 gols por partida!

E como que eles foram marcados?

...e como ocorre essa distribuição por Seleções...

...os 10 Maiores Artilheiros do mundial foram...

...você sabia que no mundial do Catar ocorreram 1584 faltas o que gerou uma média de 24,7 faltas por partida! E qual foi seleção que cometeu e recebeu o maior número de faltas?

...você sabia que no mundial do Catar ocorreram 225 (221 amarelos e 4 vermelhos) cartões o que gerou uma média de 3,5 cartões por partida?

Falando agora de faltas, você sabe qual foi seleção que cometeu e recebeu o maior número de faltas?

Então meu amigo, esse é o desafio que queria trazer pra você.

Topa fazer do seu lado para que possamos trocar uma ideia?

Deixo os arquivos com os dados gerados pelas views para que você possa montar o seu processo, sem que seja necessário seguir exatamente o que montei.

Basta clicar no link e consumir os dados.

Isso brother! Um Feliz Natal, um próspero ano novo e um 2023 com muitas conquistas e realizações.

Se puder ajudar com alguma coisa, contém comigo!

Abraços, Quintellão!