"""CO1b · 30 · impl2 en SQL (DuckDB): aristas, |c_A|, Jaccard, s_in/s_out, coherencia exp(avg ln w)/avg w, exclusividad, C1/E1, componente gigante por propagación de etiqueta mínima en SQL; AUC por búsqueda binaria. Compara con impl1.""" 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 red, var, grupo, emp, sanc_exp, sanc_alguna FROM read_parquet('/co1/interno/10_miembros.parquet') WHERE NOT red LIKE 'SAMUR/01/24%'") 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") 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(*) BETWEEN 2 AND CASE WHEN var='A' THEN 30 ELSE 50 END) USING (red, var, g)""") x("CREATE TEMP TABLE nc AS SELECT red, var, emp, count(*) c FROM tv GROUP BY ALL") x("""CREATE TEMP TABLE ed AS SELECT a.red, a.var, a.emp e1, b.emp e2, count(*)::DOUBLE w FROM tv a JOIN tv b USING (red, var, g) WHERE a.emp < b.emp GROUP BY ALL""") x("""CREATE TEMP TABLE edj AS SELECT ed.*, ed.w / (n1.c + n2.c - ed.w) j FROM ed JOIN nc n1 ON n1.red=ed.red AND n1.var=ed.var AND n1.emp=ed.e1 JOIN nc n2 ON n2.red=ed.red AND n2.var=ed.var AND n2.emp=ed.e2""") # componentes: etiqueta mínima iterativa x("CREATE TEMP TABLE und AS SELECT red, var, e1 a, e2 b FROM ed UNION ALL SELECT red, var, e2, e1 FROM ed") x("CREATE TEMP TABLE lab AS SELECT red, var, emp, emp l FROM nc") it = 0 while True: it += 1 x("""CREATE OR REPLACE TEMP TABLE lab2 AS SELECT lab.red, lab.var, lab.emp, least(lab.l, coalesce(min(n.l), lab.l)) l FROM lab LEFT JOIN und ON und.red=lab.red AND und.var=lab.var AND und.a=lab.emp LEFT JOIN lab n ON n.red=und.red AND n.var=und.var AND n.emp=und.b GROUP BY lab.red, lab.var, lab.emp, lab.l""") ch = x("SELECT count(*) FROM lab JOIN lab2 USING (red, var, emp) WHERE lab.l <> lab2.l").fetchone()[0] x("DROP TABLE lab"); x("ALTER TABLE lab2 RENAME TO lab") if ch == 0: break x("""CREATE TEMP TABLE cg AS SELECT red, var, l FROM (SELECT red, var, l, n, row_number() OVER (PARTITION BY red, var ORDER BY n DESC) rn FROM (SELECT red, var, l, count(*) n FROM lab GROUP BY red, var, l)) WHERE rn = 1""") x("""CREATE TEMP TABLE gm AS WITH e AS (SELECT edj.*, m1.grupo g1, m2.grupo g2 FROM edj JOIN mem m1 ON m1.red=edj.red AND m1.var=edj.var AND m1.emp=edj.e1 JOIN mem m2 ON m2.red=edj.red AND m2.var=edj.var AND m2.emp=edj.e2), inn AS (SELECT red, var, g1 grupo, sum(w) w_in, sum(j) j_in, exp(avg(ln(j))) / avg(j) cg FROM e WHERE g1=g2 GROUP BY ALL), outt AS (SELECT red, var, grupo, sum(w) w_out, sum(j) j_out FROM (SELECT red, var, g1 grupo, w, j FROM e WHERE g1<>g2 UNION ALL SELECT red, var, g2, w, j FROM e WHERE g1<>g2) GROUP BY ALL), k AS (SELECT m.red, m.var, m.grupo, count(*) k, sum(m.sanc_exp::INT) nse, sum(m.sanc_alguna::INT) nsa, bool_and(lab.l = cg.l) en_cg FROM mem m JOIN lab USING (red, var, emp) JOIN cg USING (red, var) GROUP BY ALL) SELECT k.*, coalesce(w_in,0) w_in, coalesce(w_out,0) w_out, coalesce(cg,0) CG, CASE WHEN coalesce(j_in,0)+coalesce(j_out,0) > 0 THEN coalesce(j_in,0)/(coalesce(j_in,0)+coalesce(j_out,0)) ELSE 0 END EG, coalesce(w_in,0)/(k.k*(k.k-1)/2.0) C1, 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 E1, CASE WHEN nse >= 2 THEN 'pos' WHEN nsa = 0 THEN 'ctrl' ELSE 'fuera' END clase FROM k LEFT JOIN inn USING (red, var, grupo) LEFT JOIN outt USING (red, var, grupo) WHERE k.k >= 2""") x("CREATE TEMP TABLE g1 AS SELECT * FROM read_parquet('/w/interno/10_grupos_b.parquet')") cmp = x("""SELECT count(*), count(*) FILTER (WHERE g2.grupo IS NULL), count(*) FILTER (WHERE g1.en_cg <> g2.en_cg), count(*) FILTER (WHERE g1.clase <> g2.clase), max(abs(g1.CG-g2.CG)/greatest(abs(g1.CG),1e-12)), max(abs(g1.EG-g2.EG)/greatest(abs(g1.EG),1e-12)), max(abs(g1.SWK-g2.CG*g2.EG)/greatest(abs(g1.SWK),1e-12)), max(abs(g1.C1-g2.C1)/greatest(abs(g1.C1),1e-12)), max(abs(g1.E1-g2.E1)/greatest(abs(g1.E1),1e-12)) FROM g1 LEFT JOIN gm g2 USING (red, var, grupo)""").fetchone() cmp_ev = x("""SELECT count(*), max(abs(g1.CG-g2.CG)/greatest(abs(g1.CG),1e-12)), max(abs(g1.EG-g2.EG)/greatest(abs(g1.EG),1e-12)), max(abs(g1.SWK-g2.CG*g2.EG)/greatest(abs(g1.SWK),1e-12)) FROM g1 JOIN gm g2 USING (red, var, grupo) WHERE g1.en_cg AND g1.k >= 3 AND g1.clase IN ('pos','ctrl')""").fetchone() def tramo(k, kmin): for lim, lab in ((5, 'a'), (10, 'b'), (20, 'c'), (50, 'd')): if k <= lim: return lab return 'e' def auc2(var, kmin, expr): rows = x(f"SELECT red, k, {expr}, clase FROM gm WHERE var=? AND en_cg AND k >= ? AND clase IN ('pos','ctrl')", [var, kmin]).fetchall() c = {} for red, k, s, cl in rows: if cl == 'ctrl': c.setdefault((red, tramo(k, kmin)), []).append(s) for v in c.values(): v.sort() fs = [] for red, k, s, cl in rows: if cl != 'pos' or (red, tramo(k, kmin)) not in c: continue cs = c[(red, tramo(k, kmin))]; lo, hi = bisect.bisect_left(cs, s), bisect.bisect_right(cs, s) fs.append((lo + (hi - lo) / 2) / len(cs)) return sum(fs) / len(fs), len(fs) r1 = json.load(open('/w/interno/20_res.json')) res = dict(iteraciones_componentes=it, grupos_impl1=cmp[0], sin_pareja=cmp[1], en_cg_distinto=cmp[2], clase_distinta=cmp[3], max_dif_rel_todos=dict(CG=cmp[4], EG=cmp[5], SWK=cmp[6], C1=cmp[7], E1=cmp[8]), evaluables=dict(n=cmp_ev[0], CG=cmp_ev[1], EG=cmp_ev[2], SWK=cmp_ev[3])) for nom, var, kmin, ex in (('A_k3_SWK', 'A', 3, 'CG*EG'), ('A_k5_SWK', 'A', 5, 'CG*EG'), ('A_k3_S1', 'A', 3, 'C1*E1'), ('B_k3_SWK', 'B', 3, 'CG*EG')): a, n = auc2(var, kmin, ex); 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 cmp[2] == 0 and cmp[3] == 0 and max(cmp[4:9]) <= 0.005 and all(res[k]['dif'] <= 0.005 and res[k]['n1'] == res[k]['n2'] for k in ('A_k3_SWK', 'A_k5_SWK', 'A_k3_S1', 'B_k3_SWK'))) json.dump(res, open('/w/interno/30_impl2.json', 'w'), indent=1) print(json.dumps(res, indent=1))