NBA stats

TP5

Author

Ludovic Deneuville

Introduction

Vous allez travailler sur des données de la nba depuis les années 2000.

Les données sont brutes i.e. elles ont été récupérée via l’api stats nba et elles n’ont quasiment pas été retouchées.

Le modèle de données n’est pas fourni ! Ce sera à vous d’explorer vous même la base de données.

Tip

Dans votre vie professionnelle, dans 95 % des cas vous allez hériter de missions où la documentation est obsolète voire inexistante.

Il faut donc apprendre à vous débrouiller dès maintenant. Et au passage, vous convaincre que faire et maintenir la doc ça aide et c’est bien !

1 Importer la base de données

Connectez-vous à un Datalab
Lancez un service PostgreSQL
Lancez un service cloudBeaver

🚧

import os
import subprocess

import psycopg2
import s3fs
from dotenv import load_dotenv

load_dotenv()

AWS_ENDPOINT = os.environ["AWS_ENDPOINT_URL"]
DUMP_FILE = "nba.dump"
FILE_PATH = f"s3://ludo2ne/diffusion/ENSAI/SQL-TP/{DUMP_FILE}"

fs = s3fs.S3FileSystem(
    client_kwargs={"endpoint_url": AWS_ENDPOINT},
    key=os.environ["AWS_ACCESS_KEY_ID"],
    secret=os.environ["AWS_SECRET_ACCESS_KEY"],
    token=os.environ["AWS_SESSION_TOKEN"],
)

print("📥 Téléchargement du dump depuis S3...")
with fs.open(FILE_PATH, "rb") as f_in:
    with open(DUMP_FILE, "wb") as f_out:
        f_out.write(f_in.read())
print("✅ Dump téléchargé :")


PG_HOST = os.getenv("POSTGRESQL_HOST")
PG_PORT = os.getenv("POSTGRESQL_PORT", "5432")
PG_DB = os.getenv("POSTGRESQL_DATABASE")
PG_USER = os.getenv("POSTGRESQL_USERNAME")
PG_PASSWORD = os.getenv("POSTGRESQL_PASSWORD")
PG_SCHEMA = os.getenv("POSTGRESQL_SCHEMA")

os.environ["PGPASSWORD"] = PG_PASSWORD

conn = psycopg2.connect(
    dbname=PG_DB, user=PG_USER, password=PG_PASSWORD, host=PG_HOST, port=PG_PORT
)
conn.autocommit = True
cur = conn.cursor()

sql_create_schema = f"CREATE SCHEMA IF NOT EXISTS {PG_SCHEMA};"
cur.execute(sql_create_schema)

cmd = [
    "pg_restore",
    "-d",
    PG_DB,
    "-h",
    PG_HOST,
    "-p",
    PG_PORT,
    "-U",
    PG_USER,
    "-n",
    PG_SCHEMA,
    DUMP_FILE,
]


print("⏳ Restauration PostgreSQL...")
subprocess.run(cmd, check=True)
print("🎉 Base NBA restaurée dans votre PostgreSQL")

2 Rendez-vous en base inconnue

Je n’ai pas réussi à inviter Laury T pour vous accompagner dans cette aventure mais voici quelques requêtes utiles pour explorer une base de données PostgreSQL.

SELECT table_name
  FROM information_schema.tables
 WHERE table_schema = '<schema_name>'
   AND table_type = 'BASE TABLE'
 ORDER BY table_name;

SELECT column_name,
       data_type,
       is_nullable,
       column_default
  FROM information_schema.columns
 WHERE table_schema = '<...>'
   AND table_name   = '<...>'
 ORDER BY ordinal_position;

Glossaire

Colonne	Signification
matchup	Description du match : ex. `LAL @ BOS` (à l’extérieur) ou `LAL vs BOS` (à domicile).
wl	Résultat pour l’équipe : `W` (win), `L` (loss).
min	Minutes totales jouées par l’équipe (toujours `240` sauf prolongation).
pts	Total des points de l’équipe.
fgm	Field goals made — tirs réussis (2 pts + 3 pts).
fga	Field goals attempted — tirs tentés.
fg_pct	Taux de réussite : `fgm / fga`.
fg3m	3-points réussis.
fg3a	3-points tentés.
fg3_pct	Pourcentage à 3 points.
ftm	Free throws made — lancers francs réussis.
fta	Free throws attempted — lancers francs tentés.
ft_pct	Pourcentage aux lancers.
oreb	Rebonds offensifs.
dreb	Rebonds défensifs.
reb	Rebonds totaux (oreb + dreb).
ast	Passes décisives.
stl	Interceptions.
blk	Contres.
tov	Ballons perdus (turnovers).
pf	Fautes personnelles.

4 Matchs et classements

Dans toute cette partie nous nous interesserons à la saison 2020-2021.

Listez les matchs de la saison
Combien y-a-t-il eu de match cette saison
Distinguez saison régulière et Playoffs

Interessons-nous la saison régulière.

Listez les matchs de Utah Jazz
Comptez leur nombre de victoires et de défaites
Utilisez COUNT et FILTER pour avoir le résultat sur une seule ligne
- ajoutez le nombre de matchs joués
Affichez les mêmes colones pour chaque équipe de la conférence Ouest
- classez par nombre de victoires décroissant

Passons maintenant aux Playoffs.

Listez les matchs de Utah Jazz
Pour chacun de leurs matchs, affichez le score, le nom de leurs adversaires et le résultat
Affichez les scores de matchs

Une dernière question pour la route :

Si vous deviez afficher toutes les finales et leur score, comment feriez-vous ?

Aide

Quelles étapes sont réalisées ci-dessous ?

Tout simplement :

WITH last_playoff_games AS (
SELECT season_id, MAX(game_id) AS last_game_id
  FROM nba.game
 WHERE season_type = 'Playoffs'
 GROUP BY season_id
),
final_teams AS (
SELECT g.season_id, 
       g.team_id AS team_id_win, 
       g2.team_id AS team_id_los
  FROM nba.game g
  JOIN last_playoff_games lpg ON g.season_id = lpg.season_id AND g.game_id = lpg.last_game_id
  JOIN nba.game g2 ON g2.game_id = g.game_id AND g2.team_id <> g.team_id
 WHERE g.wl = 'W'
)
SELECT gwin.season_id,
       twin.full_name AS equipe,
       COUNT(*) FILTER (WHERE gwin.wl = 'W') || ' - ' || COUNT(*) FILTER (WHERE gwin.wl = 'L') AS score,
       tlos.full_name AS adversaire
  FROM nba.game gwin
  JOIN final_teams ft ON ft.team_id_win = gwin.team_id AND ft.season_id = gwin.season_id
  JOIN nba.team twin ON twin.id = gwin.team_id
  JOIN nba.game glos ON glos.game_id = gwin.game_id AND glos.team_id = ft.team_id_los
  JOIN nba.team tlos ON tlos.id = glos.team_id
 WHERE gwin.season_type = 'Playoffs'
 GROUP BY gwin.season_id, twin.full_name, tlos.full_name
 ORDER BY gwin.season_id DESC;

Arrêtez vos services

C’est la fin du TP, vous pouvez maintenant sauvegarder votre travail et libérer les ressources réservées :

Copiez-collez vos scripts SQL sur votre machine
Arrêter les services du Datalab

NBA stats

Introduction

1 Importer la base de données

2 Rendez-vous en base inconnue

3 Explorons avec les Spurs

4 Matchs et classements

5 Triple zéro

Arrêtez vos services