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:
- Ingerir os dados no Couchbase facilmente.
- Comece a obter o valor desses dados JSON imediatamente.
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
COM d AS ( SELECIONAR pl.hnum AS holedn, pl.ps.Nat AS país, (pl.ps.FN || " " || pl.ps.LN) AS nome, pl.ps.ID AS ID, comprimento da matriz(hps.Sks) AS pontuação, hpl.buraco AS `buraco`, hpl.dia AS `dia` DE ( SELECIONAR meta(usoaberto).id AS hnum, ps DE usoaberto USO chaves "holes:1:1" não registrado Ps AS ps ) pl INNER JUNTAR ( SELECIONAR TONUMBER(dividir(meta(usoaberto).id, ":") [1]) AS `buraco`, TONUMBER(dividir(meta(usoaberto).id, ":") [2]) AS `dia`, hps DE usoaberto não registrado Rs AS rs INÚTIL rs.Hs AS hs INÚTIL hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ) SELECIONAR d.nome, SUM( CASO QUANDO d.dia = 1 ENTÃO d.pontuação ELSE 0 FIM ) R1, SUM( CASO QUANDO d.dia = 2 ENTÃO d.pontuação ELSE 0 FIM ) R2, SUM( CASO QUANDO d.dia = 3 ENTÃO d.pontuação ELSE 0 FIM ) R3, SUM( CASO QUANDO d.dia = 4 ENTÃO d.pontuação ELSE 0 FIM ) R4, SUM(d.pontuação) T DE d GRUPO BY d.nome ORDEM BY d.nome |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "d": { "ID": "37189", "país": "EUA", "dia": 1, "buraco": 10, "holedn": "holes:1:1", "name" (nome): "Harold Varner", "pontuação": 6 } } |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
selecionar d.nome, soma(caso quando d.dia = 1 então d.pontuação mais 0 final) R1, soma(caso quando d.dia = 2 então d.pontuação mais 0 final) R2, soma(caso quando d.dia = 3 então d.pontuação mais 0 final) R3, soma(caso quando d.dia = 4 então d.pontuação mais 0 final) R4, soma(d.pontuação) T de d grupo por d.nome ordem por d.nome |
**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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
( CASO QUANDO ( d2.R1 = 0 OU d2.R2 = 0 OU d2.R3 = 0 OU d2.R4 = 0 ) ENTÃO "CORTE" ELSE FALTANDO FIM ) AS CORTE |
Aqui está a consulta completa:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
COM dy AS ( SELECIONAR pl.hnum AS holedn, pl.ps.Nat AS país,(pl.ps.FN || " " || pl.ps.LN) AS nome, pl.ps.ID AS ID, comprimento da matriz(hps.Sks) AS pontuação, hpl.buraco AS `buraco`, hpl.dia AS `dia` DE ( SELECIONAR meta(usoaberto).id AS hnum, ps DE usoaberto USO chaves "holes:1:1" não registrado Ps AS ps ) pl INNER JUNTAR ( SELECIONAR TONUMBER(dividir(meta(usoaberto).id, ":") [1]) AS `buraco`, TONUMBER(dividir(meta(usoaberto).id, ":") [2]) AS `dia`, hps DE usoaberto não registrado Rs AS rs não registrado rs.Hs AS hs não registrado hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECIONAR d.nome, soma( CASO QUANDO d.dia = 1 ENTÃO d.pontuação ELSE 0 FIM ) R1, soma( CASO QUANDO d.dia = 2 ENTÃO d.pontuação ELSE 0 FIM ) R2, soma( CASO QUANDO d.dia = 3 ENTÃO d.pontuação ELSE 0 FIM ) R3, soma( CASO QUANDO d.dia = 4 ENTÃO d.pontuação ELSE 0 FIM ) R4, soma(d.pontuação) T DE dy AS d GRUPO BY d.nome ORDEM BY d.nome ) SELECIONAR d2.nome, d2.R1, d2.R2, d2.R3, d2.R4, d2.T,( CASO QUANDO ( d2.R1 = 0 OU d2.R2 = 0 OU d2.R3 = 0 OU d2.R4 = 0 ) ENTÃO "CUT" (CORTE) ELSE FALTANDO FIM ) AS CORTE DE dx AS d2 ORDEM BY CORTE ASC, d2.T ASC |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
COM dy AS ( SELECIONAR pl.hnum AS holedn ,pl.ps.Nat AS país ,(pl.ps.FN || " " || pl.ps.LN) AS nome ,pl.ps.ID AS ID ,comprimento da matriz(hps.Sks) AS pontuação ,hpl.buraco AS `buraco` ,hpl.dia AS `dia` DE ( SELECIONAR meta(usoaberto).id AS hnum ,ps DE usoaberto USO chaves "holes:1:1" não registrado Ps AS ps ) pl INNER JUNTAR ( SELECIONAR TONUMBER(dividir(meta(usoaberto).id, ":") [1]) AS `buraco` ,TONUMBER(dividir(meta(usoaberto).id, ":") [2]) AS `dia` ,hps DE usoaberto não registrado Rs AS rs não registrado rs.Hs AS hs não registrado hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ) ,dx AS ( SELECIONAR d.nome ,soma(CASO QUANDO d.dia = 1 ENTÃO d.pontuação ELSE 0 FIM) R1 ,soma(CASO QUANDO d.dia = 2 ENTÃO d.pontuação ELSE 0 FIM) R2 ,soma(CASO QUANDO d.dia = 3 ENTÃO d.pontuação ELSE 0 FIM) R3 ,soma(CASO QUANDO d.dia = 4 ENTÃO d.pontuação ELSE 0 FIM) R4 ,soma(d.pontuação) T DE dy AS d GRUPO BY d.nome ORDEM BY d.nome ) SELECIONAR d2.nome ,d2.R1 ,d2.R2 ,d2.R3 ,d2.R4 ,d2.T ,RANK() SOBRE (ORDEM BY d2.T + CORTE) AS Classificação DE dx AS d2 LET CORTE = ( CASO QUANDO ( d2.R1 = 0 OU d2.R2 = 0 OU d2.R3 = 0 OU d2.R4 = 0 ) ENTÃO 1000 ELSE 0 FIM ) ORDEM BY Classificação |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
COM dy AS ( SELECIONAR pl.hnum AS holedn, pl.ps.Nat AS país,(pl.ps.FN || " " || pl.ps.LN) AS nome, pl.ps.ID AS ID, comprimento da matriz(hps.Sks) AS pontuação, hpl.buraco AS `buraco`, hpl.dia AS `dia` DE ( SELECIONAR meta(usoaberto).id AS hnum, ps DE usoaberto USO chaves "holes:1:1" não registrado Ps AS ps ) pl INNER JUNTAR ( SELECIONAR TONUMBER(dividir(meta(usoaberto).id, ":") [1]) AS `buraco`, TONUMBER(dividir(meta(usoaberto).id, ":") [2]) AS `dia`, hps DE usoaberto não registrado Rs AS rs não registrado rs.Hs AS hs não registrado hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECIONAR d.nome, soma( CASO QUANDO d.dia = 1 ENTÃO d.pontuação ELSE 0 FIM ) R1, soma( CASO QUANDO d.dia = 2 ENTÃO d.pontuação ELSE 0 FIM ) R2, soma( CASO QUANDO d.dia = 3 ENTÃO d.pontuação ELSE 0 FIM ) R3, soma( CASO QUANDO d.dia = 4 ENTÃO d.pontuação ELSE 0 FIM ) R4, soma(d.pontuação) T DE dy AS d GRUPO BY d.nome ORDEM BY d.nome ) SELECIONAR d2.nome, d2.R1, d2.R2, d2.R3, d2.R4, d2.T, DENSE_RANK() SOBRE ( ORDEM BY d2.T + CORTE ) AS rankMoney, RANK() SOBRE ( ORDEM BY d2.T + CORTE ) AS classificaçãoFinal, RANK() SOBRE ( ORDEM BY d2.R1 ) AS round1rank, RANK() SOBRE ( ORDEM BY d2.R1 + d2.R2 ) AS round2rank, RANK() SOBRE ( ORDEM BY d2.R1 + d2.R2 + d2.R3 + CORTE ) AS round3rank DE dx AS d2 LET CORTE = ( CASO QUANDO ( d2.R1 = 0 OU d2.R2 = 0 OU d2.R3 = 0 OU d2.R4 = 0 ) ENTÃO 1000 ELSE 0 FIM ) ORDEM BY classificaçãoFinal, round1rank, round2rank, round3rank |
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().
1 |
SUM(d2.pontuação) SOBRE (PARTIÇÃO BY d2.dia ORDEM BY d2.buraco) hst |
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.
1 |
SUM(d3.pontuação) SOBRE (ORDEM BY d3.dia,d3.buraco) ToTScore |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
COM dy AS ( SELECIONAR pl.hnum AS holedn, pl.ps.Nat AS país,(pl.ps.FN || " " || pl.ps.LN) AS nome, pl.ps.ID AS ID, comprimento da matriz(hps.Sks) AS pontuação, hpl.buraco AS `buraco`, hpl.dia AS `dia`, hpl.Par AS Par DE ( SELECIONAR meta(usoaberto).id AS hnum, ps DE usoaberto USO chaves "holes:1:1" não registrado Ps AS ps ONDE ps.LN = "Koepka" ) pl INNER JUNTAR ( SELECIONAR TONUMBER(dividir(meta(usoaberto).id, ":") [1]) AS `buraco`, TONUMBER(dividir(meta(usoaberto).id, ":") [2]) AS `dia`, hs.Par, hps DE usoaberto não registrado Rs AS rs não registrado rs.Hs AS hs não registrado hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECIONAR d.nome, d.dia, d.pontuação, d.buraco, d.Par DE dy AS d ORDEM BY d.nome ), dz AS ( SELECIONAR d2.dia, d2.buraco, d2.pontuação, SUM(d2.pontuação) SOBRE ( PARTIÇÃO BY d2.dia ORDEM BY d2.buraco ) hst, d2.Par, SUM(d2.Par) SOBRE ( PARTIÇÃO BY d2.dia ORDEM BY d2.buraco ) hpr DE dx AS d2 LET CORTE = ( CASO QUANDO ( d2.R1 = 0 OU d2.R2 = 0 OU d2.R3 = 0 OU d2.R4 = 0 ) ENTÃO 1000 ELSE 0 FIM ) ORDEM BY d2.dia, d2.buraco ) SELECIONAR d3.Par, d3.dia, d3.buraco, d3.hst, d3.pontuação,(d3.hst - d3.hpr) ToPar, soma(d3.pontuação) SOBRE ( ORDEM BY d3.dia, d3.buraco ) ToTScore, contagem(1) SOBRE ( ORDEM BY d3.dia, d3.buraco ) HoleNum DE dz AS d3 |