Partie 6 · Pilotage

Dashboard et critères d'évaluation

Les responsables métier ont besoin d'indicateurs simples et lisibles, pas seulement d'une liste brute. Le dashboard minimal propose :

le nombre total de marchés détectés ;
le nombre de marchés pertinents ;
une répartition par priorité ;
une évolution temporelle des détections ;
une répartition géographique.

Le travail SQL consiste à concevoir les données analytiques qui alimentent ce tableau de bord (requêtes ou vues). C'est ici que reviennent les agrégations GROUP BY et, pour les tendances, les window functions de la première moitié du cours (cumul, moyenne glissante, comparaison à la période précédente avec LAG).

À toi de coder

Les indicateurs du dashboard

Le dashboard minimal demande : le nombre total de marchés détectés, le nombre de marchés pertinents, une répartition par priorité, une évolution temporelle des détections et une répartition géographique. Ici « pertinent » signifie niveau différent de faible (hypothèse ajoutée).

Table marche(id, date_pub, localisation, niveau) déjà remplie (8 annonces). Complète : total, pertinents, par_niveau (dict niveau → nombre), par_jour (liste de (date_pub, nombre) triée par date) et par_region (dict localisation → nombre).

import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE marche(id INTEGER PRIMARY KEY, date_pub TEXT, localisation TEXT, niveau TEXT);
INSERT INTO marche VALUES
 (1,'2024-03-01','IDF','élevé'),(2,'2024-03-01','Bretagne','faible'),
 (3,'2024-03-02','Occitanie','moyen'),(4,'2024-03-02','IDF','moyen'),(5,'2024-03-02','IDF','faible'),
 (6,'2024-03-03','Bretagne','élevé'),(7,'2024-03-03','Occitanie','faible'),(8,'2024-03-04','IDF','élevé');
""")
total = con.execute("SELECT ___ FROM marche").fetchone()[0]
pertinents = con.execute("SELECT COUNT(*) FROM marche WHERE niveau ___ 'faible'").fetchone()[0]
par_niveau = dict(con.execute("SELECT niveau, COUNT(*) FROM marche GROUP BY niveau"))
par_jour = con.execute("SELECT date_pub, COUNT(*) FROM marche GROUP BY ___ ORDER BY date_pub").fetchall()
par_region = dict(con.execute("SELECT localisation, COUNT(*) FROM marche GROUP BY localisation"))

Ctrl/⌘+Entrée

Vérifie que tu as compris

Quel indicateur du dashboard correspond à GROUP BY date_pub ?

À toi de coder

Une vue d'alimentation avec cumul

Livrable attendu : les requêtes ou vues d'alimentation du dashboard. On crée une vue v_kpi_jour (par jour : nombre d'annonces et nombre de pertinentes) puis on lit cette vue en y ajoutant un cumul avec une window function (tendance de l'évolution temporelle).

Complète le regroupement de la vue et l'ordre du cumul ; le résultat rows a les colonnes date_pub, nb, pertinents, cumul.

import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE marche(id INTEGER PRIMARY KEY, date_pub TEXT, localisation TEXT, niveau TEXT);
INSERT INTO marche VALUES
 (1,'2024-03-01','IDF','élevé'),(2,'2024-03-01','Bretagne','faible'),
 (3,'2024-03-02','Occitanie','moyen'),(4,'2024-03-02','IDF','moyen'),(5,'2024-03-02','IDF','faible'),
 (6,'2024-03-03','Bretagne','élevé'),(7,'2024-03-03','Occitanie','faible'),(8,'2024-03-04','IDF','élevé');
""")
con.execute("""
CREATE VIEW v_kpi_jour AS
SELECT date_pub, COUNT(*) AS nb,
       SUM(CASE WHEN niveau <> 'faible' THEN 1 ELSE 0 END) AS pertinents
FROM marche
GROUP BY ___
""")
rows = con.execute("""
SELECT date_pub, nb, pertinents, SUM(nb) OVER (ORDER BY ___) AS cumul
FROM v_kpi_jour
ORDER BY date_pub
""").fetchall()

Ctrl/⌘+Entrée

Productions et critères

Livrables attendus : le schéma de données, les scripts de création des tables, les scripts des fonctions, procédures et triggers, la logique transactionnelle retenue, la stratégie de sauvegarde documentée, les requêtes ou vues d'alimentation du dashboard.

Critère	Indicateurs
Compréhension du besoin	reformulation correcte, entités clés, cohérence besoin/solution
Modélisation	3FN minimum, clés primaires/étrangères, unicité, index justifiés
Fonctions SQL	paramètres cohérents, valeur de retour pertinente, réutilisabilité, performance
Procédures stockées	cas métiers, contrôle des données, gestion des erreurs, lisibilité
Triggers	utilité réelle, absence d'effets de bord, BEFORE/AFTER
Transactions	BEGIN/COMMIT/ROLLBACK, cohérence, gestion des erreurs
Sauvegarde	automatisation, complétude (données + structure), procédure de restauration
Dashboard	KPI pertinents, requêtes optimisées, lisibilité, cohérence métier