# AUC del descriptor como predictor (pre-registro §4, aclaración B.8). DuckDB puro; escribe salidas/auc_descriptor_ind.json import duckdb, json, time con = duckdb.connect() con.execute("SET memory_limit='7GB'; SET threads=6; SET temp_directory='/w/tmp/duck';") con.execute("CREATE TEMP TABLE L AS SELECT * FROM read_parquet('/w/tmp/lotes_ind.parquet')") con.execute("CREATE TEMP TABLE P AS SELECT * FROM read_parquet('/w/salidas/parecidas_ind.parquet')") con.execute("CREATE TEMP TABLE M AS SELECT * FROM read_parquet('/w/salidas/mercado_ind.parquet')") # tasa global del feed propio en T-1 (lotes con anio_ref = T-1 y num_ofertas >= 1) con.execute("""CREATE TEMP TABLE G AS SELECT anio_ref+1 anio, count(*) n, sum((num_ofertas=1)::int)::DOUBLE/count(*) tasa FROM L WHERE feed='propio' AND num_ofertas>=1 GROUP BY 1""") con.execute(""" CREATE TEMP TABLE S AS SELECT l.licitacion_id, l.lote_u, l.anio_anuncio anio, (l.num_ofertas=1)::INTEGER y, l.fpub_ok, CASE WHEN p.con_ofertas >= 10 THEN 'celda' WHEN m.con_ofertas >= 10 THEN 'mercado' ELSE 'global' END fuente, CASE WHEN p.con_ofertas >= 10 THEN p.tasa_oferta_unica WHEN m.con_ofertas >= 10 THEN m.tasa_oferta_unica ELSE g.tasa END s FROM L l LEFT JOIN P p ON p.organo_clave = l.organo_clave AND p.cpv_div = coalesce(l.cpv_division,'??') AND p.anio = l.anio_anuncio LEFT JOIN M m ON m.cpv_div = coalesce(l.cpv_division,'??') AND m.proc_nat = l.proc_nat AND m.anio = l.anio_anuncio LEFT JOIN G g ON g.anio = l.anio_anuncio WHERE l.feed='propio' AND l.num_ofertas >= 1 AND l.anio_anuncio BETWEEN 2016 AND 2025 """) # AUC de Mann-Whitney con empates a 0,5: por valor de puntuación, positivos × (negativos por debajo + 0,5 negativos empatados) AUC_SQL = """ WITH v AS (SELECT anio, s, sum(y) pos, sum(1-y) neg FROM S WHERE {f} GROUP BY 1,2), c AS (SELECT anio, s, pos, neg, coalesce(sum(neg) OVER (PARTITION BY anio ORDER BY s ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) neg_below FROM v) SELECT anio, sum(pos)::BIGINT n1, sum(neg)::BIGINT n0, sum(pos*(neg_below+0.5*neg))/(sum(pos)*sum(neg)) auc FROM c GROUP BY 1 ORDER BY 1 """ res = {} for nombre, f in (('todos', 'TRUE'), ('fpub_ok', 'fpub_ok')): for anio, n1, n0, auc in con.execute(AUC_SQL.format(f=f)).fetchall(): res.setdefault(int(anio), {})[nombre] = {'n': int(n1 + n0), 'base': round(n1 / (n1 + n0), 6), 'auc': round(float(auc), 6)} nulos = con.execute("SELECT count(*) FROM S WHERE s IS NULL").fetchone()[0] assert nulos == 0, nulos for anio, fuente, n in con.execute("SELECT anio, fuente, count(*) FROM S GROUP BY 1,2").fetchall(): res[int(anio)].setdefault('lotes_por_fuente', {})[fuente] = int(n) for anio, n, t in con.execute("SELECT anio, n, tasa FROM G WHERE anio BETWEEN 2016 AND 2025").fetchall(): res[int(anio)]['global_T_menos_1'] = {'n': int(n), 'tasa': round(t, 6)} out = { 'definicion': 'Pre-registro §4 y aclaración B.8: lotes del feed propio con num_ofertas>=1 y anio_anuncio=T; puntuación = tasa de la celda ' '(organo_clave, cpv_div con ?? si nula, T) si con_ofertas>=10; si no, la del mercado (cpv_div x proc_nat, feed propio, ' 'ventana T-3..T-1) si con_ofertas>=10; si no, la global del feed propio con anio_ref=T-1. AUC de Mann-Whitney, empates 0,5.', 'aceptacion_criterio_5': {str(a): res[a]['todos']['auc'] for a in (2024, 2025)}, 'por_anio': {str(a): res[a] for a in sorted(res)}, } json.dump(out, open('/w/salidas/auc_descriptor_ind.json', 'w'), ensure_ascii=False, indent=1) con.execute("COPY (SELECT * FROM S ORDER BY anio, licitacion_id, lote_u) TO '/w/tmp/scores_descriptor_ind.csv' (HEADER, DELIMITER ',')") print(json.dumps({a: out['por_anio'][a] for a in ('2023', '2024', '2025')}, ensure_ascii=False, indent=1)) print('AUC por año:', {a: v['todos']['auc'] for a, v in out['por_anio'].items()})