GSP461

Descripción general

BigQuery es la base de datos de análisis de Google de bajo costo, no-ops y completamente administrada. Con BigQuery, puedes consultar muchos terabytes de datos sin tener que administrar infraestructura o contar con un administrador de base de datos. Esta base de datos usa SQL y un modelo de pago por uso, y permite enfocarse en el análisis de datos para hallar estadísticas valiosas.

Con BigQuery ML, los analistas de datos pueden aprovechar sus conocimientos sobre SQL para crear modelos de aprendizaje automático rápidamente en el mismo lugar donde se encuentran sus datos en BigQuery.

BigQuery cuenta con un conjunto de datos público con información sobre los partidos, equipos y jugadores de baloncesto de la NCAA. Los datos de los partidos incluyen información detallada de las jugadas y anotaciones desde el 2009, así como resultados finales desde 1996. En el caso de algunos equipos, los datos adicionales sobre las victorias y las derrotas se remontan a la temporada de 1894-1895.

En este lab, usarás BigQuery ML para elaborar prototipos, entrenar modelos, realizar evaluaciones y predecir los “ganadores” y “perdedores” de dos equipos del torneo de baloncesto de la NCAA.

Actividades

En este lab, aprenderás a hacer lo siguiente:

• Usar BigQuery para acceder al conjunto de datos público de la NCAA
• Explorar el conjunto de datos para familiarizarte con el esquema y el alcance de los datos disponibles
• Preparar los datos existentes y transformarlos en atributos y etiquetas
• Dividir el conjunto de datos en subconjuntos de entrenamiento y evaluación
• Usar BigQuery ML para crear un modelo basado en el conjunto de datos de torneos de la NCAA
• Usar el modelo nuevo para predecir los ganadores de los torneos de la NCAA de tu ronda

Requisitos previos

Este lab es de nivel intermedio, por lo que debes tener experiencia en SQL y conocer las palabras clave del lenguaje. También se recomienda tener conocimientos de BigQuery. Si necesitas ponerte al día en estas áreas antes de intentar este lab, debes realizar, como mínimo, uno de los siguientes labs:

• Introducción a SQL para BigQuery y Cloud SQL
• BigQuery: Qwik Start - Consola

Configuración y requisitos

Antes de hacer clic en el botón Comenzar lab

Lee estas instrucciones. Los labs cuentan con un temporizador que no se puede pausar. El temporizador, que comienza a funcionar cuando haces clic en Comenzar lab, indica por cuánto tiempo tendrás a tu disposición los recursos de Google Cloud.

Este lab práctico te permitirá realizar las actividades correspondientes en un entorno de nube real, no en uno de simulación o demostración. Para ello, se te proporcionan credenciales temporales nuevas que utilizarás para acceder a Google Cloud durante todo el lab.

Para completar este lab, necesitarás lo siguiente:

• Acceso a un navegador de Internet estándar. Se recomienda el navegador Chrome.

Nota: Usa una ventana del navegador privada o de incógnito (opción recomendada) para ejecutar el lab. Así evitarás conflictos entre tu cuenta personal y la cuenta de estudiante, lo que podría generar cargos adicionales en tu cuenta personal.

• Tiempo para completar el lab (recuerda que, una vez que comienzas un lab, no puedes pausarlo).

Nota: Usa solo la cuenta de estudiante para este lab. Si usas otra cuenta de Google Cloud, es posible que se apliquen cargos a esa cuenta.

Cómo iniciar tu lab y acceder a la consola de Google Cloud

1. Haz clic en el botón Comenzar lab. Si debes pagar por el lab, se abrirá un diálogo para que selecciones la forma de pago. A la izquierda, se encuentra el panel Detalles del lab, que tiene estos elementos:

   • El botón para abrir la consola de Google Cloud
   • El tiempo restante
   • Las credenciales temporales que debes usar para el lab
   • Otra información para completar el lab, si es necesaria

2. Haz clic en Abrir la consola de Google Cloud (o haz clic con el botón derecho y selecciona Abrir el vínculo en una ventana de incógnito si ejecutas el navegador Chrome).

   El lab inicia recursos y abre otra pestaña en la que se muestra la página de acceso.

   Sugerencia: Ordena las pestañas en ventanas separadas, una junto a la otra.

   Nota: Si ves el diálogo Elegir una cuenta, haz clic en Usar otra cuenta.

3. De ser necesario, copia el nombre de usuario a continuación y pégalo en el diálogo Acceder.

   {{{user_0.username | "Username"}}}

   También puedes encontrar el nombre de usuario en el panel Detalles del lab.

4. Haz clic en Siguiente.

5. Copia la contraseña que aparece a continuación y pégala en el diálogo Te damos la bienvenida.

   {{{user_0.password | "Password"}}}

   También puedes encontrar la contraseña en el panel Detalles del lab.

6. Haz clic en Siguiente.

   Importante: Debes usar las credenciales que te proporciona el lab. No uses las credenciales de tu cuenta de Google Cloud. Nota: Usar tu propia cuenta de Google Cloud para este lab podría generar cargos adicionales.

7. Haz clic para avanzar por las páginas siguientes:

   • Acepta los Términos y Condiciones.
   • No agregues opciones de recuperación o autenticación de dos factores (esta es una cuenta temporal).
   • No te registres para obtener pruebas gratuitas.

Después de un momento, se abrirá la consola de Google Cloud en esta pestaña.

Nota: Para acceder a los productos y servicios de Google Cloud, haz clic en el menú de navegación o escribe el nombre del servicio o producto en el campo Buscar.

Cómo abrir la consola de BigQuery

1. En la consola de Google Cloud, seleccione elmenú de navegación > BigQuery.

Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en la consola de Cloud. Este cuadro de mensaje contiene un vínculo a la guía de inicio rápido y las notas de la versión.

2. Haga clic en Listo.

Se abrirá la consola de BigQuery.

Tarea 1: Abre la consola de BigQuery

1. En la consola de Cloud, abre el menú de navegación y selecciona BigQuery.

2. Haz clic en LISTO para ir a la IU en fase beta. Asegúrate de que el ID de tu proyecto esté configurado en la pestaña Explorador, que debería verse de la siguiente manera:

Si haces clic en la flecha EXPANDIR EL NODO junto a tu proyecto, no verás bases de datos ni tablas, ya que aún no has agregado ninguna al proyecto.

Por suerte, hay muchos conjuntos de datos públicos y abiertos disponibles en BigQuery para que los uses. Ahora, obtendrás más información sobre el conjunto de datos de la NCAA y, luego, descubrirás cómo agregarlo a tu proyecto de BigQuery.

Tarea 2: El March Madness de la NCAA

Cada año, la National Collegiate Athletic Association (NCAA) organiza dos torneos importantes de baloncesto universitario en Estados Unidos: uno para hombres y otro para mujeres. En el torneo masculino de la NCAA que se realiza en marzo, 68 equipos participan en partidos de eliminación directa y uno resulta ganador general del March Madness.

La NCAA ofrece un conjunto de datos público que contiene las estadísticas de los partidos de baloncesto masculinos y femeninos, y de los jugadores de la temporada y los torneos finales. Los datos de los partidos incluyen información detallada de las jugadas y anotaciones desde el 2009, así como resultados finales desde 1996. En el caso de algunos equipos, los datos adicionales sobre las victorias y las derrotas se remontan a la temporada de 1894-1895.

• Asegúrate de consultar esta entrada del blog de Google Cloud sobre la predicción de estadísticas en vivo para obtener más información sobre el conjunto de datos y lo que se hizo con él. También puedes ver un panel de demostración sobre el torneo en Google Cloud March Madness Insights.

Tarea 3: Busca el conjunto de datos públicos de la NCAA en BigQuery

1. Asegúrate de permanecer en la consola de BigQuery para realizar este paso. En la pestaña Explorador, haz clic en el botón + AGREGAR y, luego, selecciona Conjuntos de datos públicos.

2. En la barra de búsqueda, escribe NCAA Basketball y presiona Intro. Aparecerá un resultado. Selecciónalo y, luego, haz clic en VER CONJUNTO DE DATOS:

Se abrirá una nueva pestaña de BigQuery con el conjunto de datos cargado. Puedes seguir trabajando en esa pestaña, o bien cerrarla y actualizar la consola de BigQuery en la otra pestaña para ver el conjunto de datos públicos.

Nota: Si no puedes ver “ncaa_basketball”, haz clic en + AGREGAR > Destaca un proyecto por nombre. Asígnale el nombre bigquery-public-data al proyecto y, luego, haz clic en DESTACAR.

3. Expande el conjunto de datos bigquery-public-data > ncaa_basketball para revelar sus tablas:

Deberías ver 10 tablas en el conjunto de datos.

4. Haz clic en mbb_historical_tournament_games y, luego, en VISTA PREVIA para ver las filas de muestra de datos.

5. Luego, haz clic en DETALLES para obtener los metadatos de la tabla.

La página debería tener el siguiente aspecto:

Tarea 4: Escribe una consulta para determinar las temporadas y los partidos disponibles

Ahora, escribirás una consulta sencilla en SQL para determinar cuántas temporadas y partidos se encuentran disponibles para explorar en nuestra tabla mbb_historical_tournament_games.

1. En el EDITOR de consultas, que se encuentra arriba de la sección de detalles de la tabla, copia y pega lo siguiente:

SELECT season, COUNT(*) as games_per_tournament FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` GROUP BY season ORDER BY season # default is Ascending (low to high)

2. Haz clic en EJECUTAR. Algunos segundos después, deberías obtener un resultado similar al siguiente:

3. Desplázate por el resultado y anota la cantidad de temporadas y de partidos que se jugaron en cada una de ellas. Usarás esa información para responder las siguientes preguntas. Además, en la parte inferior derecha, junto a las flechas de paginación, podrás ver rápidamente cuántas filas se devolvieron.

Haz clic en Revisar mi progreso para verificar el objetivo. Escribir una consulta para determinar las temporadas y los partidos disponibles

Pon a prueba tus conocimientos

Responde las siguientes preguntas de opción múltiple para reforzar tus conocimientos sobre los conceptos que tratamos hasta ahora. Trata de responderlas lo mejor posible.

Tarea 5: Comprende las etiquetas y los atributos del aprendizaje automático

El objetivo final de este lab es predecir el ganador de un determinado partido de baloncesto masculino de la NCAA usando datos históricos. En el aprendizaje automático, cada columna de datos que nos ayuda a determinar un resultado (en este caso, la victoria o derrota en un partido del torneo) se denomina atributo.

La columna de datos que intentas predecir se denomina etiqueta. Los modelos de aprendizaje automático “aprenden” la relación entre los atributos para predecir el resultado de una etiqueta.

Estos son algunos ejemplos de atributos del conjunto de datos histórico:

• Temporada
• Nombre del equipo
• Nombre del equipo rival
• Clasificación del equipo (ranking)
• Clasificación del equipo rival

La etiqueta que intentarás predecir será el resultado de cada partido futuro, es decir, si un equipo gana o pierde.

Pon a prueba tus conocimientos

Responde las siguientes preguntas de opción múltiple para reforzar tus conocimientos sobre los conceptos que tratamos hasta ahora. Trata de responderlas lo mejor posible.

Tarea 6: Crea un conjunto de datos de aprendizaje automático etiquetado

Para compilar un modelo de aprendizaje automático, se requieren muchos datos de entrenamiento de alta calidad. Por suerte, nuestro conjunto de datos de la NCAA es lo suficientemente sólido como para que podamos compilar un modelo eficaz basándonos en él.

1. Regresa a la consola de BigQuery. Deberías haberte detenido en el resultado de la consulta que ejecutó.

2. En el menú de la izquierda, abre la tabla mbb_historical_tournament_games haciendo clic en su nombre. Una vez que se cargue, haz clic en VISTA PREVIA. La página debería tener el siguiente aspecto:

Pon a prueba tus conocimientos

Responde las siguientes preguntas de opción múltiple para reforzar tus conocimientos sobre los conceptos que tratamos hasta ahora. Trata de responderlas lo mejor posible.

3. Después de inspeccionar el conjunto de datos, observarás que una fila del conjunto de datos tiene las columnas win_market y lose_market. Deberás dividir el registro de un partido en un registro para cada equipo, de modo que puedas etiquetar cada fila como “ganador” o “perdedor”.

4. En el EDITOR de consultas, copia y pega la siguiente consulta y haz clic en EJECUTAR:

# create a row for the winning team SELECT # features season, # ex: 2015 season has March 2016 tournament games round, # sweet 16 days_from_epoch, # how old is the game game_date, day, # Friday 'win' AS label, # our label win_seed AS seed, # ranking win_market AS market, win_name AS name, win_alias AS alias, win_school_ncaa AS school_ncaa, # win_pts AS points, lose_seed AS opponent_seed, # ranking lose_market AS opponent_market, lose_name AS opponent_name, lose_alias AS opponent_alias, lose_school_ncaa AS opponent_school_ncaa # lose_pts AS opponent_points FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` UNION ALL # create a separate row for the losing team SELECT # features season, round, days_from_epoch, game_date, day, 'loss' AS label, # our label lose_seed AS seed, # ranking lose_market AS market, lose_name AS name, lose_alias AS alias, lose_school_ncaa AS school_ncaa, # lose_pts AS points, win_seed AS opponent_seed, # ranking win_market AS opponent_market, win_name AS opponent_name, win_alias AS opponent_alias, win_school_ncaa AS opponent_school_ncaa # win_pts AS opponent_points FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`

Deberías recibir el siguiente resultado:

Haz clic en Revisar mi progreso para verificar el objetivo. Crear un conjunto de datos de aprendizaje automático etiquetado

Ahora que conoces qué atributos se encuentran disponibles a partir del resultado, responde la siguiente pregunta para reforzar tus conocimientos sobre el conjunto de datos.

Tarea 7: Crea un modelo de aprendizaje automático para predecir el ganador según la clasificación y el nombre del equipo

Ahora que ya exploramos nuestros datos, es momento de entrenar un modelo de aprendizaje automático.

• Responde la siguiente pregunta según tus conocimientos para orientarte en esta sección.

Cómo elegir un tipo de modelo

Para este problema en particular, compilarás un modelo de clasificación. Como tendremos dos clases, victoria o derrota, también se denomina modelo de clasificación binaria. Un equipo puede ganar o perder un partido.

Si lo deseas, después del lab, puedes predecir la cantidad total de puntos que anotará un equipo con un modelo de predicción, pero ese no es el enfoque de esta tarea.

Una manera sencilla de saber si estás prediciendo o clasificando es mirar el tipo de etiqueta (columna) de los datos que estás intentando predecir:

• Si se trata de una columna numérica (como unidades vendidas o puntos en un partido), estás prediciendo.
• Si se trata de un valor de cadena, estás clasificando (esa fila es de esta clase o de esta otra).
• Si tienes más de dos clases (como victoria, derrota o empate), estás clasificando en clases múltiples.

Nuestro modelo de clasificación llevará a cabo el aprendizaje automático con un modelo estadístico muy popular llamado regresión logística.

Necesitamos un modelo que genere una probabilidad para cada valor posible de etiqueta discreta, el que en nuestro caso es una “victoria” o una “derrota”. La regresión logística es un buen tipo de modelo para comenzar con este objetivo. Lo positivo es que el modelo de AA hará todos los cálculos y la optimización durante el entrenamiento del modelo, una actividad en la que las computadoras llegan a ser infalibles.

Nota: Existen muchos otros modelos de aprendizaje automático que varían en complejidad para realizar tareas de clasificación. Uno que se usa con frecuencia en Google es el aprendizaje profundo con redes neuronales.

Crea un modelo de aprendizaje automático con BigQuery ML

Para crear nuestro modelo de clasificación en BigQuery, solo hay que escribir la instrucción de SQL CREATE MODEL y proporcionar algunas opciones.

Sin embargo, antes de poder crear el modelo, necesitamos un lugar para almacenarlo en nuestro proyecto.

1. En el panel Explorador, haz clic en el ícono Ver acciones que se encuentra junto al ID del proyecto y elige Crear un conjunto de datos.

2. Se abrirá el diálogo Crear un conjunto de datos. Configura el ID de tu conjunto de datos como bracketology y haz clic en CREAR CONJUNTO DE DATOS.

3. Ahora, ejecuta el siguiente comando en el EDITOR de consultas:

CREATE OR REPLACE MODEL `bracketology.ncaa_model` OPTIONS ( model_type='logistic_reg') AS # create a row for the winning team SELECT # features season, 'win' AS label, # our label win_seed AS seed, # ranking win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ranking lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` WHERE season <= 2017 UNION ALL # create a separate row for the losing team SELECT # features season, 'loss' AS label, # our label lose_seed AS seed, # ranking lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ranking win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` # now we split our dataset with a WHERE clause so we can train on a subset of data and then evaluate and test the model's performance against a reserved subset so the model doesn't memorize or overfit to the training data. # tournament season information from 1985 - 2017 # here we'll train on 1985 - 2017 and predict for 2018 WHERE season <= 2017

En nuestro código, observarás que solo se requieren unas pocas líneas de SQL para crear el modelo. Una de las opciones más importantes es elegir el tipo de modelo logistic_reg para nuestra tarea de clasificación.

Nota: Consulta la guía de documentación de BigQuery ML para ver una lista de todas las opciones de modelos disponibles y los parámetros de configuración. En nuestro caso, ya tenemos un campo llamado “label” para que evitemos especificar nuestra columna de etiquetas con la opción del modelo input_label_cols.

Entrenar el modelo tardará entre 3 y 5 minutos. Deberías obtener el siguiente resultado cuando finalice el trabajo:

4. En el lado derecho de la consola, haz clic en el botón Ir al modelo.

Haz clic en Revisar mi progreso para verificar el objetivo. Crear un modelo de aprendizaje automático

Ve los detalles del entrenamiento de modelos

• Ahora que te encuentras en los detalles del modelo, desplázate hasta la sección Opciones de entrenamiento y ve las iteraciones reales que realizó el modelo para el entrenamiento.

Si tienes experiencia en aprendizaje automático, observa que puedes personalizar todos esos hiperparámetros (opciones configuradas antes de ejecutar el modelo) definiendo su valor en la instrucción OPTIONS.

Si no tienes experiencia, BigQuery ML configurará valores inteligentes predeterminados para cualquier opción que no esté configurada.

Si necesitas más información, consulta la lista de opciones de modelos de BigQuery ML.

Ve las estadísticas del entrenamiento de modelos

Los modelos de aprendizaje automático “aprenden” la asociación entre los atributos conocidos y las etiquetas desconocidas. Como puedes suponer, algunos atributos, como la "clasificación" o el "nombre de la universidad", pueden ser de mayor ayuda para determinar una victoria o una derrota que otras columnas de datos (atributos), como el día de la semana en el que se juega el partido.

Los modelos de aprendizaje automático inician el proceso de entrenamiento sin esa intuición y, por lo general, aleatorizarán la ponderación de cada atributo.

Durante el proceso de entrenamiento, el modelo optimizará una ruta para ponderar cada atributo de la mejor manera posible. Con cada ejecución, se intenta minimizar la Pérdida de datos de entrenamiento y la Pérdida de datos de evaluación.

Si alguna vez descubres que la pérdida final de la evaluación es mucho mayor que la de entrenamiento, significa que tu modelo sobreajusta o memoriza tus datos de entrenamiento en vez de aprender relaciones generalizables.

Puedes ver cuántas ejecuciones de entrenamiento realiza el modelo haciendo clic en la pestaña ENTRENAMIENTO y seleccionando Tabla en la opción Ver como.

Durante nuestra ejecución en particular, el modelo completó 3 iteraciones de entrenamiento en aproximadamente 20 segundos. Es posible que tu modelo varíe.

Ve lo que aprendió el modelo sobre nuestros atributos

Después del entrenamiento, puedes inspeccionar los pesos para ver qué atributos le proporcionaron más valor al modelo.

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT category, weight FROM UNNEST(( SELECT category_weights FROM ML.WEIGHTS(MODEL `bracketology.ncaa_model`) WHERE processed_input = 'seed')) # try other features like 'school_ncaa' ORDER BY weight DESC

El resultado debería ser similar al siguiente:

Como puedes ver, si la clasificación de un equipo es muy baja (1, 2 o 3) o muy alta (14, 15 o 16), el modelo le proporciona un peso significativo (lo máximo es 1.0) para determinar el resultado de victorias y derrotas. De forma intuitiva, esto tiene sentido porque esperamos que los equipos con una clasificación muy baja tengan un buen rendimiento en el torneo.

El verdadero atractivo del aprendizaje automático es que no tuvimos que crear muchas instrucciones IF THEN codificadas en SQL que le indicaran al modelo que, si la clasificación es 1, el equipo tiene un 80% más de probabilidades de ganar. El aprendizaje automático elimina las reglas codificadas y la lógica, y aprende esas relaciones por sí mismo. Si necesitas más detalles, consulta la documentación sobre la sintaxis de BQML en relación con los pesos.

Tarea 8: Evalúa el rendimiento del modelo

Para evaluar el rendimiento del modelo, puedes ejecutar el fragmento de código ML.EVALUATE en un modelo entrenado.

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT * FROM ML.EVALUATE(MODEL `bracketology.ncaa_model`)

El resultado debería ser similar al siguiente:

El valor tendrá una exactitud de alrededor del 69%. Aunque es mejor que lanzar una moneda a la suerte, quedan aspectos por mejorar.

Nota: En el caso de los modelos de clasificación, la exactitud del modelo no es la única métrica a la que debes prestarle atención en el resultado.

Debido a que realizaste una regresión logística, puedes evaluar el rendimiento de tu modelo respecto de todas estas métricas (mientras más cerca de 1.0, mejor):

Precisión: Es una métrica de los modelos de clasificación que identifica la frecuencia con la que un modelo predijo de manera correcta la clase positiva.

Recuperación: Es una métrica de los modelos de clasificación que responde la siguiente pregunta: de todas las etiquetas positivas posibles, ¿cuántas identificó de forma correcta el modelo?

Exactitud: Es la fracción de las predicciones que acertó un modelo de clasificación.

Puntuación F1: Es una medida de la exactitud del modelo. Corresponde al promedio armónico de la precisión y la recuperación. El mejor valor de una puntuación F1 es 1. El peor valor es 0.

Pérdida logística: Es la función de pérdida que se usa en una regresión logística. Mide qué tan lejos están las predicciones del modelo de las etiquetas correctas.

AUC de la ROC: Es el área bajo la curva ROC. Es la probabilidad de que un clasificador tenga más certeza de que un ejemplo positivo elegido al azar sea realmente positivo a que un ejemplo negativo elegido al azar sea positivo.

Tarea 9: Realiza predicciones

Ahora que entrenaste un modelo con datos históricos hasta la temporada del 2017 inclusive (todos los datos que tenías), es hora de realizar las predicciones para la temporada del 2018. Tu equipo de científicos de datos te proporcionó los resultados del torneo del 2018 en una tabla aparte que no tienes en tu conjunto de datos original.

Hacer predicciones es tan simple como llamar a ML.PREDICT en un modelo entrenado y pasar el conjunto de datos sobre el que quieres realizar la predicción.

• Ejecuta el siguiente comando en el EDITOR de consultas:

CREATE OR REPLACE TABLE `bracketology.predictions` AS ( SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model`, # predicting for 2018 tournament games (2017 season) (SELECT * FROM `data-to-insights.ncaa.2018_tournament_results`) ) )

Algunos segundos después, deberías obtener el siguiente resultado:

Haz clic en Revisar mi progreso para verificar el objetivo. Evaluar el rendimiento del modelo y crear una tabla

Nota: Dado que estás almacenando tus predicciones en una tabla, podrás consultar las estadísticas posteriormente sin tener que volver a ejecutar la consulta anterior.

Ahora verás el conjunto de datos original, además de tres columnas nuevas:

• Etiqueta de predicción
• Opciones de etiqueta de predicción
• Probabilidad de etiqueta de predicción

Como ya conoces los resultados del March Madness 2018, veamos cómo le fue al modelo con las predicciones. Sugerencia: Si quieres predecir los resultados del March Madness 2019, simplemente pasa un conjunto de datos con las clasificaciones y los nombres de los equipos de ese año. Desde luego, la columna de etiquetas estará vacía, ya que esos partidos aún no se jugaron (eso es lo que va a predecir).

Tarea 10: ¿Cuántos aciertos tuvo nuestro modelo respecto del torneo de la NCAA del 2018?

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT * FROM `bracketology.predictions` WHERE predicted_label <> label

El resultado debería ser similar al siguiente:

De un total de 134 predicciones (67 partidos del torneo), nuestro modelo se equivocó 38 veces. Obtuvo un 70% total de aciertos en los partidos del torneo 2018.

Tarea 11: Los modelos solo tienen un alcance limitado

Hay muchos otros factores y atributos que influyen en las victorias estrechas y las derrotas increíbles de cualquier torneo March Madness que a un modelo le costaría mucho predecir.

Busquemos la mayor derrota del torneo 2017 según el modelo. Veremos dónde el modelo predice con una certeza de más del 80% y ERRA.

1. Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT model.label AS predicted_label, model.prob AS confidence, predictions.label AS correct_label, game_date, round, seed, school_ncaa, points, opponent_seed, opponent_school_ncaa, opponent_points FROM `bracketology.predictions` AS predictions, UNNEST(predicted_label_probs) AS model WHERE model.prob > .8 AND predicted_label <> predictions.label

El resultado debería ser similar al siguiente:

Predicción: El modelo predice que el equipo de University of Virginia (clasificación 1) derrotará al de UMBC (clasificación 16) con una certeza del 87%. Parece razonable, ¿no?

Mira el video “16-seed UMBC pulls off a miracle upset over 1-seed Virginia” para ver qué sucedió en realidad.

Después del partido, el entrenador Odom de la UMBC declaró: “Increíble. No hay nada más que decir”. Obtén más información sobre este tema en el artículo 2018 UMBC vs. Virginia men's basketball game.

Resumen

• Creaste un modelo de aprendizaje automático para predecir el resultado de los partidos.
• Evaluaste el rendimiento y lograste una exactitud del 69% con la clasificación y el nombre del equipo como atributos principales.
• Predijiste los resultados del torneo 2018.
• Analizaste los resultados para obtener estadísticas.

Nuestro próximo desafío será crear un modelo más eficaz SIN usar la clasificación ni el nombre del equipo como atributos.

Tarea 12: Usa atributos útiles de los modelos de AA

En la segunda parte de este lab, crearás un segundo modelo de AA con atributos detallados que se proporcionaron recientemente.

Ahora que ya sabes crear modelos de AA usando BigQuery ML, tu equipo de científicos de datos te proporcionó un nuevo conjunto de datos con información sobre cada jugada, en el que crearon métricas nuevas de equipos para que tu modelo las aprenda. Estos incluyen:

• Eficiencia en el tiempo para anotar según el análisis histórico jugada por jugada
• Posesión del balón en el transcurso del tiempo

Crea un conjunto de datos de AA nuevo con estos atributos útiles

• Ejecuta el siguiente comando en el EDITOR de consultas:

# create training dataset: # create a row for the winning team CREATE OR REPLACE TABLE `bracketology.training_new_features` AS WITH outcomes AS ( SELECT # features season, # 1994 'win' AS label, # our label win_seed AS seed, # ranking # this time without seed even win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ranking lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # create a separate row for the losing team SELECT # features season, # 1994 'loss' AS label, # our label lose_seed AS seed, # ranking lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ranking win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # add in 2018 tournament game results not part of the public dataset: SELECT season, label, seed, school_ncaa, opponent_seed, opponent_school_ncaa FROM `data-to-insights.ncaa.2018_tournament_results` ) SELECT o.season, label, # our team seed, school_ncaa, # new pace metrics (basketball possession) team.pace_rank, team.poss_40min, team.pace_rating, # new efficiency metrics (scoring over time) team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # opposing team opponent_seed, opponent_school_ncaa, # new pace metrics (basketball possession) opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # new efficiency metrics (scoring over time) opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # a little feature engineering (take the difference in stats) # new pace metrics (basketball possession) opp.pace_rank - team.pace_rank AS pace_rank_diff, opp.poss_40min - team.poss_40min AS pace_stat_diff, opp.pace_rating - team.pace_rating AS pace_rating_diff, # new efficiency metrics (scoring over time) opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff, opp.pts_100poss - team.pts_100poss AS eff_stat_diff, opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff FROM outcomes AS o LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS team ON o.school_ncaa = team.team AND o.season = team.season LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS opp ON o.opponent_school_ncaa = opp.team AND o.season = opp.season

Algunos segundos después, deberías obtener el siguiente resultado:

Haz clic en Revisar mi progreso para verificar el objetivo. Usar atributos útiles de los modelos de AA

Tarea 13: Obtén una vista previa de los nuevos atributos

• Haz clic en el botón Ir a la tabla que se encuentra a la derecha de la consola. Luego, haz clic en la pestaña Vista previa.

Tu tabla debería ser similar a la siguiente:

No te preocupes si el resultado no es idéntico al de la captura de pantalla anterior.

Tarea 14: Interpreta las métricas seleccionadas

• Ahora conocerás algunas etiquetas importantes que nos ayudan a hacer predicciones.

opp_efficiency_rank

Clasificación de la eficiencia del rival: De todos los equipos, indica la clasificación que tiene nuestro rival para anotar de manera eficiente en el transcurso del tiempo (puntos por cada 100 posesiones del balón). Cuanto más baja, mejor.

opp_pace_rank

Clasificación de la velocidad del rival: De todos los equipos, indica la clasificación que tiene nuestro rival en cuanto a la posesión del balón (cantidad de posesiones en 40 minutos). Cuanto más baja, mejor.

Ahora que tienes atributos esclarecedores sobre lo bien que puede anotar y manejar el balón un equipo, entrenemos nuestro segundo modelo.

Como medida adicional para evitar que tu modelo “memorice los equipos buenos del pasado”, excluye el nombre del equipo y la clasificación de este próximo modelo y enfócate solo en las métricas.

Tarea 15: Entrena el nuevo modelo

• Ejecuta el siguiente comando en el EDITOR de consultas:

CREATE OR REPLACE MODEL `bracketology.ncaa_model_updated` OPTIONS ( model_type='logistic_reg') AS SELECT # this time, don't train the model on school name or seed season, label, # our pace poss_40min, pace_rank, pace_rating, # opponent pace opp_poss_40min, opp_pace_rank, opp_pace_rating, # difference in pace pace_rank_diff, pace_stat_diff, pace_rating_diff, # our efficiency pts_100poss, efficiency_rank, efficiency_rating, # opponent efficiency opp_pts_100poss, opp_efficiency_rank, opp_efficiency_rating, # difference in efficiency eff_rank_diff, eff_stat_diff, eff_rating_diff FROM `bracketology.training_new_features` # here we'll train on 2014 - 2017 and predict on 2018 WHERE season BETWEEN 2014 AND 2017 # between in SQL is inclusive of end points

Algunos segundos después, deberías obtener un resultado similar al siguiente:

Tarea 16: Evalúa el rendimiento del nuevo modelo

• Para evaluar el rendimiento de tu modelo, ejecuta el siguiente comando en el EDITOR de consultas:

SELECT * FROM ML.EVALUATE(MODEL `bracketology.ncaa_model_updated`)

El resultado debería ser similar al siguiente:

¡Bien! Acabas de entrenar un modelo nuevo con atributos diferentes y de aumentar su exactitud a aproximadamente un 75%, o un aumento del 5% en comparación con el modelo original.

Una de las lecciones más importantes del aprendizaje automático es que la calidad de los atributos de un conjunto de datos puede marcar una gran diferencia en la exactitud del modelo.

Haz clic en Revisar mi progreso para verificar el objetivo. Entrenar el nuevo modelo y realizar una evaluación

Tarea 17: Inspecciona lo que aprendió el modelo

• ¿Qué atributos pondera más el modelo en el resultado de una victoria o derrota? Descúbrelo ejecutando el siguiente comando en el EDITOR de consultas:

SELECT * FROM ML.WEIGHTS(MODEL `bracketology.ncaa_model_updated`) ORDER BY ABS(weight) DESC

El resultado debería ser similar al siguiente:

Ordenamos los pesos según su valor absoluto para que aparezcan en primer lugar los más determinantes (para una victoria o una derrota).

Como puedes ver en los resultados, los 3 principales son pace_stat_diff, eff_stat_diff y eff_rating_diff. Pasemos a explorarlos un poco más.

pace_stat_diff

Indica qué tan diferente fue, entre los equipos, la estadística real de posesiones en 40 minutos. Según el modelo, es el factor más determinante en el resultado del partido.

eff_stat_diff

Indica qué tan diferente fue, entre los equipos, la estadística real de puntos netos por cada 100 posesiones.

eff_rating_diff

Indica qué tan diferente fue, entre los equipos, la calificación normalizada de eficiencia para anotar.

La temporada es el último factor en la lista de pesos ordenados, por lo que tuvo poca influencia en las predicciones del modelo. Lo que indica el modelo es que la temporada (2013, 2014 y 2015) no es tan útil para predecir el resultado de los partidos. El 2014 no tuvo nada especial para ningún equipo.

Una estadística interesante es que el modelo valoró el ritmo del equipo (su capacidad para controlar el balón) por sobre la eficiencia para anotar.

Tarea 18: Momento de la predicción

• Ejecuta el siguiente comando en el EDITOR de consultas:

CREATE OR REPLACE TABLE `bracketology.ncaa_2018_predictions` AS # let's add back our other data columns for context SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( SELECT * # include all columns now (the model has already been trained) FROM `bracketology.training_new_features` WHERE season = 2018 ))

El resultado debería ser similar al siguiente:

Haz clic en Revisar mi progreso para verificar el objetivo. Ejecutar una consulta para crear una tabla de ncaa_2018_predictions

Tarea 19: Analiza las predicciones

Como conoces el resultado real del partido, puedes ver en qué parte el modelo hizo una predicción errónea con el nuevo conjunto de datos de prueba.

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT * FROM `bracketology.ncaa_2018_predictions` WHERE predicted_label <> label

Como puedes ver en la cantidad de registros que devolvió la consulta, el modelo erró en 48 enfrentamientos (24 partidos) de la cantidad total del torneo, con una exactitud del 64% para el 2018. Debió haber sido un año agitado. Veamos qué victorias inesperadas ocurrieron.

Tarea 20: ¿Cuáles fueron las victorias inesperadas que ocurrieron en marzo de 2018?

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT CONCAT(school_ncaa, " was predicted to ",IF(predicted_label="loss","lose","win")," ",CAST(ROUND(p.prob,2)*100 AS STRING), "% but ", IF(n.label="loss","lost","won")) AS narrative, predicted_label, # what the model thought n.label, # what actually happened ROUND(p.prob,2) AS probability, season, # us seed, school_ncaa, pace_rank, efficiency_rank, # them opponent_seed, opponent_school_ncaa, opp_pace_rank, opp_efficiency_rank FROM `bracketology.ncaa_2018_predictions` AS n, UNNEST(predicted_label_probs) AS p WHERE predicted_label <> n.label # model got it wrong AND p.prob > .75 # by more than 75% confidence ORDER BY prob DESC

El resultado debería ser similar al siguiente:

La victoria inesperada más importante fue la misma que descubrió nuestro modelo anterior: la del equipo de UMBC contra el de University of Virginia. Obtén más información sobre cómo, en general, el 2018 fue un año de grandes victorias inesperadas en el artículo Has This Been the “Maddest” March? ¿El 2019 fue igual de agitado?

Tarea 21: Compara el rendimiento de los modelos

¿Qué ocurre con los casos en los que el modelo básico (que compara clasificaciones) erró, pero el modelo avanzado acertó?

• Ejecuta el siguiente comando en el EDITOR de consultas:

SELECT CONCAT(opponent_school_ncaa, " (", opponent_seed, ") was ",CAST(ROUND(ROUND(p.prob,2)*100,2) AS STRING),"% predicted to upset ", school_ncaa, " (", seed, ") and did!") AS narrative, predicted_label, # what the model thought n.label, # what actually happened ROUND(p.prob,2) AS probability, season, # us seed, school_ncaa, pace_rank, efficiency_rank, # them opponent_seed, opponent_school_ncaa, opp_pace_rank, opp_efficiency_rank, (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) AS seed_diff FROM `bracketology.ncaa_2018_predictions` AS n, UNNEST(predicted_label_probs) AS p WHERE predicted_label = 'loss' AND predicted_label = n.label # model got it right AND p.prob >= .55 # by 55%+ confidence AND (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) > 2 # seed difference magnitude ORDER BY (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) DESC

El resultado debería ser similar al siguiente:

El modelo predijo que el equipo de Florida State University (09) vencería inesperadamente al de Xavier University (01), y así fue.

El modelo nuevo predijo correctamente la victoria inesperada (aunque la clasificación indicaba lo contrario) basándose en los nuevos atributos útiles, como el ritmo y la eficiencia para anotar. Mira los momentos destacados del partido en YouTube.

Tarea 22: Predice los resultados del March Madness 2019

Ahora que conocemos los equipos y las clasificaciones de marzo 2019, hagamos predicciones del resultado de los próximos partidos.

Explora los datos del 2019

• Ejecuta la siguiente consulta para ver las clasificaciones principales:

SELECT * FROM `data-to-insights.ncaa.2019_tournament_seeds` WHERE seed = 1

El resultado debería ser similar al siguiente:

Crea una matriz de todos los partidos posibles

No sabemos qué equipos se enfrentarán a medida que avance el torneo, así que haremos que todos compitan entre sí.

En SQL, una manera sencilla de hacer que un solo equipo se enfrente a todos los de la tabla es usar la instrucción CROSS JOIN.

• Ejecuta la siguiente consulta para obtener todos los partidos posibles del torneo:

SELECT NULL AS label, team.school_ncaa AS team_school_ncaa, team.seed AS team_seed, opp.school_ncaa AS opp_school_ncaa, opp.seed AS opp_seed FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp # teams cannot play against themselves :) WHERE team.school_ncaa <> opp.school_ncaa

Agrega las estadísticas de los equipos del 2018 (ritmo y eficiencia)

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament` AS WITH team_seeds_all_possible_games AS ( SELECT NULL AS label, team.school_ncaa AS school_ncaa, team.seed AS seed, opp.school_ncaa AS opponent_school_ncaa, opp.seed AS opponent_seed FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp # teams cannot play against themselves :) WHERE team.school_ncaa <> opp.school_ncaa ) , add_in_2018_season_stats AS ( SELECT team_seeds_all_possible_games.*, # bring in features from the 2018 regular season for each team (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE school_ncaa = team AND season = 2018) AS team, (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE opponent_school_ncaa = team AND season = 2018) AS opp FROM team_seeds_all_possible_games ) # Preparing 2019 data for prediction SELECT label, 2019 AS season, # 2018-2019 tournament season # our team seed, school_ncaa, # new pace metrics (basketball possession) team.pace_rank, team.poss_40min, team.pace_rating, # new efficiency metrics (scoring over time) team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # opposing team opponent_seed, opponent_school_ncaa, # new pace metrics (basketball possession) opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # new efficiency metrics (scoring over time) opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # a little feature engineering (take the difference in stats) # new pace metrics (basketball possession) opp.pace_rank - team.pace_rank AS pace_rank_diff, opp.poss_40min - team.poss_40min AS pace_stat_diff, opp.pace_rating - team.pace_rating AS pace_rating_diff, # new efficiency metrics (scoring over time) opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff, opp.pts_100poss - team.pts_100poss AS eff_stat_diff, opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff FROM add_in_2018_season_stats

Haz predicciones

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament_predictions` AS SELECT * FROM # let's predicted using the newer model ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( # let's predict on March 2019 tournament games: SELECT * FROM `bracketology.ncaa_2019_tournament` ))

Haz clic en Revisar mi progreso para verificar el objetivo. Ejecutar consultas para crear las tablas ncaa_2019_tournament y ncaa_2019_tournament_predictions

Obtén las predicciones

SELECT p.label AS prediction, ROUND(p.prob,3) AS confidence, school_ncaa, seed, opponent_school_ncaa, opponent_seed FROM `bracketology.ncaa_2019_tournament_predictions`, UNNEST(predicted_label_probs) AS p WHERE p.prob >= .5 AND school_ncaa = 'Duke' ORDER BY seed, opponent_seed

• Aquí filtramos los resultados del modelo para ver todos los partidos posibles del equipo de Duke University. Desplázate para ver el partido del equipo de Duke University contra el de North Dakota State University.

Estadística: El equipo de Duke University (1) tiene un 88.5% de probabilidades de derrotar al de North Dakota State University (16) el 22/3/19.

Cambia el filtro school_ncaa anterior para predecir los enfrentamientos en tu ronda. Escribe la puntuación de confianza del modelo y disfruta de los partidos.

¡Felicitaciones!

Usaste BigQuery ML para predecir los equipos ganadores del torneo de baloncesto masculino de la NCAA.

Próximos pasos y más información

• Si buscas más información sobre las métricas del baloncesto y su análisis, consulta los análisis adicionales del equipo de Google Cloud encargado de las predicciones y los anuncios del torneo de la NCAA.
• Consulta este lab:
  • Cómo explorar datos de la NCAA con BigQuery

Capacitación y certificación de Google Cloud

Recibe la formación que necesitas para aprovechar al máximo las tecnologías de Google Cloud. Nuestras clases incluyen habilidades técnicas y recomendaciones para ayudarte a avanzar rápidamente y a seguir aprendiendo. Para que puedas realizar nuestros cursos cuando más te convenga, ofrecemos distintos tipos de capacitación de nivel básico a avanzado: a pedido, presenciales y virtuales. Las certificaciones te ayudan a validar y demostrar tus habilidades y tu conocimiento técnico respecto a las tecnologías de Google Cloud.

Última actualización del manual: 19 de marzo de 2024

Prueba más reciente del lab: 19 de marzo de 2024

Copyright 2025 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.