Spaces:
Sleeping
Sleeping
File size: 3,986 Bytes
1d0150f | 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | -- DERNIÈRES PRÉDICTIONS EFFECTUÉES
SELECT
p.id AS prediction_id,
p.created_at AS prediction_date,
p.model_version,
p.predicted_class,
p.predicted_proba,
p.threshold_used,
p.latency_ms,
r.employee_id
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
ORDER BY p.created_at DESC
LIMIT 20;
-- DERNIÈRES PRÉDICTIONS AVEC INFORMATIONS EMPLOYÉ
SELECT
e.employee_external_id,
e.departement,
e.poste,
p.predicted_class,
p.predicted_proba,
p.created_at
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
ORDER BY p.created_at DESC
LIMIT 20;
-- NOMBRE DE PRÉDICTIONS PAR CLASSE
SELECT
predicted_class,
COUNT(*) AS nb_predictions
FROM app.predictions
GROUP BY predicted_class
ORDER BY predicted_class;
-- TAUX DE RISQUE MOYEN PAR DÉPARTEMENT
SELECT
e.departement,
ROUND(AVG(p.predicted_proba)::numeric, 3) AS risque_moyen,
COUNT(*) AS nb_predictions
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
GROUP BY e.departement
ORDER BY risque_moyen DESC;
-- EMPLOYÉS AVEC RISQUE ÉLEVÉ (> 0.8)
SELECT
e.employee_external_id,
e.departement,
e.poste,
p.predicted_proba,
p.created_at
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
WHERE p.predicted_proba >= 0.8
ORDER BY p.predicted_proba DESC;
-- COMPARAISON PRÉDICTION vs RÉALITÉ (DERNIER GROUND TRUTH PAR EMPLOYÉ)
WITH last_truth AS (
SELECT DISTINCT ON (employee_id)
employee_id,
a_quitte_l_entreprise,
date_event
FROM raw.ground_truth
ORDER BY employee_id, date_event DESC
)
SELECT
e.employee_external_id,
p.predicted_class,
gt.a_quitte_l_entreprise,
p.predicted_proba,
p.created_at AS prediction_date,
gt.date_event AS real_event_date
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
JOIN last_truth gt
ON gt.employee_id = e.id
ORDER BY p.created_at DESC
LIMIT 50;
-- MATRICE DE CONFUSION (SIMPLIFIÉE) - DERNIER GROUND TRUTH PAR EMPLOYÉ
WITH last_truth AS (
SELECT DISTINCT ON (employee_id)
employee_id,
a_quitte_l_entreprise
FROM raw.ground_truth
ORDER BY employee_id, date_event DESC
)
SELECT
p.predicted_class AS prediction,
gt.a_quitte_l_entreprise AS real_value,
COUNT(*) AS count
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN last_truth gt
ON gt.employee_id = r.employee_id
GROUP BY p.predicted_class, gt.a_quitte_l_entreprise
ORDER BY p.predicted_class, gt.a_quitte_l_entreprise;
-- LATENCE MOYENNE DES PRÉDICTIONS
SELECT
ROUND(AVG(latency_ms)::numeric, 2) AS avg_latency_ms,
MAX(latency_ms) AS max_latency_ms,
MIN(latency_ms) AS min_latency_ms
FROM app.predictions;
-- HISTORIQUE DES PRÉDICTIONS POUR UN EMPLOYÉ DONNÉ
SELECT
p.created_at,
p.predicted_class,
p.predicted_proba,
p.model_version
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
WHERE e.employee_external_id = 123
ORDER BY p.created_at DESC;
-- EMPLOYÉS À RISQUE MAIS TOUJOURS PRÉSENTS (DERNIER GROUND TRUTH)
WITH last_truth AS (
SELECT DISTINCT ON (employee_id)
employee_id,
a_quitte_l_entreprise
FROM raw.ground_truth
ORDER BY employee_id, date_event DESC
)
SELECT
e.employee_external_id,
e.departement,
e.poste,
p.predicted_proba
FROM app.predictions p
JOIN app.prediction_requests r
ON p.request_id = r.id
JOIN raw.employees e
ON r.employee_id = e.id
JOIN last_truth gt
ON gt.employee_id = e.id
WHERE p.predicted_class = 1
AND gt.a_quitte_l_entreprise = 0
ORDER BY p.predicted_proba DESC;
|