-- 10. Tabla final por indicador (D12): cada métrica, su umbral y si pasa. Lee 06_auc.csv, 06_precision.csv y las tablas de -- 07 (rob, pers, coh) y 09 (est_l) de la base de trabajo. Las reproducciones (08) no tienen umbral numérico: van en P6.md. CREATE OR REPLACE TABLE auc6 AS SELECT * FROM read_csv('/w/salidas/06_auc.csv', header = true, auto_detect = true); CREATE OR REPLACE TABLE prec6 AS SELECT * FROM read_csv('/w/salidas/06_precision.csv', header = true, auto_detect = true); CREATE OR REPLACE TABLE inds AS SELECT * FROM (VALUES ('m1e', 'principal'), ('m1r', 'principal'), ('m2', 'principal'), ('c1', 'principal'), ('m1a', 'exploratorio'), ('m6', 'exploratorio'), ('m4', 'exploratorio')) t(indicador, rotulo); CREATE OR REPLACE TABLE tf AS -- AUC (D6): las 8 combinaciones, variante 'sin', percentil, elegibilidad >= 1 SELECT i.indicador, i.rotulo, '1 AUC entidad-año (8 combinaciones)' metrica, 'AUC ' || printf('%.3f', min(a.auc)) || '-' || printf('%.3f', max(a.auc)) || '; IC inf mín ' || printf('%.3f', min(a.ic_inf)) || '; n pos ' || min(a.n_pos) || '-' || max(a.n_pos) valor, 'AUC >= 0,65, n pos >= 60, IC inf > 0,5 en las 8' umbral, (count(*) FILTER (WHERE a.pasa) = 8) pasa, count(*) FILTER (WHERE a.pasa) || ' de 8' detalle FROM inds i JOIN auc6 a ON a.indicador = i.indicador AND a.var = 'sin' AND a.puntuacion = 'pct' AND a.elegibilidad = 'eleg1' GROUP BY ALL UNION ALL SELECT i.indicador, i.rotulo, '2 Precisión en el decil superior', 'precisión ' || printf('%.2f', min(p.precision)) || '-' || printf('%.2f', max(p.precision)) || '; razón ' || printf('%.2f', min(p.razon)) || '-' || printf('%.2f', max(p.razon)) || '; n en el decil ' || min(p.n_decil) || '-' || max(p.n_decil), '>= 2 × tasa base en las 8', (count(*) FILTER (WHERE p.pasa) = 8), count(*) FILTER (WHERE p.pasa) || ' de 8; tasa base ' || printf('%.2f', min(p.tasa_base)) || '-' || printf('%.2f', max(p.tasa_base)) FROM inds i JOIN prec6 p ON p.indicador = i.indicador AND p.var = 'sin' GROUP BY ALL UNION ALL SELECT i.indicador, i.rotulo, '4 Persistencia t -> t+1 (2019-2024)', printf('%.1f', 100.0 * count(*) FILTER (WHERE top_t AND top_t1 AND t <= 2023) / count(*) FILTER (WHERE top_t AND t <= 2023)) || ' % (azar ' || printf('%.1f', 100.0 * count(*) FILTER (WHERE top_t1 AND t <= 2023) / count(*) FILTER (WHERE t <= 2023)) || ' %)', 'sin umbral numérico; el flag exige dos ejercicios', NULL, count(DISTINCT organo_clave) FILTER (WHERE top_t AND top_t1 AND t = 2023) || ' órganos en 2023 y 2024' FROM inds i JOIN pers USING (indicador) GROUP BY ALL UNION ALL SELECT i.indicador, i.rotulo, '5 Robustez: ' || r.prueba, printf('%.1f', 100.0 * count(*) FILTER (WHERE top_a AND top_b AND anio <= 2024) / count(*) FILTER (WHERE top_a AND anio <= 2024)) || ' %', 'decil superior coincide >= 75 %', 100.0 * count(*) FILTER (WHERE top_a AND top_b AND anio <= 2024) / count(*) FILTER (WHERE top_a AND anio <= 2024) >= 75, NULL FROM inds i JOIN rob_pares rp ON rp.ind_a = i.indicador JOIN rob r ON r.prueba = rp.prueba GROUP BY ALL UNION ALL SELECT i.indicador, i.rotulo, '6 Estabilidad entre descargas (ene/abr-2026 frente a oct-2026)', printf('%.1f', 100.0 * count(*) FILTER (WHERE o.pct >= 90 AND a.pct >= 90) / count(*) FILTER (WHERE o.pct >= 90)) || ' %', 'decil superior coincide >= 90 %', 100.0 * count(*) FILTER (WHERE o.pct >= 90 AND a.pct >= 90) / count(*) FILTER (WHERE o.pct >= 90) >= 90, NULL FROM inds i JOIN est_l o ON o.ind = i.indicador AND o.d = 'oct' JOIN est_l a ON a.d = 'abr' AND a.ind = o.ind AND a.organo_clave = o.organo_clave AND a.anio = o.anio GROUP BY ALL UNION ALL SELECT i.indicador, i.rotulo, '7 Coherencia interna (Spearman con los otros)', string_agg(CASE WHEN c.ia = i.indicador THEN c.ib ELSE c.ia END || ' ' || printf('%.3f', c.rho), '; ' ORDER BY c.ia, c.ib), 'ninguna negativa significativa (por año ni en conjunto)', bool_and(c.ok), string_agg(CASE WHEN NOT c.ok THEN c.ia || '~' || c.ib || ' ' || c.motivo END, '; ' ORDER BY c.ia, c.ib) FROM inds i JOIN ( SELECT y.ia, y.ib, g.rho, (atanh(g.rho) * sqrt(g.n - 3) >= -1.96 AND count(*) FILTER (WHERE y.anio <= 2024 AND atanh(y.rho) * sqrt(y.n - 3) < -1.96) = 0) ok, string_agg(CASE WHEN y.anio <= 2024 AND atanh(y.rho) * sqrt(y.n - 3) < -1.96 THEN y.anio::VARCHAR || ' rho ' || printf('%.3f', y.rho) END, ', ' ORDER BY y.anio) motivo FROM (SELECT ia, ib, anio, corr(ra, rb) rho, count(*) n FROM coh GROUP BY ALL) y JOIN (SELECT ia, ib, corr(ra / n, rb / n) rho, count(*) n FROM coh WHERE anio <= 2024 GROUP BY ALL) g USING (ia, ib) GROUP BY y.ia, y.ib, g.rho, g.n) c ON i.indicador IN (c.ia, c.ib) GROUP BY ALL; COPY (SELECT * FROM tf ORDER BY CASE rotulo WHEN 'principal' THEN 0 ELSE 1 END, indicador, metrica) TO '/w/salidas/10_tabla_final.csv' (HEADER, DELIMITER ','); -- @max 200 -- @ Tabla final por indicador (variante 'sin') SELECT indicador, rotulo, metrica, valor, umbral, CASE WHEN pasa IS NULL THEN '—' WHEN pasa THEN 'SÍ' ELSE 'no' END pasa, detalle FROM tf ORDER BY CASE rotulo WHEN 'principal' THEN 0 ELSE 1 END, indicador, metrica; -- @ Veredicto por indicador: pasa a «indicador de riesgo» solo si pasa todas las métricas con umbral SELECT indicador, rotulo, count(*) FILTER (WHERE pasa IS NOT NULL) metricas_con_umbral, count(*) FILTER (WHERE pasa) pasan, string_agg(CASE WHEN pasa = FALSE THEN metrica END, ' | ' ORDER BY metrica) fallan, CASE WHEN count(*) FILTER (WHERE pasa = FALSE) = 0 THEN 'riesgo' ELSE 'descriptor' END veredicto FROM tf GROUP BY indicador, rotulo ORDER BY CASE rotulo WHEN 'principal' THEN 0 ELSE 1 END, indicador;