"""SUB1 · 10 · base (DEFINICIONES_PREVIAS §1-4): identidad del ente (§3), contratos C (§1), subvenciones S (§2). Escribe interno/{concedente_ente,entidad_ente,c_filas,s_filas}.parquet y salidas/10_base.json.""" import duckdb, json, sys, time sys.path.insert(0,'/w/py') from comun_sql import MACROS, CCAA_DIR3 T0=time.time() c=duckdb.connect(); x=c.execute x("SET threads=4; SET memory_limit='6GB'; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='5GB'; SET preserve_insertion_order=false") for m in MACROS.strip().split(';\n'): if m.strip(): x(m) def log(*a): print(f"[{time.time()-T0:6.1f}s]",*a,flush=True) R='/id2/etl/salida/real'; I='/d/unified_20261018.identidad' NIFPJ = "'^[ABCDEFGHJNRUVW][0-9]{7}[0-9A-J]$'" NIFPUB = "'^[A-Z][0-9]{7}[0-9A-Z]$'" DNI = "'^([0-9]{8}[A-Z]|[XYZ][0-9]{7}[A-Z])$'" r={} # ── DIR3 y CCAA ── x("CREATE TABLE cc (nivel2 VARCHAR, pref VARCHAR)"); c.executemany("INSERT INTO cc VALUES (?,?)", list(CCAA_DIR3.items())) x(f"CREATE TABLE d3 AS SELECT codigo, nombre, listado, nif, nk(nombre) k FROM '{R}/dir3_unidades.parquet' WHERE nif IS NOT NULL AND nk(nombre)<>''") # ── §3.2 nuestra entidad -> ente ── x(f"CREATE TABLE ent AS SELECT entidad_clave, nombre, administracion, nk(nombre) k FROM '{I}/entidades.parquet'") ADM = [('Andaluc','A01'),('Arag','A02'),('Asturias','A03'),('Balears','A04'),('Canarias','A05'),('Cantabria','A06'),('Castilla y Le','A07'), ('Castilla-La Mancha','A08'),('Catalunya','A09'),('Valencian','A10'),('Extremadura','A11'),('Galicia','A12'),('Madrid','A13'),('Murcia','A14'), ('Navarra','A15'),('Vasco','A16'),('Euskadi','A16'),('Rioja','A17'),('Ceuta','A18'),('Melilla','A19')] case = "CASE WHEN administracion IS NULL THEN 'ANY' WHEN administracion='Administración local' THEN 'EELL' WHEN administracion='Administración General del Estado' THEN 'AGE' " + \ " ".join(f"WHEN administracion ILIKE '%{a}%' THEN '{p}'" for a,p in ADM) + " ELSE 'ANY' END" x(f"ALTER TABLE ent ADD COLUMN amb VARCHAR; UPDATE ent SET amb = {case}") r['administracion_sin_ambito']=x("SELECT administracion, count(*) FROM ent WHERE amb='ANY' AND administracion IS NOT NULL GROUP BY 1 ORDER BY 2 DESC LIMIT 20").fetchall() x(f"""CREATE TABLE oe AS SELECT o.entidad_clave, count(DISTINCT e.nif_ente) nn, any_value(e.nif_ente) nif FROM '{I}/organos.parquet' o JOIN '{R}/organos_entes.parquet' e USING (organo_clave) WHERE e.nif_ente IS NOT NULL GROUP BY 1""") x("""CREATE TABLE ed AS SELECT ent.entidad_clave, count(DISTINCT d3.nif) nn, any_value(d3.nif) nif FROM ent JOIN d3 ON d3.k = ent.k AND ent.k<>'' AND (ent.amb='ANY' OR (ent.amb='EELL' AND d3.listado='EELL') OR (ent.amb='AGE' AND d3.listado IN ('AGE','INST')) OR (ent.amb LIKE 'A%' AND ent.amb<>'AGE' AND ent.amb<>'ANY' AND d3.listado='CCAA' AND left(d3.codigo,3)=ent.amb)) WHERE ent.entidad_clave LIKE 'N:%' GROUP BY 1""") x(f"""CREATE TABLE entidad_ente AS SELECT ent.entidad_clave, ent.nombre, ent.administracion, CASE WHEN ent.entidad_clave NOT LIKE 'N:%' THEN ent.entidad_clave WHEN oe.nn=1 THEN oe.nif WHEN ed.nn=1 THEN ed.nif ELSE ent.entidad_clave END ente, CASE WHEN ent.entidad_clave NOT LIKE 'N:%' THEN 'nif' WHEN oe.nn=1 THEN 'organos_entes' WHEN ed.nn=1 THEN 'dir3_nombre' ELSE 'nombre' END ente_via FROM ent LEFT JOIN oe USING (entidad_clave) LEFT JOIN ed USING (entidad_clave)""") x(f"ALTER TABLE entidad_ente ADD COLUMN ente_dni BOOLEAN; UPDATE entidad_ente SET ente_dni = regexp_matches(ente, {DNI})") r['entidad_ente_via']=x("SELECT ente_via, count(*), count(DISTINCT ente) FROM entidad_ente GROUP BY 1 ORDER BY 2 DESC").fetchall() r['entes_dni']=x("SELECT count(DISTINCT ente) FROM entidad_ente WHERE ente_dni").fetchone()[0] log('entidad_ente', r['entidad_ente_via']) # ── §3.1 concedente -> ente ── x("""CREATE TABLE ca AS SELECT * FROM read_json('/bdns/concesiones/*/*.jsonl.gz', format='newline_delimited', union_by_name=true, columns={id:'VARCHAR',fechaConcesion:'VARCHAR',importe:'VARCHAR',nivel1:'VARCHAR',nivel2:'VARCHAR',nivel3:'VARCHAR',codigoInvente:'VARCHAR',_cif:'VARCHAR'})""") assert x("SELECT count(*) = count(DISTINCT id) FROM ca").fetchone()[0] x("""CREATE TABLE cd AS SELECT nivel1, nivel2, nivel3, codigoInvente inv, count(*) n, sum(try_cast(importe AS DOUBLE)) imp FROM ca WHERE fechaConcesion BETWEEN '2022-01-01' AND '2025-12-31' GROUP BY ALL""") x("""CREATE TABLE cd2 AS SELECT row_number() OVER (ORDER BY cd.nivel1, cd.nivel2, cd.nivel3, cd.inv) cid, cd.*, cc.pref, nk(coalesce(cd.nivel3, cd.nivel2)) k3, nk(cd.nivel2) k2 FROM cd LEFT JOIN cc ON cd.nivel1='AUTONOMICA' AND cc.nivel2=cd.nivel2""") assert x("SELECT count(*) FROM cd2 WHERE nivel1='AUTONOMICA' AND pref IS NULL").fetchone()[0]==0 x(f"""CREATE TABLE v1 AS SELECT cid, any_value(e.nif) nif, count(DISTINCT e.nif) nn FROM cd2 JOIN '{R}/entes.parquet' e ON cd2.inv IN (e.codigo_invente, e.codigo_invente_origen) WHERE e.nif IS NOT NULL GROUP BY cid""") x("""CREATE TABLE v2 AS SELECT cid, any_value(d3.nif) nif, count(DISTINCT d3.nif) nn FROM cd2 JOIN d3 ON d3.k = cd2.k3 AND cd2.k3 <> '' AND ((cd2.nivel1='LOCAL' AND d3.listado='EELL') OR (cd2.nivel1='AUTONOMICA' AND d3.listado='CCAA' AND left(d3.codigo,3)=cd2.pref) OR (cd2.nivel1='ESTADO' AND d3.listado IN ('AGE','INST')) OR (cd2.nivel1='OTROS')) GROUP BY cid""") SINPERS = "'^(DIRECCION GENERAL|SECRETARIA|SUBSECRETARIA|GABINETE|DELEGACION|SUBDIRECCION|COMISIONADO|DEPARTAMENTO)( |$)'" x(f"""CREATE TABLE v3 AS SELECT cid, any_value(d3.nif) nif, count(DISTINCT d3.nif) nn FROM cd2 JOIN d3 ON d3.k = cd2.k2 AND d3.listado='AGE' WHERE cd2.nivel1='ESTADO' AND cd2.inv IS NULL AND regexp_matches(n0(cd2.nivel3), {SINPERS}) GROUP BY cid""") x("""CREATE TABLE v4 AS SELECT cid, any_value(ent.entidad_clave) ek, count(DISTINCT ent.entidad_clave) nn FROM cd2 JOIN ent ON ent.k = cd2.k3 AND cd2.k3<>'' GROUP BY cid""") x("CREATE TABLE entes_c AS SELECT DISTINCT ente FROM entidad_ente") x("""CREATE TABLE ce AS SELECT cd2.* EXCLUDE (k3, k2), v1.nif v1_nif, v1.nn v1_nn, v2.nif v2_nif, v2.nn v2_nn, v3.nif v3_nif, v3.nn v3_nn, v4.ek v4_ek, v4.nn v4_nn, ee4.ente v4_ente FROM cd2 LEFT JOIN v1 USING(cid) LEFT JOIN v2 USING(cid) LEFT JOIN v3 USING(cid) LEFT JOIN v4 USING(cid) LEFT JOIN entidad_ente ee4 ON ee4.entidad_clave = v4.ek""") x("""CREATE TABLE concedente_ente AS SELECT *, CASE WHEN v1_nn=1 THEN 'V1_invente' WHEN v2_nn=1 THEN 'V2_dir3_nombre' WHEN coalesce(v2_nn,0)=0 AND v3_nn=1 THEN 'V3_ministerio' WHEN coalesce(v2_nn,0)=0 AND v4_nn=1 THEN 'V4_nombre_nuestro' END via FROM ce""") x("""ALTER TABLE concedente_ente ADD COLUMN ente VARCHAR; UPDATE concedente_ente SET ente = CASE via WHEN 'V1_invente' THEN v1_nif WHEN 'V2_dir3_nombre' THEN v2_nif WHEN 'V3_ministerio' THEN v3_nif WHEN 'V4_nombre_nuestro' THEN v4_ente END""") x("""ALTER TABLE concedente_ente ADD COLUMN con_contratos BOOLEAN; UPDATE concedente_ente SET con_contratos = ente IN (SELECT ente FROM entes_c)""") x("""ALTER TABLE concedente_ente ADD COLUMN motivo_sin VARCHAR; UPDATE concedente_ente SET motivo_sin = CASE WHEN via IS NOT NULL AND con_contratos THEN NULL WHEN via IS NOT NULL THEN 'ente_sin_entidad_nuestra' WHEN v1_nn>1 OR v2_nn>1 OR v3_nn>1 OR v4_nn>1 THEN 'ambiguo' ELSE 'sin_candidato' END""") r['concedentes_2225']=x("SELECT count(*), sum(n), sum(imp) FROM concedente_ente").fetchone() r['concedente_via']=x("SELECT coalesce(via,'-'), coalesce(motivo_sin,'casado'), count(*), sum(n), round(sum(imp)/1e9,3), count(DISTINCT ente) FROM concedente_ente GROUP BY 1,2 ORDER BY 4 DESC").fetchall() r['concedente_nivel1']=x("""SELECT nivel1, count(*), count(*) FILTER (WHERE motivo_sin IS NULL), round(sum(n) FILTER (WHERE motivo_sin IS NULL)/sum(n),4), round(sum(imp) FILTER (WHERE motivo_sin IS NULL)/sum(imp),4) FROM concedente_ente GROUP BY 1 ORDER BY 1""").fetchall() log('concedentes', r['concedente_via']) # ── S ── x(f"""CREATE TABLE s_filas AS SELECT ca.id, upper(trim(ca._cif)) cif, year(try_cast(ca.fechaConcesion AS DATE))::INTEGER anio, try_cast(ca.importe AS DOUBLE) importe, ce.ente, ce.via ente_via, ce.cid FROM ca JOIN concedente_ente ce ON ce.nivel1 IS NOT DISTINCT FROM ca.nivel1 AND ce.nivel2 IS NOT DISTINCT FROM ca.nivel2 AND ce.nivel3 IS NOT DISTINCT FROM ca.nivel3 AND ce.inv IS NOT DISTINCT FROM ca.codigoInvente WHERE ca.fechaConcesion BETWEEN '2022-01-01' AND '2025-12-31'""") r['s_filas']=x(f"""SELECT count(*), count(*) FILTER (WHERE regexp_matches(cif, {NIFPJ})), count(*) FILTER (WHERE ente IS NOT NULL AND ente IN (SELECT ente FROM entes_c)), sum(importe), count(DISTINCT cif) FROM s_filas""").fetchone() x(f"DELETE FROM s_filas WHERE NOT regexp_matches(cif, {NIFPJ})") x("COPY s_filas TO '/w/interno/s_filas.parquet' (FORMAT parquet)") x("COPY concedente_ente TO '/w/interno/concedente_ente.parquet' (FORMAT parquet)") x("COPY entidad_ente TO '/w/interno/entidad_ente.parquet' (FORMAT parquet)") log('s_filas', r['s_filas']) # ── C ── x("CREATE TABLE nocuenta AS SELECT id_canonical FROM '/d/unified_20261018.vinculos/filas.parquet' WHERE NOT cuenta") x("""CREATE TABLE u AS SELECT source, id_canonical, anio_ref::INTEGER anio, organo_nif, organo_nombre, dir3, adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, CASE WHEN coalesce(rol_am IN ('acuerdo','sda'), FALSE) THEN NULL ELSE imp_adj_eur_corregido END importe, cpv_division, tipo_contrato, es_menor FROM read_parquet('/d/unified_20261018/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE unidad='adjudicacion' AND anio_ref BETWEEN 2022 AND 2025 AND copia_de IS NULL AND NOT coalesce(correccion_imp_adj='copia_otra_fuente', FALSE) AND id_canonical NOT IN (SELECT id_canonical FROM nocuenta)""") r['c_filas_universo']=x("SELECT count(*), sum(importe) FILTER (WHERE abs(importe)<=1e9) FROM u").fetchone() log('u', r['c_filas_universo']) nd=x(f"SELECT count(*) FROM (SELECT organo_nif, organo_nombre, dir3 FROM '{I}/organos_filas.parquet' GROUP BY ALL HAVING count(*)>1)").fetchone()[0]; assert nd==0 nd=x(f"SELECT count(*) FROM (SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre FROM '{I}/adjudicatarios_filas.parquet' GROUP BY ALL HAVING count(*)>1)").fetchone()[0]; assert nd==0 x(f"""CREATE TABLE tipo_ente AS SELECT ente, arg_max(tipo, f) tipo FROM (SELECT ee.ente, coalesce(t.tipo,'sin_tipo') tipo, sum(o.filas) f FROM '{I}/organos.parquet' o JOIN entidad_ente ee USING (entidad_clave) LEFT JOIN '{I}/organos_tipo.parquet' t USING (organo_clave) GROUP BY 1, 2 ORDER BY 1, 3 DESC, 2) GROUP BY 1""") x(f"""CREATE TABLE c_filas AS SELECT u.source, u.id_canonical, u.anio, u.importe, u.cpv_division, u.tipo_contrato, u.es_menor, of.entidad_clave, ee.ente, ee.ente_dni, a.empresa_clave, a.empresa_tipo, a.empresa_via, (a.empresa_tipo='empresa' AND a.empresa_via='nif' AND regexp_matches(a.empresa_clave, {NIFPJ})) emp_principal, (a.empresa_tipo='empresa' AND a.empresa_via IN ('nif','diccionario') AND regexp_matches(a.empresa_clave, {NIFPJ})) emp_sens FROM u LEFT 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 entidad_ente ee ON ee.entidad_clave = of.entidad_clave 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""") n0,n1=x("SELECT (SELECT count(*) FROM u), (SELECT count(*) FROM c_filas)").fetchone(); assert n0==n1,(n0,n1) x("ALTER TABLE c_filas ADD COLUMN tipo_ente VARCHAR; UPDATE c_filas SET tipo_ente = coalesce((SELECT tipo FROM tipo_ente t WHERE t.ente=c_filas.ente),'sin_tipo')") r['c_filas']=x("""SELECT count(*), count(ente), count(*) FILTER (WHERE emp_principal), count(*) FILTER (WHERE emp_sens), sum(importe) FILTER (WHERE abs(importe)<=1e9 AND emp_principal), count(DISTINCT empresa_clave) FILTER (WHERE emp_principal), count(DISTINCT ente) FROM c_filas""").fetchone() r['c_por_via_empresa']=x("SELECT empresa_tipo, empresa_via, count(*), round(sum(importe) FILTER (WHERE abs(importe)<=1e9)/1e9,2) FROM c_filas GROUP BY ALL ORDER BY 3 DESC").fetchall() r['c_por_anio']=x("SELECT anio, count(*), count(*) FILTER (WHERE emp_principal), round(sum(importe) FILTER (WHERE abs(importe)<=1e9)/1e9,2) FROM c_filas GROUP BY 1 ORDER BY 1").fetchall() x("COPY c_filas TO '/w/interno/c_filas.parquet' (FORMAT parquet)") x("COPY tipo_ente TO '/w/interno/tipo_ente.parquet' (FORMAT parquet)") log('c_filas', r['c_filas']) json.dump(r, open('/w/salidas/10_base.json','w'), ensure_ascii=False, indent=1, default=str)