"""MOD-EJEC · 40 · implementación 1 del PREREG_2 (md5 de3e22b1…): «modificaciones por más del 20 % del precio» por contrato y por órgano × fuente (PLACSP por licitación, del universo de EJ1; RPC, PSCP y Madrid por contrato), cohortes 2018-2024. -> priv/sob_contratos.parquet (id, fuente, órgano, año, A, Σ, nmod, y), priv/sob_organos.parquet (órgano × fuente: n, k, por bloque), salidas/40_sobrecoste.json (cifras por fuente y tipo, potencia, estabilidad declarada). Sin red.""" import duckdb, json, math, time T0 = time.time() c = duckdb.connect(); x = c.execute x("SET threads=3; SET memory_limit='1500MB'; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='6GB'; SET preserve_insertion_order=false") D = '/d/unified_20261103' def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) def q(s): return x(s).fetchall() PF = r"^([0-9]{8}[A-Z]|[XYZKLM][0-9]{7}[A-Z])\|" x(f"CREATE TABLE ot AS SELECT organo_clave, tipo, coalesce(nif_persona_fisica, FALSE) pf FROM '{D}.identidad/organos_tipo.parquet'") x(f"CREATE TABLE ofi AS SELECT organo_nif, organo_nombre, dir3, organo_clave FROM '{D}.identidad/organos_filas.parquet'") # --- PLACSP: el universo de EJ1 por licitación (ej1/interno/lic.parquet, de ej1/py/10_base.py con unified_20261103) x("""CREATE TABLE c_pl AS SELECT 'PLACSP' fuente, licitacion_id id, organo_clave, anio::INT anio, A, sM s_mod, nM nmod FROM '/w/ej1/interno/lic.parquet' WHERE anio BETWEEN 2018 AND 2024 AND A > 0""") # --- RPC, PSCP y Madrid: por contrato def univ(f, tabla, col_a, extra=''): x(f"""CREATE TABLE u_{f} AS SELECT u.id_canonical id, u.organo_nif, u.organo_nombre, u.dir3, year(u.fecha_adj)::INT anio, u.{col_a} A FROM read_parquet('{D}/source={f}/**/*.parquet', hive_partitioning=1, union_by_name=1) u WHERE u.tabla_origen = '{tabla}' AND u.unidad = 'adjudicacion' AND u.es_contrato AND u.vigente AND NOT coalesce(u.es_menor, FALSE) AND u.copia_de IS NULL AND coalesce(u.correccion_imp_adj, '') <> 'copia_otra_fuente' AND u.rol_am IS NULL AND coalesce(u.tramitacion, '') <> 'emergencia' {extra} AND year(u.fecha_adj) BETWEEN 2018 AND 2024 AND u.{col_a} > 0""") univ('CATALUNYA', 'contratos_registro', 'imp_adj_eur', "AND coalesce(u.procedimiento_native, '') <> 'Contractes d''emergències'") univ('CATALUNYA_PSCP', 'publicaciones_pscp', 'imp_adj_eur_sin_iva') univ('MADRID_AYTO', 'actividad_contractual_madrid_completo', 'imp_adj_eur') x(f"""CREATE TABLE m3 AS SELECT DISTINCT fuente, id_canonical, fecha, importe_cambio FROM '{D}.cambios/cambios.parquet' WHERE fuente IN ('CATALUNYA', 'CATALUNYA_PSCP', 'MADRID_AYTO') AND tipo = 'modificacion' AND coalesce(importe_cambio, 0) <> 0 AND id_canonical IS NOT NULL""") for f, nom in (('CATALUNYA', 'RPC'), ('CATALUNYA_PSCP', 'PSCP'), ('MADRID_AYTO', 'MADRID')): x(f"""CREATE TABLE c_{nom} AS SELECT '{nom}' fuente, u.id, o.organo_clave, u.anio, u.A, coalesce(sum(m.importe_cambio), 0) s_mod, count(m.id_canonical) nmod FROM u_{f} u LEFT JOIN ofi 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 m3 m ON m.fuente = '{f}' AND m.id_canonical = u.id GROUP BY ALL""") x("CREATE TABLE cc0 AS SELECT * FROM c_pl UNION ALL SELECT * FROM c_RPC UNION ALL SELECT * FROM c_PSCP UNION ALL SELECT * FROM c_MADRID") assert q("SELECT count(*) = count(DISTINCT fuente || '¦' || id) FROM cc0")[0][0], 'id repetido' R = {'prerregistro': 'de3e22b12fae39ffc63756d008b892a1', 'construccion': 'unified_20261103', 'commit': 'd1daf83', 'sin_organo': q("SELECT fuente, count(*) FILTER (WHERE organo_clave IS NULL), count(*) FROM cc0 GROUP BY 1 ORDER BY 1")} x(f"""CREATE TABLE cc AS SELECT cc0.*, coalesce(ot.tipo, 'sin_tipo') tipo_organo, (s_mod - 0.20 * A > 0.01) y, s_mod / A s FROM cc0 LEFT JOIN ot USING (organo_clave) WHERE organo_clave IS NOT NULL AND NOT coalesce(ot.pf, FALSE) AND NOT regexp_matches(organo_clave, '{PF}')""") R['contratos'] = q("""SELECT fuente, count(*) n, sum(y::INT) k, round(avg(y::INT), 5) tasa, sum((nmod > 0)::INT) modificados, round(avg(y::INT) FILTER (WHERE nmod > 0), 4) tasa_entre_modificados FROM cc GROUP BY 1 ORDER BY 1""") R['por_anio'] = q("SELECT fuente, anio, count(*), sum(y::INT) FROM cc GROUP BY 1, 2 ORDER BY 1, 2") R['reparto_s_modificados'] = q("""SELECT fuente, round(median(s), 4), round(quantile_cont(s, 0.9), 4), round(avg((s > 0.5)::INT), 4) FROM cc WHERE nmod > 0 GROUP BY 1 ORDER BY 1""") x("""CREATE TABLE org AS SELECT fuente, organo_clave, any_value(tipo_organo) tipo_organo, count(*) n, sum(y::INT) k, count(*) FILTER (WHERE anio <= 2021) n1, sum(y::INT) FILTER (WHERE anio <= 2021) k1, count(*) FILTER (WHERE anio >= 2022) n2, sum(y::INT) FILTER (WHERE anio >= 2022) k2 FROM cc GROUP BY 1, 2""") assert q("SELECT count(DISTINCT tipo_organo) = 1 FROM (SELECT organo_clave, tipo_organo FROM cc GROUP BY 1, 2) GROUP BY organo_clave ORDER BY 1 LIMIT 1")[0][0] x("""CREATE TABLE tipo AS SELECT fuente, tipo_organo, sum(n) n, sum(k) k, count(*) FILTER (WHERE n >= 30) org30, count(*) FILTER (WHERE n >= 30 AND k > 0) org30_con, sum(n1) n1, sum(k1) k1, sum(n2) n2, sum(k2) k2 FROM org GROUP BY 1, 2""") R['potencia'] = q("SELECT fuente, count(*) FILTER (WHERE n >= 30), count(*) FILTER (WHERE n >= 30 AND k > 0), sum(n) FILTER (WHERE n >= 30), sum(k) FILTER (WHERE n >= 30) FROM org GROUP BY 1 ORDER BY 1") R['por_tipo'] = q("SELECT fuente, tipo_organo, n, k, round(k / n, 5), org30, org30_con FROM tipo ORDER BY 1, 3 DESC") # (d) estabilidad declarada: Spearman (rangos medios) y OR de «por encima de la tasa de su tipo» entre 2018-2021 y 2022-2024 x("""CREATE TABLE est AS SELECT o.fuente, o.organo_clave, o.k1 / o.n1 t1, o.k2 / o.n2 t2, (o.k1 / o.n1 > t.k1 / t.n1) e1, (o.k2 / o.n2 > t.k2 / t.n2) e2 FROM org o JOIN tipo t USING (fuente, tipo_organo) WHERE o.n1 >= 30 AND o.n2 >= 30""") x("""CREATE TABLE estr AS SELECT fuente, t1, t2, e1, e2, rank() OVER (PARTITION BY fuente ORDER BY t1) + (count(*) OVER (PARTITION BY fuente, t1) - 1) / 2.0 r1, rank() OVER (PARTITION BY fuente ORDER BY t2) + (count(*) OVER (PARTITION BY fuente, t2) - 1) / 2.0 r2 FROM est""") E = {} for f, n, rho, a, b, cc_, d in q("""SELECT fuente, count(*), corr(r1, r2), count(*) FILTER (WHERE e1 AND e2), count(*) FILTER (WHERE e1 AND NOT e2), count(*) FILTER (WHERE NOT e1 AND e2), count(*) FILTER (WHERE NOT e1 AND NOT e2) FROM estr GROUP BY 1 ORDER BY 1"""): ha = [v + 0.5 for v in (a, b, cc_, d)] if min(a, b, cc_, d) == 0 else [a, b, cc_, d] OR = ha[0] * ha[3] / (ha[1] * ha[2]); se = math.sqrt(sum(1 / v for v in ha)) E[f] = {'organos': n, 'spearman': rho, 'tabla': [a, b, cc_, d], 'OR': OR, 'ic95': [math.exp(math.log(OR) - 1.959964 * se), math.exp(math.log(OR) + 1.959964 * se)]} R['estabilidad_declarada'] = E x("COPY (SELECT * FROM cc) TO '/w/priv/sob_contratos.parquet' (FORMAT PARQUET)") x("COPY (SELECT * FROM org) TO '/w/priv/sob_organos.parquet' (FORMAT PARQUET)") x("COPY (SELECT * FROM tipo) TO '/w/salidas/40_tipo.csv' (HEADER)") json.dump(R, open('/w/salidas/40_sobrecoste.json', 'w'), ensure_ascii=False, indent=1, default=float) for k in ('sin_organo', 'contratos', 'potencia', 'estabilidad_declarada'): log(k, R[k]) log('fin')