Coautor: Sitaram Vemulapalli, Ingeniero Principal, Couchbase R&D.
"La respuesta, amigo mío, se esconde en JSON" - Bob Dylan
Hay muchos público conjuntos de datos JSON y luego se impresionante Conjuntos de datos JSON. Todas las empresas, incluida la suya, han almacenado muchos datos en JSON: el resultado de encuestas, campañas y foros.
Hay muchas maneras de conseguir la piel del JSON. Usted puede escribir el programa Python para cada informe, la visualización que desea hacer. O bien, puede utilizar N1QL (SQL para JSON) a generar el algoritmo adecuado para usted para analizar datos JSON. En este artículo, le mostramos cómo utilizar N1QL para extraer información rápidamente. También utilizamos dos funciones que aparecerán en la próxima versión: Common Table Expression (CTE) y Window Functions.
Objetivo: Utilizar el conjunto de datos JSON público de los resultados del US Open de golf para crear una tabla de clasificación simple, un ranking, etc.
Tres cosas que harás como parte de esto:
- Ingerir los datos en Couchbase fácilmente.
- Comienza a obtener el valor de estos datos JSON inmediatamente.
- Dé forma al JSON para generar rápidamente informes útiles utilizando las nuevas funciones.
Fuente de datos: https://github.com/jackschultz/usopen
Las consultas de este post también están disponibles en: https://github.com/keshavmr/usopen-golf-queries
Estructura del repositorio de datos: Este repositorio de GitHub https://github.com/jackschultz/usopen contiene US Open golf -2018 datos. Para cada hoyo, como un documento separado para cada día.
Cada documento tiene esta estructura. Este es el documento para el hoyo 1 del día 1. La Ps archivada tiene la lista de jugadores, cada uno con un ID único.
A continuación se presentan las estadísticas de juego de cada jugador, golpe a golpe. Los jugadores se emparejan con las puntuaciones utilizando el campo ID único para el jugador.
Empieza a obtener información:
Antes de iniciar la consulta, cree un índice primario en el bucket.
CREAR ÍNDICE PRIMARIO EN usopen;
Tarea 1: Crea un informe con las puntuaciones de los jugadores por rondas y el total final.
Después de jugar con JSON de abajo hacia arriba, llegamos a esta consulta. La explicación está después de la 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 |
WITH d AS ( SELECT pl.hnum AS holedn, pl.ps.Nat AS country, (pl.ps.FN || " " || pl.ps.LN) AS name, pl.ps.ID AS ID, array_length(hps.Sks) AS score, hpl.hole AS `hole`, hpl.day AS `day` FROM ( SELECT meta(usopen).id AS hnum, ps FROM usopen USE keys "holes:1:1" unnest Ps AS ps ) pl INNER JOIN ( SELECT TONUMBER(split(meta(usopen).id, ":") [1]) AS `hole`, TONUMBER(split(meta(usopen).id, ":") [2]) AS `day`, hps FROM usopen unnest Rs AS rs UNNEST rs.Hs AS hs UNNEST hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ) SELECT d.name, SUM( CASE WHEN d.day = 1 THEN d.score ELSE 0 END ) R1, SUM( CASE WHEN d.day = 2 THEN d.score ELSE 0 END ) R2, SUM( CASE WHEN d.day = 3 THEN d.score ELSE 0 END ) R3, SUM( CASE WHEN d.day = 4 THEN d.score ELSE 0 END ) R4, SUM(d.score) T FROM d GROUP BY d.name ORDER BY d.name |
Resultados tabulares (en forma tabular, desde el banco de trabajo de consultas de Couchbase)
Veamos la consulta bloque por bloque.
Mira la cláusula WITH d. La sentencia desenreda el JSON de datos PER-día-PER-agujero-disparo-a-disparo a simples valores escalares.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "d": { "ID": "37189", "country": "USA", "day": 1, "hole": 10, "holedn": "holes:1:1", "name": "Harold Varner", "score": 6 } } |
Holedn es la clave del documento - hole-day-number
País es la nacionalidad del jugador
ID es el identificador único del jugador.
El hoyo y el día son obvios y la puntuación es la puntuación del jugador en ese hoyo.
En la cláusula FROM de la sentencia SELECT, pl es la lista completa de jugadores extraída del documento para el primer día, primer hoyo (hoyos:1:1).
Rs es el resultado de los jugadores, golpe a golpe, hoyo a hoyo. En primer lugar, anulamos ese array un par de veces para proyectar los detalles de cada hoyo y la puntuación de ese hoyo, determinada por array_length(hps.Sks).
Una vez que tenemos la puntuación hoyo por hoyo, es fácil escribir la consulta final para agregar por jugador y por día.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select d.name, sum(case when d.day = 1 then d.score else 0 end) R1, sum(case when d.day = 2 then d.score else 0 end) R2, sum(case when d.day = 3 then d.score else 0 end) R3, sum(case when d.day = 4 then d.score else 0 end) R4, sum(d.score) T from d group by d.name order by d.name |
**La cláusula WITH es la característica de expresión de tabla común (CTE) en la próxima versión de Mad-Hatter. La forma antigua de hacer esto en Couchbase 5.5 o inferior es usando la cláusula LET. Publica la pregunta en el foro de Couchbase si necesitas ayuda aquí).
Tarea 2: Ahora, crea la tabla de clasificación completa y añade el botón CORTE información. Los golfistas eliminados no jugarán la tercera ni la cuarta ronda. Usamos esta información para determinar los jugadores que han sido eliminados.
Consulta 2. Toma la consulta anterior y nómbrala como una tabla común dx y luego añade la siguiente expresión para determinar ese corte.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
( CASE WHEN ( d2.R1 = 0 OR d2.R2 = 0 OR d2.R3 = 0 OR d2.R4 = 0 ) THEN "CUT" ELSE MISSING END ) AS CUT |
Aquí está la 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 |
WITH dy AS ( SELECT pl.hnum AS holedn, pl.ps.Nat AS country,(pl.ps.FN || " " || pl.ps.LN) AS name, pl.ps.ID AS ID, array_length(hps.Sks) AS score, hpl.hole AS `hole`, hpl.day AS `day` FROM ( SELECT meta(usopen).id AS hnum, ps FROM usopen USE keys "holes:1:1" unnest Ps AS ps ) pl INNER JOIN ( SELECT TONUMBER(split(meta(usopen).id, ":") [1]) AS `hole`, TONUMBER(split(meta(usopen).id, ":") [2]) AS `day`, hps FROM usopen unnest Rs AS rs unnest rs.Hs AS hs unnest hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECT d.name, sum( CASE WHEN d.day = 1 THEN d.score ELSE 0 END ) R1, sum( CASE WHEN d.day = 2 THEN d.score ELSE 0 END ) R2, sum( CASE WHEN d.day = 3 THEN d.score ELSE 0 END ) R3, sum( CASE WHEN d.day = 4 THEN d.score ELSE 0 END ) R4, sum(d.score) T FROM dy AS d GROUP BY d.name ORDER BY d.name ) SELECT d2.name, d2.R1, d2.R2, d2.R3, d2.R4, d2.T,( CASE WHEN ( d2.R1 = 0 OR d2.R2 = 0 OR d2.R3 = 0 OR d2.R4 = 0 ) THEN "CUT" ELSE MISSING END ) AS CUT FROM dx AS d2 ORDER BY CUT ASC, d2.T ASC |
Tarea 3: Determinar los ganadores.
Tenemos que clasificar a los jugadores en función de la puntuación total para determinar quién ha ganado el torneo. Las clasificaciones se omiten si hay empates en las puntuaciones. Cómo hacerlo en SQL sin funciones de ventana es caro. Aquí, escribimos la consulta utilizando la función de ventana RANK(). Las funciones de ventana son una característica en N1QL en la próxima versión (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 |
WITH dy AS ( SELECT pl.hnum AS holedn ,pl.ps.Nat AS country ,(pl.ps.FN || " " || pl.ps.LN) AS name ,pl.ps.ID AS ID ,array_length(hps.Sks) AS score ,hpl.hole AS `hole` ,hpl.day AS `day` FROM ( SELECT meta(usopen).id AS hnum ,ps FROM usopen USE keys "holes:1:1" unnest Ps AS ps ) pl INNER JOIN ( SELECT TONUMBER(split(meta(usopen).id, ":") [1]) AS `hole` ,TONUMBER(split(meta(usopen).id, ":") [2]) AS `day` ,hps FROM usopen unnest Rs AS rs unnest rs.Hs AS hs unnest hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ) ,dx AS ( SELECT d.name ,sum(CASE WHEN d.day = 1 THEN d.score ELSE 0 END) R1 ,sum(CASE WHEN d.day = 2 THEN d.score ELSE 0 END) R2 ,sum(CASE WHEN d.day = 3 THEN d.score ELSE 0 END) R3 ,sum(CASE WHEN d.day = 4 THEN d.score ELSE 0 END) R4 ,sum(d.score) T FROM dy AS d GROUP BY d.name ORDER BY d.name ) SELECT d2.name ,d2.R1 ,d2.R2 ,d2.R3 ,d2.R4 ,d2.T ,RANK() OVER (ORDER BY d2.T + CUT) AS Rank FROM dx AS d2 LET CUT = ( CASE WHEN ( d2.R1 = 0 OR d2.R2 = 0 OR d2.R3 = 0 OR d2.R4 = 0 ) THEN 1000 ELSE 0 END ) ORDER BY Rank |
Fíjese en que faltan los puestos 4, 8, 9, 10 y 11 debido a los empates.
Tarea 4: Ahora, vamos a averiguar cómo le fue a cada jugador después de la ronda1, ronda2, ronda3 en comparación con la ronda final. Utilizando las funciones de ventana, resulta tan fácil como hacer desaparecer los malvaviscos cubiertos de chocolate.
Consulta 4: Utilice la misma función RANK(), ordenando por la puntuación de cada día (día1, día1+día2, día1+día2+día3) en lugar de sólo por la puntuación 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 |
WITH dy AS ( SELECT pl.hnum AS holedn, pl.ps.Nat AS country,(pl.ps.FN || " " || pl.ps.LN) AS name, pl.ps.ID AS ID, array_length(hps.Sks) AS score, hpl.hole AS `hole`, hpl.day AS `day` FROM ( SELECT meta(usopen).id AS hnum, ps FROM usopen USE keys "holes:1:1" unnest Ps AS ps ) pl INNER JOIN ( SELECT TONUMBER(split(meta(usopen).id, ":") [1]) AS `hole`, TONUMBER(split(meta(usopen).id, ":") [2]) AS `day`, hps FROM usopen unnest Rs AS rs unnest rs.Hs AS hs unnest hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECT d.name, sum( CASE WHEN d.day = 1 THEN d.score ELSE 0 END ) R1, sum( CASE WHEN d.day = 2 THEN d.score ELSE 0 END ) R2, sum( CASE WHEN d.day = 3 THEN d.score ELSE 0 END ) R3, sum( CASE WHEN d.day = 4 THEN d.score ELSE 0 END ) R4, sum(d.score) T FROM dy AS d GROUP BY d.name ORDER BY d.name ) SELECT d2.name, d2.R1, d2.R2, d2.R3, d2.R4, d2.T, DENSE_RANK() OVER ( ORDER BY d2.T + CUT ) AS rankMoney, RANK() OVER ( ORDER BY d2.T + CUT ) AS rankFinal, RANK() OVER ( ORDER BY d2.R1 ) AS round1rank, RANK() OVER ( ORDER BY d2.R1 + d2.R2 ) AS round2rank, RANK() OVER ( ORDER BY d2.R1 + d2.R2 + d2.R3 + CUT ) AS round3rank FROM dx AS d2 LET CUT = ( CASE WHEN ( d2.R1 = 0 OR d2.R2 = 0 OR d2.R3 = 0 OR d2.R4 = 0 ) THEN 1000 ELSE 0 END ) ORDER BY rankFinal, round1rank, round2rank, round3rank |
Ahora puedes ver cómo subieron o bajaron los jugadores cada día.
Tarea 5: Crear el cuadro de mando completo para el líder utilizando las estadísticas básicas tiro a tiro.
Consulta 5: Brooks Koepka es el ganador final del US open. Obtengamos sus puntuaciones, hoyo por hoyo y obtengamos las puntuaciones acumuladas para él por ronda. Observa cómo la simple SUM() y el agregado COUNT() funcionan como una función ventana con la cláusula OVER().
|
1 |
SUM(d2.score) OVER (PARTITION BY d2.day ORDER BY d2.hole) hst |
Primero se divide la puntuación por día y luego por hoyo - especificado por la cláusula PARTITION BY, en el orden de los hoyos 1-18. A continuación, la SUMA suma las puntuaciones hasta el momento.
|
1 |
SUM(d3.score) OVER (ORDER BY d3.day,d3.hole) ToTScore |
Esta función SUM() simplemente suma la puntuación desde el día 1, hoyo 1 hasta el día 4, hoyo 18 - esto es especificado por el ORDER BY d3.day, d3.hole dentro de la cláusula OVER().. El campo ToTScore muestra los cortos totales del torneo por Koepka en cada hoyo.
|
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 |
WITH dy AS ( SELECT pl.hnum AS holedn, pl.ps.Nat AS country,(pl.ps.FN || " " || pl.ps.LN) AS name, pl.ps.ID AS ID, array_length(hps.Sks) AS score, hpl.hole AS `hole`, hpl.day AS `day`, hpl.Par AS Par FROM ( SELECT meta(usopen).id AS hnum, ps FROM usopen USE keys "holes:1:1" unnest Ps AS ps WHERE ps.LN = "Koepka" ) pl INNER JOIN ( SELECT TONUMBER(split(meta(usopen).id, ":") [1]) AS `hole`, TONUMBER(split(meta(usopen).id, ":") [2]) AS `day`, hs.Par, hps FROM usopen unnest Rs AS rs unnest rs.Hs AS hs unnest hs.HPs AS hps ) hpl ON (pl.ps.ID = hps.ID) ), dx AS ( SELECT d.name, d.day, d.score, d.hole, d.Par FROM dy AS d ORDER BY d.name ), dz AS ( SELECT d2.day, d2.hole, d2.score, SUM(d2.score) OVER ( PARTITION BY d2.day ORDER BY d2.hole ) hst, d2.Par, SUM(d2.Par) OVER ( PARTITION BY d2.day ORDER BY d2.hole ) hpr FROM dx AS d2 LET CUT = ( CASE WHEN ( d2.R1 = 0 OR d2.R2 = 0 OR d2.R3 = 0 OR d2.R4 = 0 ) THEN 1000 ELSE 0 END ) ORDER BY d2.day, d2.hole ) SELECT d3.Par, d3.day, d3.hole, d3.hst, d3.score,(d3.hst - d3.hpr) ToPar, sum(d3.score) OVER ( ORDER BY d3.day, d3.hole ) ToTScore, count(1) OVER ( ORDER BY d3.day, d3.hole ) HoleNum FROM dz AS d3 |
