"""CO1 · 30 · segunda implementación: aristas, W_in, W_out, C, E, S en SQL (DuckDB, autounión) desde `u` y la partición de impl1; AUC por búsqueda binaria sobre controles ordenados (no pares). Compara con interno/10_grupos.parquet.""" import json, bisect, duckdb con = duckdb.connect('/w/tmp/co1.duckdb', read_only=True); x = con.execute x("SET threads=4; SET memory_limit='6GB'; SET temp_directory='/w/tmp/duck'") x("CREATE TEMP TABLE mem AS SELECT * FROM read_parquet('/w/interno/10_miembros.parquet')") x("CREATE TEMP TABLE g1 AS SELECT * FROM read_parquet('/w/interno/10_grupos.parquet') WHERE metodo = 'louvain' AND semilla = 20261022") x("CREATE TEMP TABLE redes AS SELECT DISTINCT red, var, split_part(red, '|', 2) cpv2, split_part(split_part(red, '|', 3), '-', 1)::INT a0, split_part(split_part(red, '|', 3), '-', 2)::INT a1 FROM mem") # grupos de co-adjudicación (licitación o celda) por red x("""CREATE TEMP TABLE t AS SELECT r.red, r.var, CASE WHEN r.var = 'A' THEN u.lic ELSE u.organo_clave || '|' || u.anio END g, u.emp FROM redes r JOIN u ON u.cpv2 = r.cpv2 AND u.anio BETWEEN r.a0 AND r.a1 AND (r.var = 'A' OR u.organo_clave IS NOT NULL) GROUP BY ALL""") x("""CREATE TEMP TABLE tv AS SELECT t.* FROM t JOIN (SELECT red, var, g FROM t GROUP BY ALL HAVING count(*) >= 2 AND count(*) <= CASE WHEN var = 'A' THEN 30 ELSE 50 END) USING (red, var, g)""") x("""CREATE TEMP TABLE ed AS SELECT a.red, a.var, a.emp e1, b.emp e2, count(*) w FROM tv a JOIN tv b ON a.red = b.red AND a.var = b.var AND a.g = b.g AND a.emp < b.emp GROUP BY ALL""") print('aristas por red', x("SELECT red, var, count(*), sum(w) FROM ed GROUP BY ALL ORDER BY 1, 2").fetchall()[:6]) x("""CREATE TEMP TABLE gm AS WITH e AS (SELECT ed.*, m1.grupo g1, m2.grupo g2 FROM ed JOIN mem m1 ON m1.red = ed.red AND m1.var = ed.var AND m1.emp = ed.e1 JOIN mem m2 ON m2.red = ed.red AND m2.var = ed.var AND m2.emp = ed.e2), win AS (SELECT red, var, g1 grupo, sum(w) w_in FROM e WHERE g1 = g2 GROUP BY ALL), wout AS (SELECT red, var, grupo, sum(w) w_out FROM (SELECT red, var, g1 grupo, w FROM e WHERE g1 <> g2 UNION ALL SELECT red, var, g2, w FROM e WHERE g1 <> g2) GROUP BY ALL), k AS (SELECT red, var, grupo, count(*) k, sum(sanc_exp::INT) nse, sum(sanc_alguna::INT) nsa FROM mem GROUP BY ALL) SELECT k.*, coalesce(w_in, 0) w_in, coalesce(w_out, 0) w_out, coalesce(w_in, 0) / (k.k * (k.k - 1) / 2.0) C, CASE WHEN coalesce(w_in,0) + coalesce(w_out,0) > 0 THEN coalesce(w_in,0) / (coalesce(w_in,0) + coalesce(w_out,0)) ELSE 0 END E FROM k LEFT JOIN win USING (red, var, grupo) LEFT JOIN wout USING (red, var, grupo)""") x("ALTER TABLE gm ADD COLUMN S DOUBLE"); x("UPDATE gm SET S = C * E") x("""ALTER TABLE gm ADD COLUMN clase VARCHAR"""); x("""UPDATE gm SET clase = CASE WHEN nse >= 2 THEN 'pos' WHEN nsa = 0 THEN 'ctrl' ELSE 'fuera' END""") cmp = x("""SELECT count(*), count(*) FILTER (WHERE g2.grupo IS NULL), max(abs(g1.C - g2.C) / greatest(abs(g1.C), 1e-12)), max(abs(g1.E - g2.E) / greatest(abs(g1.E), 1e-12)), max(abs(g1.S - g2.S) / greatest(abs(g1.S), 1e-12)), count(*) FILTER (WHERE g1.clase <> g2.clase), count(*) FILTER (WHERE g1.w_in <> g2.w_in OR g1.w_out <> g2.w_out) FROM g1 LEFT JOIN gm g2 ON g2.red = g1.red AND g2.var = g1.var AND g2.grupo = g1.grupo""").fetchone() print('grupos impl1, sin pareja, máx dif rel C/E/S, clase distinta, pesos distintos', cmp) n2 = x("SELECT count(*) FROM gm").fetchone()[0] def estr(k): for lim, lab in ((2, '2'), (3, '3'), (5, '4-5'), (10, '6-10'), (20, '11-20'), (50, '21-50')): if k <= lim: return lab return '>50' def auc2(var, emp): rows = x("SELECT red, k, S, clase FROM gm WHERE var = ? AND split_part(red, '|', 1) <> 'SAMUR/01/24'", [var]).fetchall() ctrl = {} for red, k, S, cl in rows: if cl == 'ctrl': ctrl.setdefault((red, estr(k) if emp else 0), []).append(S) for v in ctrl.values(): v.sort() fs = [] for red, k, S, cl in rows: if cl != 'pos': continue c = ctrl.get((red, estr(k) if emp else 0)) if not c: continue lo, hi = bisect.bisect_left(c, S), bisect.bisect_right(c, S) fs.append((lo + (hi - lo) / 2) / len(c)) return sum(fs) / len(fs), len(fs) r1 = json.load(open('/w/interno/20_res.json')) res = dict(grupos_impl1=cmp[0], grupos_impl2=n2, sin_pareja=cmp[1], max_dif_rel=dict(C=cmp[2], E=cmp[3], S=cmp[4]), clase_distinta=cmp[5], pesos_distintos=cmp[6]) for nom, var, emp in (('AUC_A', 'A', False), ('AUC_A_emparejado', 'A', True), ('AUC_B', 'B', False), ('AUC_B_emparejado', 'B', True)): a, n = auc2(var, emp); res[nom] = dict(impl2=a, impl1=r1[nom]['auc'], n2=n, n1=r1[nom]['n_pos'], dif=abs(a - r1[nom]['auc'])) res['pasa'] = (cmp[1] == 0 and max(cmp[2], cmp[3], cmp[4]) <= 0.005 and all(res[k]['dif'] <= 0.005 for k in ('AUC_A', 'AUC_A_emparejado', 'AUC_B', 'AUC_B_emparejado'))) json.dump(res, open('/w/interno/30_impl2.json', 'w'), indent=1) print(json.dumps(res, indent=1))