"""DAT-CRUCE · H-CNV · §2-§3: contratos 2024-2026 de órganos de cada ministerio (DIR3 principal o NIF de la raíz) y K de N por ministerio. Salidas: /dc/interno/cnv_*.parquet (700), /dc/salidas/cnv_hecho.json (solo recuentos).""" import duckdb, json, sys sys.path.insert(0, '/code') from etl.v2.identidad import MACROS_IDENTIDAD B = '20261103'; U = f'/v2/unified_{B}'; I = f'{U}.identidad/' con = duckdb.connect(); x = con.execute; x(MACROS_IDENTIDAD) x("SET threads=2; SET memory_limit='1400MB'; SET temp_directory='/dc/tmp/duck'; SET max_temp_directory_size='20GB'; SET preserve_insertion_order=false") R = {'construccion': f'unified_{B}', 'prereg_md5': 'd4ff0a6fe22eb314a6dc6a33d973f8b3'} x(f"CREATE TABLE d3 AS SELECT codigo, principal FROM '{I}dir3_unidades.parquet' WHERE listado = 'AGE'") x("CREATE TABLE raiz AS SELECT * FROM '/dc/interno/cnv_raiz.parquet'") x("CREATE TABLE nifraiz AS SELECT nif, min(codigo) codigo FROM raiz WHERE nif IS NOT NULL GROUP BY 1 HAVING count(DISTINCT codigo) = 1") x(f"CREATE TABLE nocuenta AS SELECT DISTINCT id_canonical FROM '{U}.vinculos/filas.parquet' WHERE NOT cuenta") x(f"""CREATE TABLE u AS SELECT anio_ref anio, organo_nif, organo_nombre, dir3, adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre FROM read_parquet('{U}/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE unidad='adjudicacion' AND anio_ref BETWEEN 2024 AND 2026 AND copia_de IS NULL AND NOT coalesce(correccion_imp_adj='copia_otra_fuente', FALSE) AND NOT coalesce(anulado_en_origen, FALSE) AND id_canonical NOT IN (SELECT id_canonical FROM nocuenta)""") x(f"""CREATE TABLE cf AS SELECT u.anio, coalesce(u.organo_nif, of.organo_nif_inferido) onif, of.entidad_clave, coalesce(u.dir3, of.dir3_propagado) d, a.empresa_clave emp FROM u JOIN '{I}organos_filas.parquet' of ON of.organo_nif IS NOT DISTINCT FROM u.organo_nif AND of.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND of.dir3 IS NOT DISTINCT FROM u.dir3 LEFT JOIN '{I}adjudicatarios_filas.parquet' 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 AND a.empresa_tipo = 'empresa' AND a.empresa_via = 'nif'""") x("DROP TABLE u") x("""CREATE TABLE cm AS SELECT cf.*, coalesce(d3.principal, nr.codigo) ministerio, CASE WHEN d3.principal IS NOT NULL THEN 'dir3' WHEN nr.codigo IS NOT NULL THEN 'nif_raiz' END via FROM cf LEFT JOIN d3 ON d3.codigo = cf.d LEFT JOIN nifraiz nr ON nr.nif = cf.onif""") x("DELETE FROM cm WHERE ministerio NOT IN (SELECT codigo FROM raiz)") R['contratos_de_ministerios'] = x("SELECT via, count(*), count(DISTINCT ministerio) FROM cm GROUP BY 1 ORDER BY 1").fetchall() x("CREATE TABLE men AS SELECT DISTINCT nif emp, min_codigo ministerio FROM '/dc/interno/cnv_menciones.parquet' WHERE privada AND min_codigo IS NOT NULL") x("CREATE TABLE adj AS SELECT DISTINCT emp, ministerio FROM cm WHERE emp IS NOT NULL") x("""CREATE TABLE hecho AS SELECT m.ministerio, r.nombre, count(*) n, count(a.emp) k FROM men m JOIN raiz r ON r.codigo = m.ministerio LEFT JOIN adj a USING (emp, ministerio) GROUP BY 1, 2 ORDER BY 1""") R['hecho'] = x("SELECT * FROM hecho").fetchall() R['total'] = x("SELECT sum(n), sum(k) FROM hecho").fetchone() # entidad (NIF de la ficha) → ministerio si > 50 % de sus filas 2024-2026 son de un mismo ministerio x("""CREATE TABLE ent_min AS WITH t AS (SELECT onif, count(*) n FROM cf WHERE onif IS NOT NULL GROUP BY 1), m AS (SELECT onif, ministerio, count(*) n FROM cm WHERE onif IS NOT NULL GROUP BY 1, 2) SELECT m.onif nif, m.ministerio, m.n, t.n total FROM m JOIN t USING (onif) WHERE m.n > 0.5 * t.n""") R['entidades_con_ministerio'] = x("SELECT count(*), count(DISTINCT ministerio) FROM ent_min").fetchone() # estabilidad: convenios de 2024 frente a los de 2025-2026 x("CREATE TABLE men_b AS SELECT DISTINCT nif emp, min_codigo ministerio, CASE WHEN dia < '2025' THEN 1 ELSE 2 END blq FROM '/dc/interno/cnv_menciones.parquet' WHERE privada AND min_codigo IS NOT NULL") R['estabilidad'] = x("""SELECT blq, count(*) n, count(a.emp) k FROM men_b m LEFT JOIN adj a USING (emp, ministerio) GROUP BY 1 ORDER BY 1""").fetchall() for t in ('hecho', 'ent_min', 'men', 'adj'): x(f"COPY (SELECT * FROM {t} ORDER BY ALL) TO '/dc/interno/cnv_{t}.parquet' (FORMAT parquet)") # insumos de la segunda implementación: filas de C con NIF de empresa, NIF y DIR3 de órgano x("COPY (SELECT onif, d, emp FROM cf WHERE emp IS NOT NULL) TO '/dc/interno/cnv_c_filas.parquet' (FORMAT parquet)") json.dump(R, open('/dc/salidas/cnv_hecho.json', 'w'), ensure_ascii=False, indent=1, default=str); print(json.dumps(R, ensure_ascii=False, indent=1, default=str))