"""CPV1 · segunda implementación (pre-registro §10): puntuación en SQL de DuckDB, escrita desde §2 y §4 del pre-registro, SIN importar cpvtexto ni el código de py/. Re-deriva la población desde unified (E 2024-2025, muestra de 100.000 por hash(id_canonical || 'cpv1-impl2'), y V completa), normaliza y tokeniza en SQL, calcula TF-IDF por bloque, puntúa con los pesos del JSON exportado, aplica la temperatura y el umbral de su régimen y escribe impl2/salidas/pred_impl2.parquet. Uso (etl:v2): python impl2_puntuar.py MODELO.json.gz CALIBRACION.json Lo único que toma de Python es leer el JSON (json.load) y volcarlo a tablas.""" import sys, json, gzip, time, os, duckdb T0 = time.time() def log(*a): print(f'[{time.time()-T0:6.0f}s]', *a, flush=True) U = '/d/unified_20261025'; O = '/w/impl2/salidas'; TMP = '/w/impl2/tmp' os.makedirs(O, exist_ok=True); os.makedirs(TMP, exist_ok=True) modelo = json.load(gzip.open(sys.argv[1], 'rt', encoding='utf-8')) cal = json.load(open(sys.argv[2])) con = duckdb.connect() # E7, 3-nov (ingeniería, no toca reglas): el 27-oct no cabía con 5 GB de temporal (OOM al trocear los términos); 25 GB con.execute("SET memory_limit='5GB'; SET threads=3; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='25GB'; SET preserve_insertion_order=false") # --- el JSON a tablas (TSV intermedio: el texto de las variables no lleva tabuladores ni saltos) with open(f'{TMP}/idf.tsv', 'w', encoding='utf-8') as f: f.write('feat\tbloque\tidf\n') for b, nom in (('palabras', 'p'), ('caracteres', 'c')): for k, v in (modelo['idf'].get(b) or {}).items(): f.write(f'{k}\t{nom}\t{v!r}\n') with open(f'{TMP}/w.tsv', 'w', encoding='utf-8') as f: f.write('feat\tk\tw\n') for k, lst in modelo['pesos'].items(): for j in range(0, len(lst), 2): f.write(f'{k}\t{lst[j]}\t{lst[j + 1]!r}\n') with open(f'{TMP}/pares.tsv', 'w', encoding='utf-8') as f: f.write('par\n') for p in modelo['pares_en_dominio']: f.write(p.replace('\\', '\\\\').replace('\t', '\\t').replace('\n', '\\n') + '\n') RD = "delim='\t', header=true, quote='', escape='', all_varchar=true" con.execute(f"CREATE TABLE idf AS SELECT feat, bloque, idf::DOUBLE idf FROM read_csv('{TMP}/idf.tsv', {RD})") con.execute(f"CREATE TABLE w AS SELECT feat, k::INT k, w::DOUBLE w FROM read_csv('{TMP}/w.tsv', {RD})") con.execute(f"CREATE TABLE pares AS SELECT replace(replace(replace(par, '\\n', chr(10)), '\\t', chr(9)), '\\\\', '\\') par FROM read_csv('{TMP}/pares.tsv', {RD})") con.execute("CREATE TABLE sesgo AS SELECT (i - 1)::INT k, b FROM (SELECT unnest(?::DOUBLE[]) b, generate_subscripts(?::DOUBLE[], 1) i)", [modelo['sesgo'], modelo['sesgo']]) con.execute("CREATE TABLE clases AS SELECT (i - 1)::INT k, c FROM (SELECT unnest(?::VARCHAR[]) c, generate_subscripts(?::VARCHAR[], 1) i)", [modelo['clases'], modelo['clases']]) log('modelo', con.execute("SELECT (SELECT count(*) FROM idf), (SELECT count(*) FROM w), (SELECT count(*) FROM pares)").fetchone()) # --- población (§2): filas que cuentan con.execute(f"CREATE VIEW u AS SELECT * FROM read_parquet('{U}/*/*/*.parquet', hive_partitioning=1, union_by_name=1)") con.execute(f"CREATE TABLE nc AS SELECT id_canonical FROM read_parquet('{U}.vinculos/filas.parquet') WHERE NOT cuenta") DIV = ('03 09 14 15 16 18 19 22 24 30 31 32 33 34 35 37 38 39 41 42 43 44 45 48 50 51 55 60 63 64 65 66 70 71 72 73 75 76 77 79 80 ' '85 90 92 98').split() dl = ','.join(f"'{d}'" for d in DIV) CUENTA = """unidad = 'adjudicacion' AND copia_de IS NULL AND coalesce(correccion_imp_adj, '') <> 'copia_otra_fuente' AND id_canonical NOT IN (SELECT id_canonical FROM nc)""" con.execute(f"""CREATE TABLE r0 AS SELECT 'E' conj, id_canonical, source, anio_ref, organo_nif, organo_nombre, dir3, tipo_contrato, es_menor, imp_adj_eur, objeto, cpv_division verdad FROM u WHERE {CUENTA} AND cpv_division IN ({dl}) AND anio_ref IN (2024, 2025) AND objeto IS NOT NULL AND trim(objeto) <> ''""") # V: sin cpv_division; en su grupo_contrato, otras adjudicaciones con CPV válida y una sola división con.execute(f"""CREATE TABLE g AS SELECT v.grupo_contrato, count(DISTINCT x.cpv_division) nd, any_value(x.cpv_division) d FROM read_parquet('{U}.vinculos/filas.parquet') v JOIN u x USING (id_canonical) WHERE x.unidad = 'adjudicacion' AND x.cpv_division IN ({dl}) GROUP BY 1""") con.execute(f"""INSERT INTO r0 SELECT 'V', x.id_canonical, x.source, x.anio_ref, x.organo_nif, x.organo_nombre, x.dir3, x.tipo_contrato, x.es_menor, x.imp_adj_eur, x.objeto, g.d FROM u x JOIN read_parquet('{U}.vinculos/filas.parquet') v USING (id_canonical) JOIN g USING (grupo_contrato) WHERE x.unidad = 'adjudicacion' AND x.copia_de IS NULL AND coalesce(x.correccion_imp_adj, '') <> 'copia_otra_fuente' AND v.cuenta AND x.cpv_division IS NULL AND g.nd = 1 AND x.anio_ref BETWEEN 2018 AND 2025 AND x.objeto IS NOT NULL AND trim(x.objeto) <> ''""") log('r0', con.execute("SELECT conj, count(*) FROM r0 GROUP BY 1").fetchall()) # E7, 3-nov (ingeniería, la misma muestra): solo se trocean las 120.000 filas de E de menor hash (las 100.000 de menor hash CON # términos están entre ellas: sin términos hay < 0,1 %); trocear las 2,4 M no cabía en 8 GB con.execute("""DELETE FROM r0 WHERE conj = 'E' AND id_canonical NOT IN (SELECT id_canonical FROM r0 WHERE conj = 'E' ORDER BY hash(id_canonical || 'cpv1-impl2'), id_canonical LIMIT 120000)""") log('r0 recortado', con.execute("SELECT conj, count(*) FROM r0 GROUP BY 1").fetchall()) # --- §4.1 normalización y términos (NFKD: los compatibles latinos frecuentes, a mano; luego strip_accents) NFKD = [('º', 'o'), ('ª', 'a'), ('²', '2'), ('³', '3'), ('¹', '1'), ('fi', 'fi'), ('fl', 'fl'), ('µ', 'μ'), ('½', '1⁄2'), ('¼', '1⁄4'), ('¾', '3⁄4')] expr = "lower(substr(objeto, 1, 1000))" for a, b in NFKD: expr = f"replace({expr}, '{a}', '{b}')" con.execute(f"""CREATE TABLE r1 AS SELECT conj, id_canonical, source, anio_ref, organo_nif, organo_nombre, dir3, tipo_contrato, es_menor, imp_adj_eur, verdad, string_split(regexp_replace(strip_accents({expr}), '[^a-z0-9]+', ' ', 'g'), ' ') l FROM r0""") con.execute("""CREATE TABLE tk AS SELECT id_canonical, t, row_number() OVER (PARTITION BY id_canonical ORDER BY pos) n FROM ( SELECT id_canonical, unnest(l) t, generate_subscripts(l, 1) pos FROM r1) WHERE length(t) >= 2 AND NOT regexp_full_match(t, '[0-9]+')""") con.execute("DELETE FROM tk WHERE n > 40") # muestra de E (100.000 con términos) y V completa con.execute("""CREATE TABLE pob AS SELECT r1.* EXCLUDE (l) FROM r1 WHERE id_canonical IN (SELECT id_canonical FROM tk)""") con.execute("""CREATE TABLE ids AS SELECT id_canonical FROM pob WHERE conj = 'V' UNION ALL SELECT id_canonical FROM (SELECT id_canonical FROM pob WHERE conj = 'E' ORDER BY hash(id_canonical || 'cpv1-impl2'), id_canonical LIMIT 100000)""") con.execute("DELETE FROM pob WHERE id_canonical NOT IN (SELECT id_canonical FROM ids)") con.execute("DELETE FROM tk WHERE id_canonical NOT IN (SELECT id_canonical FROM ids)") log('pob', con.execute("SELECT conj, count(*) FROM pob GROUP BY 1").fetchall()) # --- variables de texto con su recuento # E7, 3-nov (ingeniería, mismo resultado): los cuatrigramas de caracteres solo si el modelo los tiene (el v1 es F1, sin ellos: # las variables que no están en el vocabulario no cuentan). Con ellos, 57 M de filas intermedias: el contenedor de 8 GB moría CON_C = con.execute("SELECT count(*) FROM idf WHERE bloque = 'c'").fetchone()[0] > 0 log('paso fe ') con.execute("""CREATE TABLE fe AS SELECT id_canonical, 'u' || t feat FROM tk UNION ALL SELECT id_canonical, 'b' || t || ' ' || t2 FROM (SELECT id_canonical, t, lead(t) OVER (PARTITION BY id_canonical ORDER BY n) t2 FROM tk) WHERE t2 IS NOT NULL""") if CON_C: con.execute("""INSERT INTO fe SELECT id_canonical, 'c' || substr(p, i, 4) FROM (SELECT id_canonical, '<' || t || '>' p FROM tk) q, LATERAL (SELECT unnest(range(1, length(q.p) - 2)) i)""") log('paso fv ') con.execute("""CREATE TABLE fv AS SELECT f.id_canonical, f.feat, i.bloque, (1 + ln(count(*))) * any_value(i.idf) v FROM fe f JOIN idf i USING (feat) GROUP BY 1, 2, 3""") log('paso fx ') con.execute("""CREATE TABLE fx AS SELECT id_canonical, feat, v / sqrt(sum(v * v) OVER (PARTITION BY id_canonical, bloque)) x FROM fv""") # --- §4.2 indicadores log('paso cat') con.execute(f"""CREATE TABLE cat AS WITH o AS (SELECT organo_nif, organo_nombre, dir3, organo_clave FROM read_parquet('{U}.identidad/organos_filas.parquet')) SELECT p.id_canonical, p.source, o.organo_clave, CASE WHEN o.organo_clave IS NOT NULL THEN 'o:' || o.organo_clave END c1, 't:' || coalesce(p.tipo_contrato, '∅') c2, 'm:' || CASE WHEN p.es_menor THEN '1' WHEN NOT p.es_menor THEN '0' ELSE '∅' END c3, 'i:' || CASE WHEN p.imp_adj_eur IS NULL OR isnan(p.imp_adj_eur) OR p.imp_adj_eur <= 0 THEN '∅' WHEN p.imp_adj_eur < 500 THEN 'a' WHEN p.imp_adj_eur < 3000 THEN 'b' WHEN p.imp_adj_eur < 15000 THEN 'c' WHEN p.imp_adj_eur < 40000 THEN 'd' WHEN p.imp_adj_eur < 140000 THEN 'e' WHEN p.imp_adj_eur < 1000000 THEN 'f' WHEN p.imp_adj_eur < 5000000 THEN 'g' ELSE 'h' END c4 FROM pob p LEFT JOIN o ON o.organo_nif IS NOT DISTINCT FROM p.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM p.organo_nombre AND o.dir3 IS NOT DISTINCT FROM p.dir3""") con.execute("""CREATE TABLE cf AS SELECT id_canonical, c feat FROM (SELECT id_canonical, unnest([c1, c2, c3, c4]) c FROM cat) WHERE c IS NOT NULL""") # --- puntuaciones, régimen, probabilidad log('paso s A') con.execute("""CREATE TABLE s AS WITH contrib AS (SELECT fx.id_canonical, w.k, sum(fx.x * w.w) s FROM fx JOIN w USING (feat) GROUP BY 1, 2 UNION ALL SELECT cf.id_canonical, w.k, sum(w.w) FROM cf JOIN w USING (feat) GROUP BY 1, 2), c2 AS (SELECT id_canonical, k, sum(s) s FROM contrib GROUP BY 1, 2) SELECT i.id_canonical, sg.k, sg.b + coalesce(c2.s, 0) s FROM (SELECT id_canonical FROM pob) i CROSS JOIN sesgo sg LEFT JOIN c2 ON c2.id_canonical = i.id_canonical AND c2.k = sg.k""") Te, Tf = cal['temperatura']['en'], cal['temperatura']['fuera'] ue = cal['umbral']['en'] if cal['umbral']['en'] is not None else 2.0 uf = cal['umbral']['fuera'] if cal['umbral']['fuera'] is not None else 2.0 con.execute(f"""CREATE TABLE reg AS SELECT c.id_canonical, CASE WHEN c.organo_clave IS NOT NULL AND (c.source || chr(31) || c.organo_clave) IN (SELECT par FROM pares) THEN 'en' ELSE 'fuera' END regimen FROM cat c""") log('paso pr ') con.execute(f"""CREATE TABLE pr AS WITH z AS (SELECT s.id_canonical, s.k, s.s / CASE WHEN r.regimen = 'en' THEN {Te!r} ELSE {Tf!r} END z, r.regimen FROM s JOIN reg r USING (id_canonical)), m AS (SELECT id_canonical, max(z) mz FROM z GROUP BY 1), e AS (SELECT z.id_canonical, z.k, z.regimen, exp(z.z - m.mz) e FROM z JOIN m USING (id_canonical)), p AS (SELECT id_canonical, k, regimen, e / sum(e) OVER (PARTITION BY id_canonical) p FROM e) SELECT id_canonical, regimen, list(k ORDER BY p DESC, k)[1:3] top, list(p ORDER BY p DESC, k)[1:3] ptop FROM p GROUP BY 1, 2""") con.execute(f"""COPY (SELECT pob.conj, pob.id_canonical, pob.source, pob.anio_ref, pob.verdad, pr.regimen, (SELECT c FROM clases WHERE k = pr.top[1]) d1, pr.ptop[1] p1, (SELECT c FROM clases WHERE k = pr.top[2]) d2, (SELECT c FROM clases WHERE k = pr.top[3]) d3, pr.ptop[1] >= CASE WHEN pr.regimen = 'en' THEN {ue!r} ELSE {uf!r} END emitida, (SELECT list(t ORDER BY n) FROM tk WHERE tk.id_canonical = pob.id_canonical) terminos FROM pob JOIN pr USING (id_canonical)) TO '{O}/pred_impl2.parquet' (FORMAT PARQUET)""") log('fin', con.execute(f"SELECT conj, count(*), avg((d1 = verdad)::INT), avg(emitida::INT) FROM '{O}/pred_impl2.parquet' GROUP BY 1").fetchall())