"""X2 · base: población común del job + unidades E-M2b (sl_u, sl_cad) + control E0 (par0 del piloto) + días M1e, persistidos en /w/interno/x2.duckdb. Sin etiquetas.""" import sys, time from pathlib import Path import duckdb sys.path.insert(0, "/app") from etl import series_licitadas as sl T0 = time.time() DB = Path("/w/interno/x2.duckdb"); DB.unlink(missing_ok=True) con = duckdb.connect(DB.as_posix()) con.execute("SET threads=6; SET memory_limit='7GB'; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") c, e = sl.preparar(con, Path("/d/unified_20261018"), Path("/d/unified_20261018.identidad"), Path("/d/unified_20261018.vinculos"), 2025) assert e is not None, (c.omitido, c.avisos) print("preparar", round(time.time() - T0), "s", flush=True) con.execute("""CREATE TABLE fpub AS SELECT id_canonical, min(fecha_pub) fecha_pub FROM u WHERE source='PLACSP' AND es_menor AND unidad='adjudicacion' AND id_canonical IN (SELECT id_canonical FROM pob) GROUP BY 1""") con.execute("""CREATE TABLE pobx AS SELECT p.id_canonical, p.organo_clave, m.entidad_clave, p.empresa_clave, p.clase, p.tipo, p.umbral AS umbral_job, CASE WHEN p.clase='obras' THEN 40000 ELSE 15000 END AS u_cat, p.objeto_norm, p.objeto, p.tipo_contrato, p.expediente, p.base::DOUBLE AS base, p.fecha_adj, p.anio, m.cpv, f.fecha_pub, em2_tokens(p.objeto_norm) AS tokens FROM pob p JOIN sl_men m USING (id_canonical) LEFT JOIN fpub f USING (id_canonical)""") con.execute("CREATE TABLE cad AS SELECT id_canonical, serie_m1r, cadena, organo_clave FROM sl_cad") con.execute("CREATE TABLE unid AS SELECT tipo_unidad, unidad, organo_clave, anio, n, suma::DOUBLE suma, en_lista FROM sl_u") # par0 del piloto (em2b_amplia/sql/10_candidatos.py), solo para saber qué cadenas de control son E0 con.execute("""CREATE TABLE par0 AS WITH c AS (SELECT p.tipo_unidad, p.unidad, p.obj_l, p.jw, p.r_tokens, u.organo_clave, u.anio, u.cpvs, u.cabeza FROM sl_par p JOIN sl_u u USING (tipo_unidad, unidad)), f AS (SELECT c.tipo_unidad, c.unidad, c.anio, l.licitacion, l.id_canonical, (c.jw >= 0.85 AND list_contains(c.cpvs, substr(l.cpv, 1, 5))) AS a, c.r_tokens AS b, (len(c.cabeza) > 0 AND len(list_intersect(c.cabeza, em2_tokens(l.objeto_norm))) = len(c.cabeza)) AS cab FROM c JOIN sl_lic l ON l.organo_clave = c.organo_clave AND l.anio_lic = c.anio + 1 AND l.objeto_norm = c.obj_l) SELECT tipo_unidad, unidad, any_value(anio) anio, bool_or((a OR b) AND cab) AS e0b FROM f WHERE a OR b GROUP BY tipo_unidad, unidad""") con.execute("CREATE TABLE m1e AS SELECT organo_clave, empresa_clave, clase, fecha_adj, dia_m1e, n_dia FROM m1e_d") for t in ("pobx", "cad", "unid", "par0", "m1e"): print(t, con.execute(f"SELECT count(*) FROM {t}").fetchone()[0]) print(con.execute("""SELECT anio, count(*) FILTER (WHERE tipo_unidad='serie'), count(*) FILTER (WHERE tipo_unidad='control'), (SELECT count(*) FROM par0 p WHERE p.anio=u.anio AND p.tipo_unidad='control'), (SELECT count(*) FROM par0 p WHERE p.anio=u.anio AND p.tipo_unidad='serie') FROM unid u GROUP BY anio ORDER BY anio""").fetchall()) print("pob", con.execute("SELECT count(*), count(cpv), count(fecha_pub), count(expediente), min(fecha_adj), max(fecha_adj), count(DISTINCT organo_clave) FROM pobx").fetchall()) print("total", round(time.time() - T0), "s")