"""E-M2 · etiqueta refinada E0b (DEFINICIONES_E0b.md): las mismas unidades y licitaciones de medir.py (tablas p_* de /w/tmp/medir.duckdb) con el etiquetado del módulo y la cabeza activada; lift, A1 y K, Mantel-Haenszel, y las muestras de validación (jueces y origen). Uso (contenedor, sin red): python3 /w/sql/medir_e0b.py""" import csv import sys import time from pathlib import Path import duckdb sys.path.insert(0, "/app") from etl import series_licitadas as sl # noqa: E402 T0 = time.time() W = Path("/w") 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) I = "/d/unified_20261018.identidad" for t in ("organos_filas", "organos", "organos_tipo"): con.execute(f"CREATE OR REPLACE TEMP VIEW id_{t} AS SELECT * FROM read_parquet('{I}/{t}.parquet')") con.execute("CREATE OR REPLACE TEMP VIEW u AS SELECT * FROM read_parquet('/d/unified_20261018/**/*.parquet', hive_partitioning=true, union_by_name=true)") con.execute("CREATE OR REPLACE TEMP TABLE otipo AS SELECT organo_clave, coalesce(tipo, 'sin_tipo') tipo FROM id_organos_tipo") con.execute("CREATE OR REPLACE TEMP TABLE sl_u AS SELECT *, em2_cabeza(objeto_norm) AS cabeza FROM p_u") con.execute("CREATE OR REPLACE TEMP TABLE sl_lic AS SELECT * FROM p_lic") e = sl.Etiquetado(con, set(), 2025, cabeza=True) e.objetos() e.etiquetar() e.salidas(sl.seudonimos(con)) e.objetos(clave="entidad_clave", sufijo="_e") e.etiquetar(clave="entidad_clave", sufijo="_e") for t, n in (("sl_et", "p_et_e0b"), ("sl_par", "p_par_e0b"), ("sl_series", "p_series_e0b"), ("sl_hechos", "p_hechos_e0b"), ("sl_lift", "p_lift_e0b"), ("sl_oa", "p_oa_e0b"), ("sl_et_e", "p_et_e0b_e4"), ("sl_u", "p_u_b")): con.execute(f"CREATE OR REPLACE TABLE {n} AS SELECT * FROM {t}") md = ["# E-M2 · etiqueta E0b (DEFINICIONES_E0b.md)\n"] def q(titulo, sql, csv_=None): 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 (f"{v:.4g}" if isinstance(v, float) else str(v)).replace("|", "¦")[:150] for v in f) + " |\n") if csv_: con.execute(f"COPY ({sql}) TO '/w/salidas/{csv_}' (HEADER, DELIMITER ';')") return filas q("1. Lift por fuente y año (E0b)", """ SELECT fuente, coalesce(anio::VARCHAR, '2019-2024') anio, series, series_pasaron, tasa_series, control, control_pasaron, tasa_control, lift, lift_ic95_inf, lift_ic95_sup FROM p_lift_e0b ORDER BY fuente, anio NULLS LAST""", "30_lift_e0b.csv") # A1 y K con las reglas de DEFINICIONES_PREVIAS §6 q("2. A1 y K por fuente (E0b)", """ WITH t AS (SELECT * FROM p_lift_e0b WHERE anio IS NULL), y AS (SELECT fuente, count(*) FILTER (WHERE series >= 100 AND control >= 100) evaluables, count(*) FILTER (WHERE series >= 100 AND control >= 100 AND lift >= 2) evaluables_ok, min(lift) FILTER (WHERE series >= 100 AND control >= 100) lift_min_evaluable FROM p_lift_e0b WHERE anio IS NOT NULL GROUP BY 1) SELECT t.fuente, t.series, t.lift lift_agrupado, y.evaluables, y.evaluables_ok, y.lift_min_evaluable, (t.lift >= 2 AND y.evaluables >= 3 AND y.evaluables_ok = y.evaluables) pasa_a1, (t.series >= 500 AND t.lift < 1.5) muere_k FROM t JOIN y USING (fuente) ORDER BY t.series DESC""", "30_a1k_e0b.csv") q("2b. Lo mismo con E0 (para comparar)", """ WITH t AS (SELECT * FROM p_lift WHERE anio IS NULL), y AS (SELECT fuente, count(*) FILTER (WHERE series >= 100 AND control >= 100) evaluables, count(*) FILTER (WHERE series >= 100 AND control >= 100 AND lift >= 2) evaluables_ok, min(lift) FILTER (WHERE series >= 100 AND control >= 100) lift_min_evaluable FROM p_lift WHERE anio IS NOT NULL GROUP BY 1) SELECT t.fuente, t.series, t.lift lift_agrupado, y.evaluables, y.evaluables_ok, y.lift_min_evaluable, (t.lift >= 2 AND y.evaluables >= 3 AND y.evaluables_ok = y.evaluables) pasa_a1, (t.series >= 500 AND t.lift < 1.5) muere_k FROM t JOIN y USING (fuente) ORDER BY t.series DESC""", "30_a1k_e0.csv") q("3. Mantel-Haenszel por órgano-año (E0b, informativo)", """ WITH b AS (SELECT u.fuente, u.organo_clave, u.anio, count(*) FILTER (WHERE u.tipo_unidad = 'serie' AND e.pasa) a, count(*) FILTER (WHERE u.tipo_unidad = 'serie' AND NOT e.pasa) b, count(*) FILTER (WHERE u.tipo_unidad = 'control' AND e.pasa) c, count(*) FILTER (WHERE u.tipo_unidad = 'control' AND NOT e.pasa) d FROM p_u u JOIN p_et_e0b e USING (tipo_unidad, unidad) GROUP BY ALL), s AS (SELECT *, a + b + c + d n FROM b WHERE a + b > 0 AND c + d > 0) SELECT fuente, count(*) estratos, sum(a + b) series, sum(c + d) control, round(sum(a * (c + d) / n) / nullif(sum(c * (a + b) / n), 0), 3) lift_mh FROM s GROUP BY 1 ORDER BY series DESC""") q("4. E0b con la entidad en vez del órgano (E4), agrupado", """ WITH g AS (SELECT u.fuente, u.tipo_unidad, e.pasa FROM p_u u JOIN p_et_e0b_e4 e USING (tipo_unidad, unidad)) SELECT fuente, count(*) FILTER (WHERE tipo_unidad = 'serie') series, round(100.0 * avg(pasa::INT) FILTER (WHERE tipo_unidad = 'serie'), 2) tasa_s, round(100.0 * avg(pasa::INT) FILTER (WHERE tipo_unidad = 'control'), 2) tasa_c, round(avg(pasa::INT) FILTER (WHERE tipo_unidad = 'serie') / nullif(avg(pasa::INT) FILTER (WHERE tipo_unidad = 'control'), 0), 3) lift FROM g GROUP BY 1 ORDER BY 2 DESC""") q("5. E0 frente a E0b: series que pasan", """ SELECT a.fuente, count(*) series, count(*) FILTER (WHERE a.paso_a_licitacion) e0, count(*) FILTER (WHERE b.paso_a_licitacion) e0b, count(*) FILTER (WHERE b.paso_a_licitacion AND NOT a.paso_a_licitacion) solo_e0b FROM p_series a JOIN p_series_e0b b USING (serie_m1r) GROUP BY 1 ORDER BY 2 DESC""") q("6. Rama (E0b), series que pasaron", "SELECT fuente, rama, count(*) n FROM p_series_e0b WHERE paso_a_licitacion GROUP BY 1, 2 ORDER BY 1, 3 DESC") q("7. Licitaciones casadas por serie (E0b)", """ SELECT fuente, quantile_cont(objetos_casados, 0.5) mediana, max(objetos_casados) maximo, count(*) FILTER (WHERE objetos_casados >= 10) con_10_o_mas FROM p_series_e0b WHERE paso_a_licitacion GROUP BY 1 ORDER BY 1""") q("8. Procedimiento de las licitaciones casadas (E0b, series de la lista)", """ SELECT s.fuente, h.procedimiento, h.basado_en, count(*) parejas, count(DISTINCT h.serie_m1r) series FROM p_hechos_e0b h JOIN p_series_e0b s USING (serie_m1r) WHERE s.fuente = 'PLACSP' GROUP BY ALL ORDER BY 4 DESC""") # ── Muestra de los jueces (semilla em2-b): 60 positivas de E0b (30 PLACSP, 30 del resto en proporción) y 30 que E0 # acepta y E0b no; una licitación por serie; barajadas; sin la etiqueta ni la rama ───────────────────────────────────── con.execute("""CREATE OR REPLACE TEMP TABLE jb AS WITH h AS (SELECT h.*, row_number() OVER (PARTITION BY h.serie_m1r ORDER BY md5('em2-b' || h.serie_m1r || h.licitacion)) rk FROM p_hechos_e0b h) SELECT 'e0b' grupo, h.serie_m1r, h.licitacion, s.fuente FROM h JOIN p_series_e0b s USING (serie_m1r) WHERE rk = 1""") con.execute("""CREATE OR REPLACE TEMP TABLE jo AS WITH x AS (SELECT a.serie_m1r FROM p_series a JOIN p_series_e0b b USING (serie_m1r) WHERE a.paso_a_licitacion AND NOT b.paso_a_licitacion), h AS (SELECT h.*, row_number() OVER (PARTITION BY h.serie_m1r ORDER BY md5('em2-b' || h.serie_m1r || h.licitacion)) rk FROM p_hechos h WHERE h.serie_m1r IN (SELECT serie_m1r FROM x)) SELECT 'solo_e0' grupo, h.serie_m1r, h.licitacion, s.fuente FROM h JOIN p_series s USING (serie_m1r) WHERE rk = 1""") pos = dict(con.execute("SELECT fuente, count(*) FROM jb WHERE fuente <> 'PLACSP' GROUP BY 1").fetchall()) tot = sum(pos.values()) cuota = {f: 30 * 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)[:30 - sum(asig.values())]: asig[f] += 1 asig["PLACSP"] = 30 partes = [f"(SELECT * FROM jb WHERE fuente = '{f}' ORDER BY md5('em2-b' || serie_m1r) LIMIT {k})" for f, k in asig.items() if k] partes.append("(SELECT * FROM jo ORDER BY md5('em2-b' || serie_m1r) LIMIT 30)") con.execute(f"CREATE OR REPLACE TEMP TABLE jm AS SELECT * FROM ({' UNION ALL '.join(partes)})") con.execute("""CREATE OR REPLACE TEMP TABLE jm2 AS WITH m AS (SELECT serie_m1r, id_canonical FROM p_men_placsp WHERE serie_m1r IN (SELECT serie_m1r FROM jm) UNION ALL SELECT serie_m1r, id_canonical FROM p_men_v2 WHERE serie_m1r IN (SELECT serie_m1r FROM jm)), o AS (SELECT m.serie_m1r, list(DISTINCT x.objeto)[1:3] objetos, count(*) n, min(year(x.fecha_adj)) a0, max(year(x.fecha_adj)) a1, any_value(x.organo_nombre) organo FROM m JOIN (SELECT id_canonical, objeto, fecha_adj, organo_nombre FROM u WHERE es_menor AND id_canonical IN (SELECT id_canonical FROM m)) x USING (id_canonical) GROUP BY 1), l AS (SELECT licitacion, arg_max(objeto, length(objeto)) objeto_lic, any_value(procedimiento) procedimiento, min(anio_lic) anio_lic FROM p_lic WHERE licitacion IN (SELECT licitacion FROM jm) GROUP BY 1) SELECT j.*, o.organo, o.objetos, o.n, o.a0, o.a1, l.objeto_lic, l.procedimiento, l.anio_lic, md5('em2-b-orden' || j.serie_m1r || j.licitacion) orden FROM jm j JOIN o USING (serie_m1r) JOIN l USING (licitacion)""") # El objeto de la licitación: el de la fila casada (la de mayor JW de la pareja en hechos), no la más larga con.execute("""CREATE OR REPLACE TEMP TABLE jm3 AS SELECT j.* REPLACE (coalesce(hb.objeto, ho.objeto, j.objeto_lic) AS objeto_lic) FROM jm2 j LEFT JOIN p_hechos_e0b hb ON hb.serie_m1r = j.serie_m1r AND hb.licitacion = j.licitacion AND j.grupo = 'e0b' LEFT JOIN p_hechos ho ON ho.serie_m1r = j.serie_m1r AND ho.licitacion = j.licitacion AND j.grupo = 'solo_e0'""") filas = con.execute("""SELECT row_number() OVER (ORDER BY orden) id, organo, n, a0, a1, objetos, anio_lic, procedimiento, objeto_lic, grupo, fuente, serie_m1r, licitacion FROM jm3 ORDER BY orden""").fetchall() (W / "cotejo").mkdir(exist_ok=True) with open(W / "cotejo" / "jueces_parejas.csv", "w", newline="", encoding="utf-8") as f: w = csv.writer(f, delimiter=";") w.writerow(["id", "organo", "contratos_menores", "anio_primero", "anio_ultimo", "objetos_menores", "anio_licitacion", "procedimiento_licitacion", "objeto_licitacion"]) for r in filas: w.writerow([r[0], r[1], r[2], r[3], r[4], " ‖ ".join(x or "" for x in r[5]), r[6], r[7], r[8]]) with open(W / "cotejo" / "jueces_clave.csv", "w", newline="", encoding="utf-8") as f: w = csv.writer(f, delimiter=";") w.writerow(["id", "grupo", "fuente", "serie_m1r", "licitacion"]) for r in filas: w.writerow([r[0], r[9], r[10], r[11], r[12]]) q("9. Muestra de los jueces", "SELECT grupo, fuente, count(*) n FROM jm3 GROUP BY 1, 2 ORDER BY 1, 3 DESC") # ── Muestra de origen de E0b (semilla em2-origen-b): 15 PLACSP y 5 del resto, licitación con ficha PLACSP o Euskadi ── con.execute("""CREATE OR REPLACE TEMP TABLE ob AS WITH h AS (SELECT h.*, s.fuente, s.objeto_norm obj_serie, s.n, s.suma, u.organo_clave organo_real, row_number() OVER (PARTITION BY h.serie_m1r ORDER BY md5('em2-origen-b' || h.serie_m1r || h.licitacion)) rk FROM p_hechos_e0b h JOIN p_series_e0b s USING (serie_m1r) JOIN p_u u ON u.unidad = h.serie_m1r AND u.tipo_unidad = 'serie' WHERE h.url_origen LIKE 'https://contrataciondelestado.es/%' OR h.url_origen LIKE 'https://www.contratacion.euskadi.eus/%'), o AS (SELECT *, row_number() OVER (PARTITION BY organo_real ORDER BY md5('em2-origen-b' || serie_m1r)) ro FROM h WHERE rk = 1) SELECT * FROM o WHERE ro = 1""") con.execute("""CREATE OR REPLACE TEMP TABLE o20b AS SELECT row_number() OVER (ORDER BY fuente <> 'PLACSP', md5('em2-origen-b' || serie_m1r)) orden, * FROM ( (SELECT * FROM ob WHERE fuente = 'PLACSP' ORDER BY md5('em2-origen-b' || serie_m1r) LIMIT 15) UNION ALL (SELECT * FROM ob WHERE fuente <> 'PLACSP' ORDER BY md5('em2-origen-b' || serie_m1r) LIMIT 5))""") con.execute("""CREATE OR REPLACE TEMP TABLE o20bm AS WITH m AS (SELECT serie_m1r, id_canonical, fecha_adj FROM p_men_placsp WHERE serie_m1r IN (SELECT serie_m1r FROM o20b) UNION ALL SELECT serie_m1r, id_canonical, fecha_adj FROM p_men_v2 WHERE serie_m1r IN (SELECT serie_m1r FROM o20b)), k AS (SELECT m.serie_m1r, m.id_canonical, m.fecha_adj, x.url_origen, x.objeto FROM m JOIN (SELECT id_canonical, url_origen, objeto FROM u WHERE es_menor AND id_canonical IN (SELECT id_canonical FROM m)) x USING (id_canonical)) SELECT serie_m1r, arg_min(url_origen, fecha_adj) url_menor, arg_min(objeto, fecha_adj) objeto_menor FROM k GROUP BY 1""") con.execute("""COPY (SELECT o.orden, o.fuente, o.organo_real organo_clave, o.serie_m1r, o.anio, o.n, o.suma, o.rama, o.obj_serie, o.licitacion, o.anio_licitacion, o.procedimiento, o.objeto objeto_lic, o.url_origen url_lic, m.url_menor, m.objeto_menor FROM o20b o LEFT JOIN o20bm m USING (serie_m1r) ORDER BY o.orden) TO '/w/cotejo/origen_e0b.csv' (HEADER, DELIMITER ';')""") q("10. Muestra de origen de E0b", "SELECT fuente, count(*) n FROM o20b GROUP BY 1 ORDER BY 2 DESC") md.append(f"\n_Tiempo: {time.time() - T0:.0f} s_\n") (W / "salidas" / "30_e0b.md").write_text("".join(md), encoding="utf-8") print("hecho", round(time.time() - T0))