Coautor: Sitaram Vemulapalli, Engenheiro Principal, Couchbase R&D.

"A resposta, meu amigo, está escondida em JSON" - Bob Dylan

Há muitos público conjuntos de dados JSON e, em seguida, é incrível Conjuntos de dados JSON. Todas as empresas, inclusive a sua, armazenam muitos dados em JSON, resultado de pesquisas, campanhas e fóruns.

Há muitas maneiras de obter o JSON. Você pode escrever um programa Python para cada relatório, visualizando o que deseja fazer. Ou, você pode usar N1QL (SQL para JSON) para gerar o algoritmo certo para você para analisar dados JSON. Neste artigo, mostramos como usar o N1QL para extrair insights rapidamente. Também usamos dois recursos que serão lançados na próxima versão: Common Table Expression (CTE) e Window Functions.

Objetivo: usar o conjunto de dados JSON público para as pontuações de golfe do US Open para criar uma tabela de classificação simples, ranking, etc.

Três coisas que você fará como parte disso:

  1. Ingerir os dados no Couchbase facilmente.
  2. Comece a obter o valor desses dados JSON imediatamente.
  3. Molde o JSON para gerar relatórios úteis usando novos recursos rapidamente.

Dados de origem: https://github.com/jackschultz/usopen

As consultas desta postagem também estão disponíveis em: https://github.com/keshavmr/usopen-golf-queries

Estrutura de repositório de dados: Este repositório do GitHub https://github.com/jackschultz/usopen contém dados do US Open de golfe de 2018. Para cada buraco, é um documento separado para cada dia.

Cada documento tem essa estrutura. Este é o documento do buraco 1 no dia 1. O Ps arquivado tem a lista de jogadores, cada um com um ID exclusivo.  

As estatísticas de jogo de cada jogador são apresentadas a seguir, tacada por tacada. Os jogadores são combinados com as pontuações usando o ID exclusivo do campo para o jogador.  

Comece a obter insights:

Antes de começar a consultar, crie um índice primário no bucket.

CREATE PRIMARY INDEX ON usopen;

Tarefa 1:   Crie um relatório das pontuações dos jogadores por rodada e o total final.

Depois de brincar com o JSON de baixo para cima, chegamos a esta consulta. A explicação está após a consulta.

Resultados tabulares (em formato tabular, do workbench de consulta do Couchbase)

Vamos examinar a consulta bloco por bloco.

Observe a cláusula WITH d. A instrução desvincula o JSON dos dados PER-day-PER-hole-shot-by-shot para valores escalares simples.

Holedn é a chave do documento - número do dia do furo

País é a nacionalidade do jogador

ID é o ID exclusivo do jogador.

O buraco e o dia são óbvios e a pontuação é a pontuação do jogador para aquele buraco.

Na cláusula FROM da instrução SELECT, pl é a lista completa de jogadores retirada do documento para o primeiro dia, primeiro buraco (buracos:1:1).

Rs é o resultado dos jogadores, tacada por tacada, buraco por buraco. Primeiro, aninhamos essa matriz algumas vezes para projetar os detalhes de cada buraco e a pontuação desse buraco, determinada por array_length(hps.Sks).

Quando tivermos a pontuação buraco a buraco, é fácil escrever a consulta final para agregar por jogador e por dia.

 

**A cláusula WITH é o recurso de expressão de tabela comum (CTE) na próxima versão do Mad-Hatter. A maneira antiga de fazer isso no Couchbase 5.5 ou inferior é usar a cláusula LET. Publique a pergunta no fórum do Couchbase se precisar de ajuda aqui).

Tarefa 2: Agora, crie a tabela de classificação completa e adicione a tag CORTE informações. Os golfistas que foram cortados não jogarão a terceira ou a quarta rodada. Usamos essas informações para determinar os jogadores que foram cortados.

Consulta 2.  Pegue a consulta anterior e nomeie-a como uma tabela comum dx e, em seguida, adicione a seguinte expressão para determinar esse corte.

Aqui está a consulta completa:

Tarefa 3: Determinar os vencedores.

Precisamos classificar os jogadores com base na pontuação total para determinar quem venceu o torneio. As classificações serão ignoradas se houver empates nas pontuações. Fazendo isso em SQL sem funções de janela é caro. Aqui, escrevemos a consulta usando a função de janela RANK(). As funções de janela são um recurso do N1QL na próxima versão (Mad-Hatter)

Consulta 3:  

Observe que as classificações 4, 8, 9, 10 e 11 estão faltando por causa do empate nas pontuações!

Tarefa 4: Agora, vamos descobrir como cada jogador se saiu após a rodada 1, a rodada 2 e a rodada 3 em comparação com a rodada final. Usando as funções de janela, é muito fácil fazer desaparecer os marshmallows cobertos com chocolate.

Consulta 4: Use a mesma função RANK(), ordenando pela pontuação de cada dia (dia1, dia1+dia2, dia1+dia2+dia3) em vez de apenas a pontuação final. 

Agora você pode ver como os jogadores subiram ou desceram a cada dia.

Tarefa 5: Crie o scorecard completo para o líder usando as estatísticas básicas de tacada por tacada.

Consulta 5:  Brooks Koepka é o vencedor final do US Open. Vamos obter suas pontuações, buraco a buraco, e obter as pontuações cumulativas para ele por rodada. Observe como o agregado simples SUM() e COUNT() funciona como uma função de janela com a cláusula OVER().

Isso primeiro divide a pontuação por dia e depois por buraco - especificado pela cláusula PARTITION BY, na ordem dos buracos 1-18. Em seguida, o SUM soma as pontuações até o momento.

Essa função SUM() simplesmente soma a pontuação do dia 1, buraco 1, ao dia 4, buraco 18 - isso é especificado pelo ORDER BY d3.day, d3.hole dentro da cláusula OVER(). O campo ToTScore mostra o total de shorts do torneio de Koepka em cada buraco.

Autor

Postado por Keshav Murthy

Keshav Murthy é vice-presidente de P&D da Couchbase. Anteriormente, ele trabalhou na MapR, IBM, Informix e Sybase, com mais de 20 anos de experiência em design e desenvolvimento de bancos de dados. Ele liderou a equipe de P&D de SQL e NoSQL na IBM Informix. Recebeu dois prêmios President's Club na Couchbase e dois Outstanding Technical Achievement Awards na IBM. Keshav é bacharel em Ciência da Computação e Engenharia pela Universidade de Mysore, Índia, detém dez patentes nos EUA e tem três patentes pendentes nos EUA.

Deixar uma resposta