De JSON a Insights: Rápido y fácil

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:

1. Ingerir los datos en Couchbase fácilmente.
2. Comienza a obtener el valor de estos datos JSON inmediatamente.
3. 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.

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.

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.

**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.

Aquí está la consulta completa:

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:  

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. 

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().

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.

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.