"""E-M2 · muestras de los cotejos (DEFINICIONES_PREVIAS §6), sobre /w/tmp/medir.duckdb: - A2: 50 parejas positivas (semilla «em2»): 25 PLACSP y 25 del resto en proporción a sus positivos (resto mayor); coincidencia de tokens (2 tokens de contenido de 4 letras o más; 1 si la serie tiene uno) o de CPV (clase, 4 dígitos); regla literal y no circular. - Origen: 20 parejas (semilla «em2-origen»): 12 PLACSP y 8 del resto con url_origen de la licitación, un órgano distinto cada una; con la url de un contrato de la serie si la tiene. Uso (contenedor, sin red): python3 /w/sql/cotejo.py [etiqueta] (etiqueta: p_hechos por defecto; o la tabla de otra etiqueta con las mismas columnas). Deja /w/salidas/20_cotejo_.md y /w/cotejo/*.csv.""" import sys from pathlib import Path import duckdb sys.path.insert(0, "/app") from etl import series_licitadas as sl # noqa: E402 ET = sys.argv[1] if len(sys.argv) > 1 else "e0" HECHOS = {"e0": ("p_hechos", "p_series", "em2", "em2-origen")}.get(ET) or (f"p_hechos_{ET}", f"p_series_{ET}", f"em2-{ET}", f"em2-origen-{ET}") T_H, T_S, SEM, SEM_O = HECHOS con = duckdb.connect("/w/tmp/medir.duckdb") con.execute("SET memory_limit='7GB'; SET threads=6; SET temp_directory='/w/tmp/duckdb_tmp'; SET preserve_insertion_order=false") sl.macros(con) con.execute("CREATE OR REPLACE TEMP VIEW u AS SELECT * FROM read_parquet('/d/unified_20261018/**/*.parquet', hive_partitioning=true, union_by_name=true)") md = [f"# E-M2 · cotejos de la etiqueta {ET}\n"] def tabla(titulo, sql): r = con.execute(sql) cols = [d[0] for d in r.description] filas = r.fetchall() md.append(f"\n## {titulo}\n\n| " + " | ".join(cols) + " |\n|" + "|".join("---" for _ in cols) + "|\n") for f in filas: md.append("| " + " | ".join("∅" if v is None else str(v).replace("|", "¦")[:160] for v in f) + " |\n") return filas # Parejas positivas: una por serie (la licitación con menor huella de la semilla), con el objeto y el CPV de la serie y de # la fila casada de la licitación con.execute(f"""CREATE OR REPLACE TEMP TABLE cand AS WITH h AS (SELECT h.*, row_number() OVER (PARTITION BY h.serie_m1r ORDER BY md5('{SEM}' || h.serie_m1r || h.licitacion)) rk FROM {T_H} h) SELECT h.serie_m1r, h.licitacion, h.rama, h.jw, h.tokens_pct, h.objeto AS objeto_lic, h.url_origen AS url_lic, h.procedimiento, h.anio_licitacion, h.fuente_licitacion, s.fuente, s.organo_clave, s.anio, s.objeto_norm AS obj_serie, s.n, s.suma, u.cpvs AS cpv5_serie, u.tokens AS tokens_serie, u.organo_clave AS organo_real FROM h JOIN {T_S} s USING (serie_m1r) JOIN p_u u ON u.unidad = h.serie_m1r AND u.tipo_unidad = 'serie' WHERE h.rk = 1""") con.execute("""CREATE OR REPLACE TEMP TABLE cand2 AS SELECT c.*, l.cpv AS cpv_lic FROM cand c LEFT JOIN (SELECT licitacion, objeto, any_value(cpv) cpv FROM p_lic GROUP BY 1, 2) l ON l.licitacion = c.licitacion AND l.objeto IS NOT DISTINCT FROM c.objeto_lic""") # Reparto de las 25 del resto: proporcional a los positivos, resto mayor pos = dict(con.execute("SELECT fuente, count(*) FROM cand2 WHERE fuente <> 'PLACSP' GROUP BY 1").fetchall()) tot = sum(pos.values()) cuota = {f: 25 * n / tot for f, n in pos.items()} asig = {f: int(c) for f, c in cuota.items()} for f in sorted(cuota, key=lambda f: cuota[f] - int(cuota[f]), reverse=True)[:25 - sum(asig.values())]: asig[f] += 1 asig["PLACSP"] = 25 casos = " UNION ALL ".join( f"(SELECT * FROM cand2 WHERE fuente = '{f}' ORDER BY md5('{SEM}' || serie_m1r) LIMIT {k})" for f, k in asig.items() if k) con.execute(f"CREATE OR REPLACE TEMP TABLE a2 AS SELECT * FROM ({casos})") con.execute("""CREATE OR REPLACE TEMP TABLE a2c AS WITH t AS (SELECT *, list_filter(tokens_serie, w -> length(w) >= 4) ts, list_filter(em2_tokens(trim(regexp_replace(lower(strip_accents( coalesce(objeto_lic, ''))), '[^a-z]+', ' ', 'g'))), w -> length(w) >= 4) tl FROM a2) SELECT *, len(list_intersect(ts, tl)) AS comunes, (len(list_intersect(ts, tl)) >= CASE WHEN len(ts) = 1 THEN 1 ELSE 2 END) AS c_tokens, CASE WHEN cpv_lic IS NULL OR len(cpv5_serie) = 0 THEN NULL ELSE list_contains(list_transform(cpv5_serie, x -> substr(x, 1, 4)), substr(cpv_lic, 1, 4)) END AS c_cpv FROM t""") con.execute("""CREATE OR REPLACE TEMP TABLE a2r AS SELECT *, (c_tokens OR coalesce(c_cpv, FALSE)) AS literal, CASE WHEN rama = 'ambas' THEN TRUE WHEN rama = 'jw_cpv' THEN c_tokens ELSE c_cpv END AS no_circular FROM a2c""") tabla("Reparto de la muestra A2", "SELECT fuente, count(*) n FROM a2r GROUP BY 1 ORDER BY 2 DESC") tabla("A2 · resultado", """ SELECT count(*) parejas, count(*) FILTER (WHERE literal) coinciden_literal, round(100.0 * avg(literal::INT), 1) pct_literal, count(no_circular) evaluables_no_circular, count(*) FILTER (WHERE no_circular) coinciden_no_circular, round(100.0 * count(*) FILTER (WHERE no_circular) / count(no_circular), 1) pct_no_circular FROM a2r""") tabla("A2 · por rama", """ SELECT rama, count(*) n, count(*) FILTER (WHERE c_tokens) tokens, count(c_cpv) con_cpv_los_dos, count(*) FILTER (WHERE c_cpv) cpv, count(*) FILTER (WHERE no_circular) no_circular FROM a2r GROUP BY 1 ORDER BY 1""") tabla("A2 · las 50 parejas", """ SELECT fuente, rama, round(jw, 3) jw, tokens_pct, comunes, c_tokens, c_cpv, literal, no_circular, obj_serie, objeto_lic FROM a2r ORDER BY fuente, serie_m1r""") con.execute(f"COPY (SELECT * EXCLUDE (cpv5_serie, tokens_serie, ts, tl) FROM a2r) TO '/w/cotejo/a2_{ET}.csv' (HEADER, DELIMITER ';')") # Muestra de origen: 12 PLACSP y 8 del resto (con url de la licitación), órganos distintos con.execute(f"""CREATE OR REPLACE TEMP TABLE og AS SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY organo_clave ORDER BY md5('{SEM_O}' || serie_m1r)) ro FROM cand2 WHERE url_lic IS NOT NULL AND url_lic LIKE 'http%') WHERE ro = 1""") pos_o = dict(con.execute("SELECT fuente, count(*) FROM og WHERE fuente <> 'PLACSP' GROUP BY 1").fetchall()) tot_o = sum(pos_o.values()) cuota_o = {f: 8 * n / tot_o for f, n in pos_o.items()} asig_o = {f: int(c) for f, c in cuota_o.items()} for f in sorted(cuota_o, key=lambda f: cuota_o[f] - int(cuota_o[f]), reverse=True)[:8 - sum(asig_o.values())]: asig_o[f] += 1 asig_o["PLACSP"] = 12 casos_o = " UNION ALL ".join( f"(SELECT * FROM og WHERE fuente = '{f}' ORDER BY md5('{SEM_O}' || serie_m1r) LIMIT {k})" for f, k in asig_o.items() if k) con.execute(f"CREATE OR REPLACE TEMP TABLE o20 AS SELECT row_number() OVER (ORDER BY fuente, serie_m1r) AS orden, * FROM ({casos_o})") # Un contrato de la serie con su url (el primero) men = "p_men_placsp" if True else "" con.execute("""CREATE OR REPLACE TEMP TABLE o20m AS WITH m AS (SELECT serie_m1r, id_canonical, fecha_adj FROM p_men_placsp WHERE serie_m1r IN (SELECT serie_m1r FROM o20) UNION ALL SELECT serie_m1r, id_canonical, fecha_adj FROM p_men_v2 WHERE serie_m1r IN (SELECT serie_m1r FROM o20)), k AS (SELECT m.serie_m1r, m.id_canonical, m.fecha_adj, x.url_origen, x.objeto, x.imp_adj_eur FROM m JOIN (SELECT id_canonical, url_origen, objeto, imp_adj_eur FROM u WHERE es_menor AND id_canonical IN (SELECT id_canonical FROM m)) x USING (id_canonical)) SELECT serie_m1r, arg_min(id_canonical, fecha_adj) id_menor, arg_min(url_origen, fecha_adj) url_menor, arg_min(objeto, fecha_adj) objeto_menor, arg_min(imp_adj_eur, fecha_adj) imp_menor FROM k GROUP BY 1""") tabla("Muestra de origen (20)", """ SELECT o.orden, o.fuente, o.organo_real, o.anio, o.n, round(o.suma) suma, o.rama, o.obj_serie, o.licitacion, o.anio_licitacion, o.procedimiento, o.objeto_lic, o.url_lic, m.url_menor FROM o20 o LEFT JOIN o20m m USING (serie_m1r) ORDER BY o.orden""") con.execute(f"""COPY (SELECT o.orden, o.fuente, o.organo_real AS organo_clave, o.serie_m1r, o.anio, o.n, o.suma, o.rama, o.jw, o.tokens_pct, o.obj_serie, o.licitacion, o.fuente_licitacion, o.anio_licitacion, o.procedimiento, o.objeto_lic, o.url_lic, m.id_menor, m.url_menor, m.objeto_menor, m.imp_menor FROM o20 o LEFT JOIN o20m m USING (serie_m1r) ORDER BY o.orden) TO '/w/cotejo/origen_{ET}.csv' (HEADER, DELIMITER ';')""") Path(f"/w/salidas/20_cotejo_{ET}.md").write_text("".join(md), encoding="utf-8") print("hecho")