"""BU2 · 10 · universo (DEFINICIONES_PREVIAS §1-2), exposición y bins del hueco (§5). DuckDB 1.1.3 (imagen etl:v2). Escribe interno/menores.parquet (PLACSP años validados + MADRID_CAM, [0,70u,u)), interno/hueco_bins.parquet y salidas/10_base.json.""" import duckdb, json, time 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") def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) I = '/d/aggregations_20261018am/indicadores/' x("CREATE VIEW u AS SELECT * FROM read_parquet('/d/unified_20261018/**/*.parquet', hive_partitioning=1, union_by_name=1)") x("CREATE VIEW of AS SELECT * FROM '/d/unified_20261018.identidad/organos_filas.parquet'") x("CREATE VIEW ot AS SELECT * FROM '/d/unified_20261018.identidad/organos_tipo.parquet'") x("CREATE TABLE nocuenta AS SELECT DISTINCT id_canonical FROM '/d/unified_20261018.vinculos/filas.parquet' WHERE NOT cuenta") R = {} # ── universo M de BU1 (todas las fuentes, para el deduplicado) ── x("""CREATE TABLE f0 AS SELECT id_canonical, source, tipo_contrato tipo, anio_ref::INTEGER anio, imp_adj_eur_comparable::DECIMAL(18,2) imp, organo_nif, organo_nombre, dir3, iva_base, (correccion_imp_adj IS NOT DISTINCT FROM 'copia_otra_fuente') copia_fuente FROM u WHERE es_menor AND unidad='adjudicacion' AND vigente AND es_contrato AND copia_de IS NULL AND imp_adj_eur_comparable > 0 AND tipo_contrato IN ('Servicios','Suministros','Obras') AND anio_ref BETWEEN 2019 AND 2025 AND (fecha_adj IS NULL OR fecha_adj >= DATE '2018-03-09') AND NOT (source='ARAGON' AND iva_base='sin_medido')""") x("""CREATE TABLE f1 AS SELECT f.*, o.organo_clave, coalesce(t.agente_secti, FALSE) secti, CASE WHEN f.tipo='Obras' THEN 'O' WHEN coalesce(t.agente_secti, FALSE) THEN 'SSI' ELSE 'SS' END grupo, CASE WHEN f.tipo='Obras' THEN 40000 ELSE 15000 END::INTEGER umbral FROM f0 f LEFT JOIN of o ON o.organo_nif IS NOT DISTINCT FROM f.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM f.organo_nombre AND o.dir3 IS NOT DISTINCT FROM f.dir3 LEFT JOIN ot t ON t.organo_clave = o.organo_clave WHERE o.organo_clave IS NOT NULL""") x("""CREATE TABLE elige AS SELECT organo_clave, anio, grupo, arg_min(source, (-n, CASE WHEN source='PLACSP' THEN 0 ELSE 1 END, source)) source FROM (SELECT organo_clave, anio, grupo, source, count(*) n FROM f1 WHERE imp < umbral GROUP BY ALL) GROUP BY ALL""") x("CREATE TABLE m AS SELECT f1.* FROM f1 JOIN elige USING (organo_clave, anio, grupo, source)") R['M_dedup'] = x("SELECT count(*) FROM m").fetchone()[0] # comprobación §1: PLACSP de los años validados en [0,70u,u) = BU1 interno/m.parquet x("""CREATE TABLE p0 AS SELECT * FROM m WHERE source='PLACSP' AND anio IN (2020,2022,2023,2024) AND imp >= 0.70*umbral AND imp < umbral""") q = x("""SELECT (SELECT count(*) FROM p0), (SELECT count(*) FROM '/bu1/interno/m.parquet' WHERE source='PLACSP' AND anio IN (2020,2022,2023,2024) AND imp >= 0.70*umbral AND imp < umbral), (SELECT count(*) FROM p0 a FULL JOIN (SELECT * FROM '/bu1/interno/m.parquet' WHERE source='PLACSP' AND anio IN (2020,2022,2023,2024) AND imp >= 0.70*umbral AND imp < umbral) b USING (id_canonical) WHERE a.id_canonical IS NULL OR b.id_canonical IS NULL OR a.imp<>b.imp OR a.grupo<>b.grupo)""").fetchone() R['cotejo_bu1_m'] = {'bu2': q[0], 'bu1': q[1], 'diferencias': q[2]}; log(R['cotejo_bu1_m']) assert q[2] == 0, q # ── exposición PLACSP ── x(f"""CREATE TABLE ser AS SELECT 'PLACSP' fuente, ms.serie_m1r, ms.anio_fin t, ms.suma, ms.n, sl.parejas_candidatas, sl.paso_a_licitacion_misma, (sl.serie_m1r IS NOT NULL) en_sl FROM '{I}m1r_series.parquet' ms LEFT JOIN (SELECT * FROM '{I}series_licitadas_series.parquet' WHERE fuente='PLACSP') sl USING (serie_m1r)""") x(f"""INSERT INTO ser SELECT 'MADRID_CAM', serie_m1r, anio, suma, n, parejas_candidatas, paso_a_licitacion_misma, TRUE FROM '{I}series_licitadas_series.parquet' WHERE fuente='MADRID_CAM'""") assert x("SELECT count(*) FROM (SELECT fuente, serie_m1r FROM ser GROUP BY ALL HAVING count(*)>1)").fetchone()[0] == 0 x("""CREATE TABLE ser2 AS SELECT *, CASE WHEN ((fuente='PLACSP' AND t IN (2020,2022,2023,2024)) OR (fuente='MADRID_CAM' AND t IN (2021,2022,2024))) AND paso_a_licitacion_misma THEN 'MISMA' WHEN NOT en_sl OR paso_a_licitacion_misma IS NULL THEN 'DESCONOCIDO' WHEN NOT ((fuente='PLACSP' AND t IN (2020,2022,2023,2024)) OR (fuente='MADRID_CAM' AND t IN (2021,2022,2024))) AND parejas_candidatas > 0 THEN 'DESCONOCIDO' ELSE 'NO' END estado FROM ser""") R['series_estado'] = x("SELECT fuente, t, estado, count(*) FROM ser2 GROUP BY ALL ORDER BY 1,2,3").fetchall() x(f"""CREATE TABLE pert AS SELECT 'PLACSP' fuente, id_canonical, serie_m1r FROM '{I}m1r_contratos.parquet' UNION ALL SELECT fuente, id_canonical, serie_m1r FROM '{I}series_licitadas_contratos.parquet' WHERE fuente='MADRID_CAM'""") assert x("SELECT count(*) FROM (SELECT fuente, id_canonical FROM pert GROUP BY ALL HAVING count(*)>1)").fetchone()[0] == 0 R['misma_sin_contratos'] = x("SELECT fuente, count(*), sum(n) FROM ser2 s WHERE estado='MISMA' AND NOT EXISTS (SELECT 1 FROM pert p WHERE p.fuente=s.fuente AND p.serie_m1r=s.serie_m1r) GROUP BY 1").fetchall() # ── Madrid (secundario): importe estimado ── x("""CREATE TABLE mad0 AS SELECT id_canonical, source, tipo_contrato tipo, anio_ref::INTEGER anio, (CASE WHEN iva_base IN ('sin','sin_medido') AND imp_adj_eur_comparable > 0 THEN imp_adj_eur_comparable ELSE imp_adj_eur_corregido/1.21 END)::DECIMAL(18,2) imp, organo_nif, organo_nombre, dir3, (correccion_imp_adj IS NOT DISTINCT FROM 'copia_otra_fuente') copia_fuente FROM u WHERE source='MADRID_CAM' AND es_menor AND unidad='adjudicacion' AND vigente AND es_contrato AND copia_de IS NULL AND tipo_contrato IN ('Servicios','Suministros','Obras') AND anio_ref IN (2021,2022,2024) AND (fecha_adj IS NULL OR fecha_adj >= DATE '2018-03-09')""") x("""CREATE TABLE mad AS SELECT f.*, o.organo_clave, CASE WHEN f.tipo='Obras' THEN 'O' WHEN coalesce(t.agente_secti, FALSE) THEN 'SSI' ELSE 'SS' END grupo, CASE WHEN f.tipo='Obras' THEN 40000 ELSE 15000 END::INTEGER umbral FROM mad0 f LEFT JOIN of o ON o.organo_nif IS NOT DISTINCT FROM f.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM f.organo_nombre AND o.dir3 IS NOT DISTINCT FROM f.dir3 LEFT JOIN ot t ON t.organo_clave = o.organo_clave WHERE o.organo_clave IS NOT NULL AND f.imp > 0""") # ── tabla de menores del análisis ── x("""CREATE TABLE men AS SELECT id_canonical, source fuente, tipo, anio, imp, organo_clave, grupo, umbral, copia_fuente FROM p0 UNION ALL SELECT id_canonical, source, tipo, anio, imp, organo_clave, grupo, umbral, copia_fuente FROM mad WHERE imp >= 0.70*umbral AND imp < umbral""") x(f"""COPY (SELECT m.*, (m.imp >= 0.95*m.umbral) franja, ((m.imp*100) % 50000 = 0) redondo, (m.copia_fuente OR m.id_canonical IN (SELECT id_canonical FROM nocuenta)) es_copia, p.serie_m1r, s.t, s.suma, s.n n_serie, coalesce(s.estado, 'SIN_SERIE') estado FROM men m LEFT JOIN pert p ON p.fuente=m.fuente AND p.id_canonical=m.id_canonical LEFT JOIN ser2 s ON s.fuente=p.fuente AND s.serie_m1r=p.serie_m1r) TO '/w/interno/menores.parquet' (FORMAT parquet)""") R['menores'] = x("""SELECT fuente, grupo, count(*), count(*) FILTER (WHERE es_copia) FROM '/w/interno/menores.parquet' GROUP BY ALL ORDER BY 1,2""").fetchall() R['pert_sin_casar_en_universo'] = x("""SELECT p.fuente, count(*) FROM pert p WHERE NOT EXISTS (SELECT 1 FROM men m WHERE m.fuente=p.fuente AND m.id_canonical=p.id_canonical) GROUP BY 1""").fetchall() log('menores', R['menores']) # ── hueco (§5): PLACSP, menores y no menores, [0,70u, 1,30u) ── x("""CREATE TABLE h0 AS SELECT u.id_canonical, u.tipo_contrato tipo, u.anio_ref::INTEGER anio, u.imp_adj_eur_comparable::DECIMAL(18,2) imp, coalesce(u.es_menor, FALSE) es_menor, o.organo_clave, CASE WHEN u.tipo_contrato='Obras' THEN 40000 ELSE 15000 END::INTEGER umbral, coalesce(t.agente_secti, FALSE) secti FROM u LEFT JOIN of 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 ot t ON t.organo_clave = o.organo_clave WHERE u.source='PLACSP' AND u.unidad='adjudicacion' AND u.vigente AND u.es_contrato AND u.copia_de IS NULL AND u.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND u.imp_adj_eur_comparable > 0 AND u.tipo_contrato IN ('Servicios','Suministros','Obras') AND u.anio_ref BETWEEN 2019 AND 2025 AND (u.fecha_adj IS NULL OR u.fecha_adj >= DATE '2018-03-09') AND o.organo_clave IS NOT NULL AND NOT (coalesce(t.agente_secti, FALSE) AND u.tipo_contrato <> 'Obras') AND u.id_canonical NOT IN (SELECT id_canonical FROM nocuenta) AND u.imp_adj_eur_comparable >= 0.70*(CASE WHEN u.tipo_contrato='Obras' THEN 40000 ELSE 15000 END) AND u.imp_adj_eur_comparable < 1.30*(CASE WHEN u.tipo_contrato='Obras' THEN 40000 ELSE 15000 END)""") x("CREATE TABLE celdas AS SELECT DISTINCT organo_clave, anio, tipo FROM h0 WHERE es_menor AND imp < umbral") x("""COPY (SELECT h.tipo, h.anio, h.umbral, (c.organo_clave IS NOT NULL) en_celda, h.es_menor, CASE WHEN h.imp = h.umbral THEN -1 ELSE floor((h.imp - 0.70*h.umbral) / (0.01*h.umbral))::INTEGER END b, count(*) c_all, count(*) FILTER (WHERE (h.imp*100) % 50000 <> 0) c FROM h0 h LEFT JOIN celdas c USING (organo_clave, anio, tipo) GROUP BY ALL) TO '/w/interno/hueco_bins.parquet' (FORMAT parquet)""") R['hueco_filas'] = x("SELECT count(*), count(*) FILTER (WHERE es_menor), count(*) FILTER (WHERE NOT es_menor AND imp >= umbral), count(*) FILTER (WHERE es_menor AND imp > umbral) FROM h0").fetchone() R['hueco_menor_en_celdas_vs_universo'] = x("""SELECT count(*) FROM h0 WHERE es_menor AND imp < umbral""").fetchone()[0] json.dump(R, open('/w/salidas/10_base.json', 'w'), ensure_ascii=False, indent=1, default=str); log('fin', json.dumps(R, default=str)[:3000])