"""EJ2 · 10 · universo de EJ1 §1 (mismo SQL) + clase M (EJ1 §2) -> interno/u.parquet (licitación) e interno/lote.parquet (V-lote, V-final)""" 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, (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'""") 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, min(fref) F FROM f GROUP BY 1""") 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""") x(f"""CREATE TABLE m AS SELECT *, CASE WHEN cambia_importe AND (tipo IN ('modificacion','otro') OR (tipo IS NULL AND NOT cambia_plazo)) THEN 'M' END clase FROM '{D}.modificaciones/modificaciones.parquet' WHERE licitacion_id IN (SELECT licitacion_id FROM l)""") x("DELETE FROM m WHERE clase IS NULL") x("""CREATE TABLE ag AS SELECT licitacion_id, count(*) nM, sum(importe_modificacion_sin_iva) sM, sum(importe_modificacion_sin_iva) FILTER (WHERE tipo='modificacion') sMe, bool_or(importe_final_regla='no_cuadra') no_cuadra, bool_and(contrato_casado='uno') todas_uno, min(fecha_primera_version) fM FROM m GROUP BY 1""") x("""CREATE TABLE uu AS SELECT l.licitacion_id, l.anio, l.cpv, l.tipo, l.organo_clave, l.organo_nombre, l.tipo_organo, l.A, l.F, a.nM, a.sM, a.fM, a.sM/l.A s, coalesce(a.sMe,0)/l.A s_estricta, a.no_cuadra, a.todas_uno, CASE WHEN l.F < DATE '2018-03-09' THEN 'TRLCSP probable' ELSE 'LCSP' END regimen FROM l JOIN ag a USING (licitacion_id) WHERE l.A > 0""") R={} R['n_U']=x("SELECT count(*), count(*) FILTER (WHERE s<0), count(*) FILTER (WHERE s>=0.6), count(*) FILTER (WHERE s IS NULL), count(*) FILTER (WHERE no_cuadra), count(*) FILTER (WHERE todas_uno) FROM uu").fetchone(); log(R['n_U']) assert R['n_U'][0]==22227, R['n_U'] # V-lote y V-final: por contrato (id_canonical) en licitaciones con todas las M casadas 'uno' x("""CREATE TABLE lote AS WITH mm AS (SELECT m.*, row_number() OVER (PARTITION BY m.id_canonical ORDER BY orden_modificacion DESC, modificacion_id DESC) rk FROM m JOIN uu USING (licitacion_id) WHERE uu.todas_uno) SELECT mm.id_canonical, any_value(mm.licitacion_id) licitacion_id, sum(importe_modificacion_sin_iva) sM_lote, coalesce(max(importe_adjudicacion_sin_iva), max(f.imp)) A_lote, max(importe_final_sin_iva) FILTER (WHERE rk=1 AND importe_final_regla IN ('adjudicacion_mas_acumulado','anterior_mas_modificacion')) final_ult, max(importe_adjudicacion_sin_iva) A_sat FROM mm LEFT JOIN f ON f.id_canonical=mm.id_canonical GROUP BY 1""") x("""CREATE TABLE lote2 AS SELECT lote.*, uu.tipo, uu.anio, CASE WHEN A_lote>0 THEN sM_lote/A_lote END s_lote, CASE WHEN A_sat>0 AND final_ult IS NOT NULL THEN final_ult/A_sat - 1 END s_fin FROM lote JOIN uu USING (licitacion_id)""") R['n_lote']=x("SELECT count(*), count(s_lote), count(s_fin), count(DISTINCT licitacion_id) FROM lote2").fetchone(); log(R['n_lote']) x("COPY uu TO '/w/interno/u.parquet' (FORMAT parquet)") x("COPY lote2 TO '/w/interno/lote.parquet' (FORMAT parquet)") x("COPY (SELECT modificacion_id, licitacion_id, orden_modificacion, id_contrato, id_canonical, tipo, nota, importe_modificacion_sin_iva, importe_final_sin_iva, importe_adjudicacion_sin_iva, importe_final_regla, fecha_primera_version, url_origen FROM m) TO '/w/interno/m.parquet' (FORMAT parquet)") json.dump(R, open('/w/salidas/10_base.json','w'), ensure_ascii=False, indent=1, default=str) log('fin')