"""E-M2 · primera implementación: medición en la PLACSP (población del job) y en las demás fuentes (V2), con las funciones del módulo etl/series_licitadas.py de la rama (montada en /app) y las variantes de DEFINICIONES_PREVIAS.md. Uso (contenedor em2-medir, sin red): python3 /w/sql/medir.py Deja la base /w/tmp/medir.duckdb (tablas p_*) y las salidas en /w/salidas/10_*.md y /w/salidas/*.csv.""" import json import sys import time from pathlib import Path import duckdb sys.path.insert(0, "/app") from etl import indicadores_organo as io # noqa: E402 from etl import series_licitadas as sl # noqa: E402 D = Path("/d") U = D / "unified_20261018" W = Path("/w") ANIO_FIN = 2025 T0 = time.time() md = ["# E-M2 · medición (primera implementación)\n", f"DuckDB {duckdb.__version__} · {time.strftime('%Y-%m-%d %H:%M:%S')}\n"] def fmt(v): if v is None: return "∅" if isinstance(v, float): return f"{v:,.4g}".replace(",", "X").replace(".", ",").replace("X", ".") if abs(v) < 1000 else \ f"{v:,.1f}".replace(",", "X").replace(".", ",").replace("X", ".") if isinstance(v, int) and not isinstance(v, bool): return f"{v:,}".replace(",", ".") s = str(v).replace("|", "¦").replace("\n", " ") return s if len(s) <= 140 else s[:137] + "…" def q(con, titulo, sql, maxf=400, csv=None): t = time.time() r = con.execute(sql) cols = [d[0] for d in r.description] filas = r.fetchall() md.append(f"\n## {titulo}\n\n_{len(filas)} filas · {time.time() - t:.1f} s_\n\n| " + " | ".join(cols) + " |\n|" + "|".join("---" for _ in cols) + "|\n") for f in filas[:maxf]: md.append("| " + " | ".join(fmt(v) for v in f) + " |\n") if len(filas) > maxf: md.append(f"\n… {len(filas) - maxf} filas más\n") if csv: con.execute(f"COPY ({sql}) TO '{(W / 'salidas' / csv).as_posix()}' (HEADER, DELIMITER ';')") print(f"{titulo}: {len(filas)} filas, {time.time() - t:.1f} s", file=sys.stderr, flush=True) return filas def guardar(): md.append(f"\n_Tiempo total: {time.time() - T0:.0f} s_\n") (W / "salidas" / "10_medir.md").write_text("".join(md), encoding="utf-8") db = W / "tmp" / "medir.duckdb" db.unlink(missing_ok=True) con = duckdb.connect(db.as_posix()) con.execute("SET memory_limit = '7GB'") con.execute("SET threads = 6") con.execute("SET temp_directory = '/w/tmp/duckdb_tmp'") con.execute("SET preserve_insertion_order = false") # ── 1. PLACSP: la población y las series M1r del job (mismo código) ───────────────────────────────────────────────── c = io.Calculo(con, U, D / "unified_20261018.identidad", D / "unified_20261018.vinculos", ANIO_FIN, satelites={}) assert c.preparar() c.organos_base() c.placsp_menores() c.poblacion() c.m1() sl.menores_placsp(c) sl.macros(con) con.execute("CREATE OR REPLACE TABLE p_men_placsp AS SELECT * FROM sl_men") # Las series del job publicadas (m1r_series, sin las omitidas) frente a las de aquí: mismas claves q(con, "1. Series M1r: las de este cálculo frente a las publicadas por el job (aggregations_20261018)", """ WITH a AS (SELECT DISTINCT serie_m1r FROM sl_men WHERE serie_m1r IS NOT NULL), a2 AS (SELECT serie_m1r FROM sl_men WHERE serie_m1r IS NOT NULL GROUP BY 1 HAVING NOT bool_or(fuera_lista)), j AS (SELECT serie_m1r FROM read_parquet('/d/aggregations_20261018/indicadores/m1r_series.parquet')) SELECT (SELECT count(*) FROM a) series_aqui, (SELECT count(*) FROM a2) series_aqui_en_lista, (SELECT count(*) FROM j) series_job, (SELECT count(*) FROM a2 WHERE serie_m1r NOT IN (SELECT serie_m1r FROM j)) aqui_no_job, (SELECT count(*) FROM j WHERE serie_m1r NOT IN (SELECT serie_m1r FROM a2)) job_no_aqui""") # ── 2. Demás fuentes (V2): base, criterios por fuente y año, población ───────────────────────────────────────────── con.execute("""CREATE OR REPLACE TABLE p_categorias AS SELECT DISTINCT source, objeto_base FROM read_csv('/w/datos/c2_objetos_categoria_main.csv', header = true, all_varchar = true) WHERE poblacion = 'menores' UNION SELECT DISTINCT source, objeto_base FROM read_csv('/w/datos/categorias_p9_20261020.csv', header = true, all_varchar = true) WHERE clase = 'categoria'""") c.tabla("v2b", f""" SELECT u.source AS fuente, u.id_canonical, u.fecha_adj, u.anio_precision, u.anio_ref, u.expediente, u.objeto, u.tipo_contrato, u.iva_base, u.imp_adj_eur_comparable, u.imp_adj_eur_corregido, u.cpv_code, u.url_origen, u.anulado_motivo, v.cuenta, a.persona_fisica, a.empresa_clave, a.varias_empresas, o.organo_clave, g.entidad_clave, coalesce(t.tipo, 'sin_tipo') AS tipo, {io.NORM_OBJETO.format(x=io.OBJETO_LOW.format(t='u'))} AS objeto_norm, {io.DA9.format(x=io.OBJETO_LOW.format(t='u'))} AS da9, CASE WHEN u.anio_precision = 'exacto' AND u.fecha_adj IS NOT NULL THEN year(u.fecha_adj) ELSE u.anio_ref END AS anio FROM u LEFT JOIN vf v ON v.source = u.source AND v.id_canonical = u.id_canonical LEFT JOIN id_adjudicatarios_filas a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM u.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM u.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM u.adjudicatario_nombre LEFT JOIN id_organos_filas o ON o.organo_nif IS NOT DISTINCT FROM u.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND o.dir3 IS NOT DISTINCT FROM u.dir3 LEFT JOIN id_organos g ON g.organo_clave = o.organo_clave LEFT JOIN otipo t ON t.organo_clave = o.organo_clave WHERE u.source NOT IN ('PLACSP', 'TED', 'CATALUNYA_MENORS', 'CATALUNYA_COVID') AND u.es_menor AND u.unidad = 'adjudicacion' AND u.copia_de IS NULL AND u.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND NOT coalesce(u.es_menor_dudoso, FALSE) AND NOT coalesce(u.anulado_en_origen, FALSE)""") c.tabla("v2c", """ SELECT b.*, (length(trim(coalesce(b.objeto, ''))) >= 20 AND k.objeto_base IS NULL) AS objeto_info, (b.anio_precision = 'exacto' AND b.fecha_adj IS NOT NULL) AS fecha_ok FROM v2b b LEFT JOIN p_categorias k ON k.source = b.fuente AND k.objeto_base = b.objeto_norm WHERE b.anio BETWEEN 2019 AND 2025 AND NOT b.da9 AND b.organo_clave IS NOT NULL AND b.persona_fisica IS DISTINCT FROM TRUE""") con.execute("""CREATE OR REPLACE TABLE p_fuente_anio AS SELECT fuente, anio, count(*) AS n, round(100.0 * avg(fecha_ok::INT), 2) AS pct_fecha, round(100.0 * avg(objeto_info::INT), 2) AS pct_objeto, round(100.0 * avg((empresa_clave IS NOT NULL)::INT), 2) AS pct_empresa, round(100.0 * avg((cuenta IS NOT DISTINCT FROM FALSE)::INT), 2) AS pct_cuenta_otra, (avg(fecha_ok::INT) >= 0.9 AND avg(objeto_info::INT) >= 0.5 AND avg((empresa_clave IS NOT NULL)::INT) >= 0.6 AND avg((cuenta IS NOT DISTINCT FROM FALSE)::INT) < 0.5 AND NOT (fuente = 'CATALUNYA_PSCP' AND anio < 2023)) AS calculable FROM v2c GROUP BY 1, 2""") q(con, "2. Criterios por fuente y año (V2; DEFINICIONES_PREVIAS §3.3)", "SELECT * FROM p_fuente_anio ORDER BY fuente, anio", csv="10_fuente_anio.csv") c.tabla("v2d", """ WITH f AS ( SELECT c.*, CASE WHEN c.iva_base IN ('sin', 'sin_medido') THEN c.imp_adj_eur_comparable ELSE c.imp_adj_eur_corregido / 1.21 END AS base_d, CASE WHEN c.tipo_contrato = 'Obras' THEN 'obras' WHEN c.tipo_contrato IS NULL OR c.tipo_contrato = 'Otro' THEN 'desconocido' ELSE 'resto' END AS clase FROM v2c c JOIN p_fuente_anio fa ON fa.fuente = c.fuente AND fa.anio = c.anio AND fa.calculable WHERE c.fecha_ok AND c.objeto_info AND c.persona_fisica = FALSE AND c.empresa_clave IS NOT NULL AND c.varias_empresas IS DISTINCT FROM TRUE), g AS (SELECT *, base_d::DECIMAL(18, 2) AS base, CASE WHEN clase IN ('obras', 'desconocido') THEN 40000 WHEN tipo = 'investigacion' THEN 50000 ELSE 15000 END AS umbral FROM f WHERE base_d > 0), d AS (SELECT *, row_number() OVER (PARTITION BY fuente, organo_clave, empresa_clave, base, fecha_adj, objeto_norm, coalesce(expediente, '') ORDER BY id_canonical) > 1 AS dup FROM g) SELECT * EXCLUDE (dup, base_d) FROM d WHERE NOT dup""") # M1r en cada fuente con la lógica del job (ventanas de 365 días), con la fuente en la partición c.tabla("v2_m1r", f""" WITH w AS ( SELECT id_canonical, fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base) OVER v AS s12 FROM v2d WHERE length(objeto_norm) >= {sl.OBJETO_MIN} WINDOW v AS (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w), m AS (SELECT *, bool_or(activa) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1r FROM a), s AS (SELECT *, lag(fecha_adj) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical) AS prev FROM m WHERE en_m1r), k AS (SELECT *, sum(CASE WHEN prev IS NULL OR fecha_adj - prev > 364 THEN 1 ELSE 0 END) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical ROWS UNBOUNDED PRECEDING) AS k FROM s) SELECT id_canonical, md5(fuente || '|' || organo_clave || '|' || empresa_clave || '|' || clase || '|' || objeto_norm || '|' || k::VARCHAR) AS serie_m1r FROM k""") # Control de la lógica genérica: aplicada a la población PLACSP del job, ¿da las mismas series que el job? c.tabla("chk_m1r", f""" WITH b AS (SELECT 'PLACSP' AS fuente, * FROM pob), w AS (SELECT id_canonical, fuente, organo_clave, empresa_clave, clase, objeto_norm, fecha_adj, base, umbral, count(*) OVER v AS n12, sum(base) OVER v AS s12 FROM b WHERE length(objeto_norm) >= 5 WINDOW v AS (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), a AS (SELECT *, (n12 >= 2 AND s12 > umbral) AS activa FROM w), m AS (SELECT *, bool_or(activa) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) AS en_m1r FROM a), s AS (SELECT *, lag(fecha_adj) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical) AS prev FROM m WHERE en_m1r), k AS (SELECT *, sum(CASE WHEN prev IS NULL OR fecha_adj - prev > 364 THEN 1 ELSE 0 END) OVER (PARTITION BY fuente, organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj, id_canonical ROWS UNBOUNDED PRECEDING) AS k FROM s) SELECT id_canonical, md5(organo_clave || '|' || empresa_clave || '|' || clase || '|' || objeto_norm || '|' || k::VARCHAR) AS serie_m1r FROM k""") q(con, "3. La lógica de M1r de las demás fuentes, aplicada a la PLACSP, frente a la del job (contrato a contrato)", """ SELECT (SELECT count(*) FROM chk_m1r) contratos_generica, (SELECT count(*) FROM m1r) contratos_job, (SELECT count(*) FROM chk_m1r g FULL JOIN m1r j USING (id_canonical) WHERE g.serie_m1r IS DISTINCT FROM j.serie_m1r) distintos""") con.execute("""CREATE OR REPLACE TABLE p_men_v2 AS SELECT d.fuente, d.id_canonical, d.organo_clave, d.entidad_clave, d.empresa_clave, d.clase, d.umbral, d.objeto_norm, d.fecha_adj, d.base, CASE WHEN regexp_matches(coalesce(d.cpv_code, ''), '^[0-9]{8}$') AND d.cpv_code <> '00000000' THEN d.cpv_code END AS cpv, r.serie_m1r, coalesce(d.anulado_motivo = 'sin_motivo', FALSE) AS fuera_lista FROM v2d d LEFT JOIN v2_m1r r USING (id_canonical)""") q(con, "4. Población de series por fuente (filas de los años calculables)", """ SELECT fuente, count(*) menores, count(DISTINCT organo_clave) organos, count(serie_m1r) en_m1r, count(DISTINCT serie_m1r) series, round(100.0 * count(cpv) / count(*), 1) pct_con_cpv, min(year(fecha_adj)) anio_min, max(year(fecha_adj)) anio_max FROM (SELECT * FROM p_men_placsp UNION ALL SELECT * FROM p_men_v2) GROUP BY 1 ORDER BY menores DESC""") guardar() # ── 3. Etiqueta principal sobre todas las fuentes ───────────────────────────────────────────────────────────────── con.execute("CREATE OR REPLACE TEMP TABLE sl_men AS SELECT * FROM p_men_placsp UNION ALL SELECT * FROM p_men_v2") e = sl.Etiquetado(con, c.cols, ANIO_FIN) e.unidades() e.licitaciones(anio_ini=2019, anio_fin=2026) e.etiquetar() e.salidas(sl.seudonimos(con)) for t in ("sl_u", "sl_lic", "sl_par", "sl_et", "sl_series", "sl_hechos", "sl_oa", "sl_lift"): con.execute(f"CREATE OR REPLACE TABLE p_{t[3:]} AS SELECT * FROM {t}") q(con, "5. Lift por fuente y año (etiqueta principal E0)", """ 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 ORDER BY fuente, anio NULLS LAST""", csv="10_lift_e0.csv") q(con, "6. Licitaciones (§4.1) por fuente de la licitación y año", """ SELECT fuente_lic, count(DISTINCT licitacion) licitaciones, count(*) filas, count(DISTINCT organo_clave) organos, round(100.0 * count(cpv) / count(*), 1) pct_cpv FROM p_lic GROUP BY 1 ORDER BY 2 DESC""") q(con, "7. Regla de respaldo E4: % de series cuyo órgano tiene alguna licitación (cualquier año)", """ WITH o AS (SELECT DISTINCT organo_clave FROM p_lic) SELECT u.fuente, count(*) series, round(100.0 * count(o.organo_clave) / count(*), 1) pct_organo_con_licitaciones FROM p_u u LEFT JOIN o USING (organo_clave) WHERE u.tipo_unidad = 'serie' GROUP BY 1 ORDER BY 2 DESC""") guardar() # ── 4. Variantes ─────────────────────────────────────────────────────────────────────────────────────────────── # Parejas amplias (JW ≥ 0,85 o tokens ≥ 60 %) del mismo órgano en t + 1 con los CPV a 4 y 5 dígitos: E0, E1 y E2 salen de aquí c.tabla("lic_o45", """ SELECT organo_clave, anio_lic, objeto_norm, list_sort(list_distinct(list(substr(cpv, 1, 5)) FILTER (WHERE cpv IS NOT NULL))) cpv5, list_sort(list_distinct(list(substr(cpv, 1, 4)) FILTER (WHERE cpv IS NOT NULL))) cpv4, em2_tokens(objeto_norm) tokens FROM p_lic GROUP BY 1, 2, 3""") c.tabla("u45", """ SELECT u.*, list_sort(list_distinct(list_transform(u.cpvs, x -> substr(x, 1, 4)))) cpv4 FROM p_u u""") c.tabla("par45", f""" WITH s AS (SELECT DISTINCT organo_clave, anio, objeto_norm, tokens FROM u45), p AS (SELECT s.organo_clave, s.anio, s.objeto_norm obj_s, l.objeto_norm obj_l, l.cpv5, l.cpv4, jaro_winkler_similarity(s.objeto_norm, l.objeto_norm) jw, CASE WHEN len(s.tokens) >= 2 THEN len(list_intersect(s.tokens, l.tokens))::DOUBLE / len(s.tokens) END tp FROM s JOIN lic_o45 l ON l.organo_clave = s.organo_clave AND l.anio_lic = s.anio + 1) SELECT u.tipo_unidad, u.unidad, p.obj_l, p.jw, p.tp, len(list_intersect(u.cpvs, p.cpv5)) > 0 c5, len(list_intersect(u.cpv4, p.cpv4)) > 0 c4 FROM p JOIN u45 u ON u.organo_clave = p.organo_clave AND u.anio = p.anio AND u.objeto_norm = p.obj_s WHERE p.jw >= 0.85 OR coalesce(p.tp >= 0.6, FALSE)""") c.tabla("var_et", """ SELECT u.tipo_unidad, u.unidad, u.fuente, u.anio, u.organo_clave, coalesce(bool_or((p.jw >= 0.85 AND p.c5) OR p.tp >= 0.6), FALSE) e0, coalesce(bool_or(p.jw >= 0.85), FALSE) e1, coalesce(bool_or((p.jw >= 0.85 AND p.c4) OR p.tp >= 0.6), FALSE) e2 FROM p_u u LEFT JOIN par45 p USING (tipo_unidad, unidad) GROUP BY ALL""") q(con, "8. Control interno: la E0 de las parejas amplias = la del módulo", """ SELECT count(*) unidades, count(*) FILTER (WHERE v.e0 IS DISTINCT FROM e.pasa) distintas FROM var_et v JOIN p_et e USING (tipo_unidad, unidad)""") # E4: misma entidad e.objetos(clave="entidad_clave", sufijo="_e") e.etiquetar(clave="entidad_clave", sufijo="_e") # E5: año de la licitación = año de fecha_pub (sin ella, anio_ref) c.tabla("lic_o5", f""" SELECT organo_clave, coalesce(year(fecha_pub), anio_lic) AS anio_lic, objeto_norm, list_sort(list_distinct(list(substr(cpv, 1, 5)) FILTER (WHERE cpv IS NOT NULL))) AS cpvs, em2_tokens(objeto_norm) AS tokens FROM p_lic GROUP BY 1, 2, 3""") con.execute("CREATE OR REPLACE TEMP TABLE sl_lic_o_f AS SELECT * FROM lic_o5") e.etiquetar(clave="organo_clave", sufijo="_f") # E3: ventana de 365 días tras el último contrato (fecha de publicación o, sin ella, de adjudicación). Los pares de objetos se # calculan una vez por (órgano, año de la serie, objeto) frente a los objetos de las licitaciones de ese año y del siguiente; # la fecha se mira después, unidad a unidad, sobre la lista de fechas del objeto c.tabla("lic_o3", """ SELECT organo_clave, year(coalesce(fecha_pub, fecha_adj)) AS anio_f, objeto_norm, list(DISTINCT coalesce(fecha_pub, fecha_adj)) AS fechas, list_sort(list_distinct(list(substr(cpv, 1, 5)) FILTER (WHERE cpv IS NOT NULL))) AS cpvs, em2_tokens(objeto_norm) AS tokens FROM p_lic WHERE coalesce(fecha_pub, fecha_adj) IS NOT NULL GROUP BY 1, 2, 3""") c.tabla("par3", """ WITH s AS (SELECT DISTINCT organo_clave, anio, objeto_norm, tokens FROM p_u) SELECT s.organo_clave, s.anio, s.objeto_norm obj_s, l.objeto_norm obj_l, l.fechas, l.cpvs, jaro_winkler_similarity(s.objeto_norm, l.objeto_norm) jw, CASE WHEN len(s.tokens) >= 2 THEN len(list_intersect(s.tokens, l.tokens))::DOUBLE / len(s.tokens) END tp FROM s JOIN lic_o3 l ON l.organo_clave = s.organo_clave AND l.anio_f IN (s.anio, s.anio + 1) WHERE jaro_winkler_similarity(s.objeto_norm, l.objeto_norm) >= 0.85 OR (len(s.tokens) >= 2 AND len(list_intersect(s.tokens, l.tokens))::DOUBLE / len(s.tokens) >= 0.6)""") c.tabla("et3", """ SELECT u.tipo_unidad, u.unidad, bool_or(((p.jw >= 0.85 AND len(list_intersect(u.cpvs, p.cpvs)) > 0) OR coalesce(p.tp >= 0.6, FALSE)) AND len(list_filter(p.fechas, d -> d > u.fecha_fin AND d <= u.fecha_fin + 365)) > 0) e3 FROM p_u u JOIN par3 p ON p.organo_clave = u.organo_clave AND p.anio = u.anio AND p.obj_s = u.objeto_norm GROUP BY 1, 2""") c.tabla("var_all", """ SELECT v.*, coalesce(e4.pasa, FALSE) e4, coalesce(e5.pasa, FALSE) e5, coalesce(e3.e3, FALSE) e3 FROM var_et v LEFT JOIN sl_et_e e4 USING (tipo_unidad, unidad) LEFT JOIN sl_et_f e5 USING (tipo_unidad, unidad) LEFT JOIN et3 e3 USING (tipo_unidad, unidad)""") con.execute("CREATE OR REPLACE TABLE p_var AS SELECT * FROM var_all") lift_var = """ WITH g AS (SELECT fuente, anio, {e} AS y, tipo_unidad FROM p_var), a AS (SELECT fuente, {anio} AS anio, count(*) FILTER (WHERE tipo_unidad = 'serie') s, count(*) FILTER (WHERE tipo_unidad = 'serie' AND y) sp, count(*) FILTER (WHERE tipo_unidad = 'control') c, count(*) FILTER (WHERE tipo_unidad = 'control' AND y) cp FROM g GROUP BY ALL) SELECT fuente, anio, s series, sp, round(100.0 * sp / s, 2) tasa_s, c control, cp, round(100.0 * cp / nullif(c, 0), 2) tasa_c, round((sp::DOUBLE / s) / nullif(cp::DOUBLE / nullif(c, 0), 0), 3) lift FROM a ORDER BY fuente, anio""" for var, nombre in (("e1", "E1 (solo JW ≥ 0,85, como Fable)"), ("e2", "E2 (CPV a 4 dígitos)"), ("e3", "E3 (ventana de 365 días por fecha)"), ("e4", "E4 (misma entidad)"), ("e5", "E5 (año de publicación)")): q(con, f"9. Lift agrupado 2019-2024 por fuente · {nombre}", lift_var.format(e=var, anio="'2019-2024'"), csv=f"10_lift_{var}.csv") q(con, "9b. Lift por fuente y año · E4 (misma entidad)", lift_var.format(e="e4", anio="anio"), csv="10_lift_e4_anio.csv") guardar() # Mantel-Haenszel por órgano-año (E0), solo informativo q(con, "10. Lift de Mantel-Haenszel por órgano-año (E0; compara series y cadenas del mismo órgano y año)", """ 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 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""") # Control de Fable (PLACSP): pares (órgano, empresa, clase, objeto, año natural) con ≥ 2 menores, suma > umbral o ≤ umbral c.tabla("fab", """ WITH b AS (SELECT organo_clave, empresa_clave, clase, objeto_norm, year(fecha_adj) anio, count(*) n, sum(base) suma, any_value(umbral) umbral, list_sort(list_distinct(list(substr(cpv, 1, 5)) FILTER (WHERE cpv IS NOT NULL))) cpvs FROM p_men_placsp WHERE length(objeto_norm) >= 5 GROUP BY 1, 2, 3, 4, 5 HAVING count(*) >= 2), o AS (SELECT DISTINCT organo_clave, objeto_norm, anio, em2_tokens(objeto_norm) tokens FROM b WHERE anio <= 2024), p AS (SELECT o.organo_clave, o.objeto_norm, o.anio, max(jaro_winkler_similarity(o.objeto_norm, l.objeto_norm)) jw FROM o JOIN lic_o45 l ON l.organo_clave = o.organo_clave AND l.anio_lic = o.anio + 1 GROUP BY 1, 2, 3) SELECT b.*, coalesce(p.jw >= 0.85, FALSE) pasa_jw FROM b LEFT JOIN p USING (organo_clave, objeto_norm, anio) WHERE b.anio <= 2024""") q(con, "11. Control de Fable (PLACSP, etiqueta E1): pares año con ≥ 2 menores, por encima o no del umbral", """ SELECT suma > umbral supera, count(*) pares, round(100.0 * avg(pasa_jw::INT), 2) pct_pasa_jw FROM fab GROUP BY 1 ORDER BY 1""") q(con, "11b. Series M1r del job con fin ≤ 2024 (las publicadas, como Fable) con la etiqueta E1", """ WITH j AS (SELECT serie_m1r FROM read_parquet('/d/aggregations_20261018/indicadores/m1r_series.parquet') WHERE anio_fin <= 2024) SELECT count(*) series, count(*) FILTER (WHERE v.e1) pasa_e1, round(100.0 * count(*) FILTER (WHERE v.e1) / count(*), 2) pct_e1, count(*) FILTER (WHERE v.e0) pasa_e0, round(100.0 * count(*) FILTER (WHERE v.e0) / count(*), 2) pct_e0 FROM j JOIN p_var v ON v.unidad = j.serie_m1r AND v.tipo_unidad = 'serie'""") guardar() # ── 5. Qué hay en las licitaciones casadas ───────────────────────────────────────────────────────────────────── q(con, "12. Hechos (series de la lista que pasaron): por fuente de la serie, procedimiento y rama", """ SELECT s.fuente, h.procedimiento, h.basado_en, count(*) parejas, count(DISTINCT h.serie_m1r) series FROM p_hechos h JOIN p_series s USING (serie_m1r) GROUP BY ALL ORDER BY 1, 4 DESC""", maxf=80) q(con, "12b. Rama de la etiqueta en las series que pasaron, por fuente", """ SELECT fuente, rama, count(*) series FROM p_series WHERE paso_a_licitacion GROUP BY ALL ORDER BY 1, 3 DESC""") q(con, "12c. Fuente de la licitación casada, por fuente de la serie", """ SELECT s.fuente, h.fuente_licitacion, count(*) parejas FROM p_hechos h JOIN p_series s USING (serie_m1r) GROUP BY ALL ORDER BY 1, 3 DESC""") q(con, "12d. Licitaciones casadas por serie (series que pasaron)", """ SELECT fuente, quantile_cont(objetos_casados, 0.5) mediana_objetos, max(objetos_casados) max_objetos, count(*) FILTER (WHERE objetos_casados >= 10) con_10_o_mas FROM p_series WHERE paso_a_licitacion GROUP BY 1""") q(con, "13. Por tipo de órgano (todas las fuentes, E0)", """ SELECT coalesce(t.tipo, 'sin_tipo') tipo, u.tipo_unidad, count(*) n, round(100.0 * avg(e.pasa::INT), 2) pct_pasa FROM p_u u JOIN p_et e USING (tipo_unidad, unidad) LEFT JOIN otipo t USING (organo_clave) GROUP BY ALL ORDER BY 1, 2""") q(con, "13b. Por tramo de suma de la serie (PLACSP, E0)", """ SELECT u.tipo_unidad, CASE WHEN u.suma <= 15000 THEN 'a ≤15k' WHEN u.suma <= 30000 THEN 'b 15-30k' WHEN u.suma <= 60000 THEN 'c 30-60k' ELSE 'd >60k' END tramo, count(*) n, round(100.0 * avg(e.pasa::INT), 2) pct_pasa FROM p_u u JOIN p_et e USING (tipo_unidad, unidad) WHERE u.fuente = 'PLACSP' GROUP BY ALL ORDER BY 1, 2""") # Muestras de los cotejos (semillas fijas): 50 parejas automáticas y 20 fichas de origen c.tabla("pos", """ SELECT h.*, s.fuente, s.objeto_norm AS obj_serie, s.clase, s.n, s.suma, s.fecha_ini, s.fecha_fin FROM p_hechos h JOIN p_series s USING (serie_m1r)""") con.execute("CREATE OR REPLACE TABLE p_pos AS SELECT * FROM pos") guardar() print("hecho", round(time.time() - T0), file=sys.stderr)