"""EJ1 · 10 · licitaciones del universo (§1) con sus clases de publicación (§2) y y1, y2, y3, yP, s (§3) -> interno/lic.parquet""" 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) D='/d/unified_20261018' x(f"CREATE VIEW u AS SELECT * FROM read_parquet('{D}/source=PLACSP/**/*.parquet', hive_partitioning=1, union_by_name=1)") x(f"CREATE VIEW vf AS SELECT id_canonical, cuenta FROM read_parquet('{D}.vinculos/filas.parquet') WHERE source='PLACSP'") PROC="('Abierto','Restringido','Negociado_publicidad','Negociado_sin_publicidad','Dialogo_competitivo','Asociacion_innovacion')" x(f"""CREATE TABLE f AS SELECT u.id_canonical, licitacion_id, anio_ref, cpv_division, tipo_contrato, organo_nif, organo_nombre, dir3, imp_adj_eur_comparable imp, coalesce(fecha_formalizacion, fecha_adj) fref, url_origen, (copia_de IS NOT NULL OR coalesce(correccion_imp_adj = 'copia_otra_fuente', FALSE) OR coalesce(NOT v.cuenta, FALSE)) es_copia FROM u LEFT JOIN vf v ON v.id_canonical=u.id_canonical WHERE tabla_origen='resultados_licitaciones' AND vigente AND es_contrato AND unidad='adjudicacion' AND es_menor = FALSE AND anio_ref BETWEEN 2018 AND 2025 AND procedimiento IN {PROC} AND coalesce(rol_am,'') NOT IN ('acuerdo','sda','basado','basado_sda') AND coalesce(tramitacion,'') <> 'emergencia'""") R={} R['filas']=x("SELECT count(*), count(*) FILTER (WHERE es_copia), count(*) FILTER (WHERE licitacion_id IS NULL) FROM f").fetchone(); log(R['filas']) assert R['filas'][2]==0 x("DELETE FROM f WHERE es_copia") x(f"""CREATE TABLE l0 AS SELECT licitacion_id, max(anio_ref)::INT anio, arg_min(cpv_division,id_canonical) cpv_div, arg_min(tipo_contrato,id_canonical) tipo_c, arg_min(organo_nif,id_canonical) organo_nif, arg_min(organo_nombre,id_canonical) organo_nombre, arg_min(dir3,id_canonical) dir3, sum(imp) A, count(imp) filas_imp, count(*) filas, min(fref) F, arg_min(url_origen,id_canonical) url_origen, min(id_canonical) id_min FROM f GROUP BY 1""") nd=x(f"SELECT count(*) FROM (SELECT organo_nif, organo_nombre, dir3 FROM '{D}.identidad/organos_filas.parquet' GROUP BY ALL HAVING count(*)>1)").fetchone()[0] assert nd==0, nd x(f"""CREATE TABLE l AS SELECT l0.*, coalesce(l0.cpv_div,'??') cpv, CASE WHEN tipo_c IN ('Obras','Servicios','Suministros') THEN tipo_c ELSE 'Otros' END tipo, o.organo_clave, coalesce(t.tipo,'sin_tipo') tipo_organo FROM l0 LEFT JOIN '{D}.identidad/organos_filas.parquet' o ON o.organo_nif IS NOT DISTINCT FROM l0.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM l0.organo_nombre AND o.dir3 IS NOT DISTINCT FROM l0.dir3 LEFT JOIN '{D}.identidad/organos_tipo.parquet' t ON t.organo_clave=o.organo_clave""") assert x("SELECT (SELECT count(*) FROM l0)=(SELECT count(*) FROM l)").fetchone()[0] # clases x(f"""CREATE TABLE m AS SELECT modificacion_id, licitacion_id, tipo, importe_modificacion_sin_iva imp_m, cambia_importe ci, cambia_plazo cp, fecha_primera_version fpv, importe_final_regla regla, contrato_casado, CASE WHEN ci AND (tipo IN ('modificacion','otro') OR (tipo IS NULL AND NOT cp)) THEN 'M' WHEN tipo='prorroga' OR (tipo IS NULL AND cp) THEN 'P' WHEN tipo IN ('modificacion','otro') AND cp AND NOT ci THEN 'plazo' ELSE coalesce(tipo, 'sin_cambio') END clase, (ci AND tipo='modificacion') m_estricta, (ci AND (tipo IN ('modificacion','otro') OR tipo IS NULL)) m_amplia FROM '{D}.modificaciones/modificaciones.parquet' WHERE licitacion_id IN (SELECT licitacion_id FROM l)""") R['pub_en_universo_por_clase']=x("SELECT clase, count(*), count(DISTINCT licitacion_id) FROM m GROUP BY 1 ORDER BY 2 DESC").fetchall(); log(R['pub_en_universo_por_clase']) x("""CREATE TABLE ag AS SELECT licitacion_id, count(*) FILTER (WHERE clase='M') nM, sum(imp_m) FILTER (WHERE clase='M') sM, min(fpv) FILTER (WHERE clase='M') fM, count(*) FILTER (WHERE clase='P') nP, count(*) FILTER (WHERE clase='plazo') nPl, count(*) npub, count(*) FILTER (WHERE m_estricta) nMe, sum(imp_m) FILTER (WHERE m_estricta) sMe, count(*) FILTER (WHERE m_amplia) nMa, sum(imp_m) FILTER (WHERE m_amplia) sMa, bool_or(regla='no_cuadra' AND clase='M') no_cuadra FROM m GROUP BY 1""") x("""CREATE TABLE lic AS SELECT l.licitacion_id, l.anio, l.cpv, l.tipo, l.organo_clave, l.tipo_organo, l.A, l.F, l.url_origen, l.id_min, coalesce(a.nM,0) nM, coalesce(a.sM,0) sM, a.fM, coalesce(a.nP,0) nP, coalesce(a.nPl,0) nPl, coalesce(a.npub,0) npub, coalesce(a.nMe,0) nMe, coalesce(a.sMe,0) sMe, coalesce(a.nMa,0) nMa, coalesce(a.sMa,0) sMa, coalesce(a.no_cuadra,FALSE) no_cuadra, (coalesce(a.nM,0)>0) y1, CASE WHEN l.A>0 THEN coalesce(a.sM,0)/l.A END s, CASE WHEN l.A>0 THEN (coalesce(a.sM,0)/l.A > 0.20) END y2, (l.F BETWEEN DATE '2021-01-01' AND DATE '2025-10-10') en_h3, CASE WHEN l.F BETWEEN DATE '2021-01-01' AND DATE '2025-10-10' THEN (a.fM IS NOT NULL AND date_diff('day', l.F, a.fM) <= 365) END y3, CASE WHEN a.fM IS NOT NULL AND l.F IS NOT NULL THEN date_diff('day', l.F, a.fM) END dias_M, (coalesce(a.nP,0)>0) yP, CASE WHEN l.A>0 THEN coalesce(a.sMe,0)/l.A > 0.20 END y2e, CASE WHEN l.A>0 THEN coalesce(a.sMa,0)/l.A > 0.20 END y2a, (coalesce(a.npub,0) > 10) mas10 FROM l LEFT JOIN ag a USING (licitacion_id)""") R['licitaciones']=x("SELECT count(*), count(organo_clave), count(*) FILTER (WHERE A>0), count(*) FILTER (WHERE en_h3), count(*) FILTER (WHERE F IS NULL) FROM lic").fetchone() R['por_anio']=x("""SELECT anio, count(*) n, sum(y1::INT) k1, round(avg(y1::INT),4), count(y2) n2, sum(y2::INT) k2, round(avg(y2::INT),4), count(y3) n3, sum(y3::INT) k3, round(avg(y3::INT),4), sum(yP::INT) kP, round(avg(yP::INT),4) FROM lic GROUP BY 1 ORDER BY 1""").fetchall() R['por_tipo']=x("""SELECT tipo, count(*) n, round(avg(y1::INT),4), round(avg(y2::INT),4), round(avg(y3::INT),4), round(avg(yP::INT),4), round(sum(sM)/sum(A) FILTER (WHERE A>0),4) sobrecoste_agregado FROM lic GROUP BY 1 ORDER BY 1""").fetchall() R['dias_negativos_H3']=x("SELECT count(*) FROM lic WHERE en_h3 AND dias_M < 0").fetchone()[0] R['s_modificadas']=x("""SELECT tipo, count(*), round(median(s),4), round(quantile_cont(s,0.75),4), round(quantile_cont(s,0.9),4), round(avg((s>0.2)::INT),4), round(avg((s>0.5)::INT),4), count(*) FILTER (WHERE s<0) FROM lic WHERE y1 AND A>0 GROUP BY 1 ORDER BY 1""").fetchall() R['s_mayor_1']=x("SELECT count(*), count(*) FILTER (WHERE no_cuadra), count(*) FILTER (WHERE mas10) FROM lic WHERE s>1").fetchone() R['sin_organo']=x("SELECT count(*) FROM lic WHERE organo_clave IS NULL").fetchone()[0] R['organos']=x("SELECT count(DISTINCT organo_clave) FROM lic").fetchone()[0] R['celdas']=x("SELECT count(*), count(*) FILTER (WHERE n<50) FROM (SELECT cpv,tipo,anio,count(*) n FROM lic GROUP BY ALL)").fetchone() x("COPY lic TO '/w/interno/lic.parquet' (FORMAT parquet)") x("COPY m TO '/w/interno/pub.parquet' (FORMAT parquet)") json.dump(R, open('/w/salidas/10_base.json','w'), ensure_ascii=False, indent=1, default=str) for k,v in R.items(): print(k, v)