"""MOD-EJEC · 20 · conjunto del modelo de modificación en el Registre públic de contractes (RPC) -> priv/ds_rpc.csv (700). Población: contratos del RPC en unified_20261103 (fuente CATALUNYA, contratos_registro): unidad 'adjudicacion', no menores, vigentes, sin copia (copia_de NULL, correccion_imp_adj <> 'copia_otra_fuente'), sin basados ni SDA (rol_am NULL), sin emergencias, importe > 0, adjudicados del 1-ene-2017 al 31-dic-2025 (cohorte T = año de la adjudicación). Variables a la fecha de adjudicación: las del RPC (tipo, procedimiento, CPV, importe, duración, lotes y adjudicatarios del expediente, ámbito y agrupación del organismo, mes) y, por el vínculo de la construcción (.vinculos, mismo contrato), las del anuncio en la PSCP o la PLACSP (número de ofertas, presupuesto, valor estimado, días entre anuncio y adjudicación). Etiquetas (del satélite .cambios, fuente CATALUNYA, contrato casado): modificaciones inscritas (tipo modificacion o ampliacion_plazo; fuera suspensiones y sucesiones) con su fecha de aprobación. Sin red.""" import duckdb, json, time T0 = time.time() c = duckdb.connect(); x = c.execute x("SET threads=3; SET memory_limit='2500MB'; SET temp_directory='/w/tmp/duck'; SET max_temp_directory_size='6GB'; SET preserve_insertion_order=false") D = '/d/unified_20261103'; RAW = '/d/raw_preserved/20261103/catalunya/parquet/contratacion/contratos_registro.parquet' def log(*a): print(f"[{time.time()-T0:6.1f}s]", *a, flush=True) x(f"CREATE VIEW u AS SELECT * FROM read_parquet('{D}/source=CATALUNYA/**/*.parquet', hive_partitioning=1, union_by_name=1)") x(f"""CREATE TABLE k AS SELECT id_canonical, licitacion_id, lote_id, fila_origen, expediente, fecha_adj, year(fecha_adj)::INT T, tipo_contrato, procedimiento, procedimiento_native proc_nat, cpv_division cpv_div, imp_adj_eur imp_adj, organo_nombre, split_part(split_part(licitacion_id, ':', 2), '|', 1) org, adjudicatario_nombre FROM u WHERE tabla_origen='contratos_registro' AND unidad='adjudicacion' AND NOT es_menor AND vigente AND es_contrato AND copia_de IS NULL AND coalesce(correccion_imp_adj,'') <> 'copia_otra_fuente' AND rol_am IS NULL AND procedimiento_native <> 'Contractes d''emergències' AND imp_adj_eur > 0 AND fecha_adj BETWEEN DATE '2017-01-01' AND DATE '2025-12-31'""") R = {'contratos': x("SELECT T, count(*) FROM k GROUP BY 1 ORDER BY 1").fetchall()}; log(R) # del crudo que leyó la construcción: duración, ámbito, agrupación, lote x(f"""CREATE TABLE r AS SELECT file_row_number f, txt_e, "Àmbit organitzatiu" ambit, "Identificador agrupació organisme" agrup, "Número de lot" lote_num, "Durada anys" da, "Durada mesos" dm, "Durada dies" dd FROM (SELECT *, "Codi de l’expedient" txt_e FROM read_parquet('{RAW}', file_row_number=true)) WHERE file_row_number IN (SELECT fila_origen FROM k)""") x("""CREATE TABLE k2 AS SELECT k.*, r.ambit, r.agrup, r.lote_num, CASE WHEN coalesce(r.da,0)+coalesce(r.dm,0)+coalesce(r.dd,0) > 0 THEN coalesce(r.da,0)*12 + coalesce(r.dm,0) + coalesce(r.dd,0)/30.4375 END dur, (trim(r.txt_e) IS NOT DISTINCT FROM trim(k.expediente)) exp_ok FROM k LEFT JOIN r ON r.f = k.fila_origen""") R['crudo'] = x("SELECT count(*), count(ambit), sum(exp_ok::INT), count(dur) FROM k2").fetchone(); log('crudo', R['crudo']) assert R['crudo'][2] == R['crudo'][0], 'el crudo no casa por fila de origen' # expediente: lotes y adjudicatarios x("""CREATE TABLE e AS SELECT licitacion_id, count(DISTINCT lote_id) n_lotes, count(DISTINCT adjudicatario_nombre) n_adj_exp FROM k GROUP BY 1""") # el anuncio por el vínculo (mismo contrato): PSCP primero, luego PLACSP x(f"CREATE TABLE vf AS SELECT id_canonical, source, grupo_contrato FROM '{D}.vinculos/filas.parquet' WHERE grupo_contrato IS NOT NULL") x("CREATE TABLE g AS SELECT k.id_canonical, v.grupo_contrato FROM k JOIN vf v USING (id_canonical)") for s in ('CATALUNYA_PSCP', 'PLACSP'): x(f"""CREATE TABLE a_{s} AS SELECT g.id_canonical, max(t.num_ofertas) nof, max(coalesce(t.imp_lic_eur, t.imp_lic_eur_expediente)) imp_lic, max(coalesce(t.valor_estimado_eur, t.valor_estimado_eur_expediente)) ve, min(t.fecha_pub) fpub, arg_min(t.tramitacion, t.id_canonical) tram FROM g JOIN vf v2 ON v2.grupo_contrato = g.grupo_contrato AND v2.source = '{s}' JOIN read_parquet('{D}/source={s}/**/*.parquet', hive_partitioning=1, union_by_name=1) t ON t.id_canonical = v2.id_canonical GROUP BY 1""") x("""CREATE TABLE an AS SELECT k.id_canonical, CASE WHEN p.id_canonical IS NOT NULL THEN 'PSCP' WHEN q.id_canonical IS NOT NULL THEN 'PLACSP' END link_src, coalesce(p.nof, q.nof) nof, coalesce(p.imp_lic, q.imp_lic) imp_lic, coalesce(p.ve, q.ve) ve, coalesce(p.fpub, q.fpub) fpub, coalesce(p.tram, q.tram) tram FROM k LEFT JOIN a_CATALUNYA_PSCP p USING (id_canonical) LEFT JOIN a_PLACSP q USING (id_canonical)""") R['anuncio'] = x("SELECT link_src, count(*), count(nof), count(imp_lic), count(fpub) FROM an GROUP BY 1 ORDER BY 2 DESC").fetchall(); log(R['anuncio']) # etiquetas x(f"""CREATE TABLE ca AS SELECT id_canonical, fecha, tipo, importe_cambio FROM '{D}.cambios/cambios.parquet' WHERE fuente='CATALUNYA' AND tipo IN ('modificacion','ampliacion_plazo') AND id_canonical IN (SELECT id_canonical FROM k)""") x("""CREATE TABLE lab AS SELECT k.id_canonical, count(ca.id_canonical) n_mod_total, count(ca.id_canonical) FILTER (WHERE ca.fecha IS NULL) n_mod_sin_fecha, min(ca.fecha) FILTER (WHERE ca.fecha >= k.fecha_adj) f_primera, bool_or(ca.fecha BETWEEN k.fecha_adj AND k.fecha_adj + INTERVAL 365 DAY) y12, bool_or(ca.fecha BETWEEN k.fecha_adj AND k.fecha_adj + INTERVAL 365 DAY AND coalesce(ca.importe_cambio,0) <> 0) y12_imp, bool_or(ca.fecha BETWEEN k.fecha_adj AND k.fecha_adj + INTERVAL 730 DAY) y24, bool_or(ca.fecha < k.fecha_adj) antes_adj FROM k LEFT JOIN ca USING (id_canonical) GROUP BY 1""") x("""CREATE TABLE ds AS SELECT k2.id_canonical, k2.licitacion_id, k2.org, k2.agrup, k2.ambit, k2.T, k2.fecha_adj, k2.tipo_contrato, k2.proc_nat, k2.cpv_div, k2.imp_adj, k2.dur, k2.lote_num, e.n_lotes, e.n_adj_exp, an.link_src, an.nof, an.imp_lic, an.ve, date_diff('day', an.fpub, k2.fecha_adj) dias_pub_adj, an.tram, coalesce(lab.y12, FALSE)::INT y, coalesce(lab.y12_imp, FALSE)::INT y_imp, coalesce(lab.y24, FALSE)::INT y24, lab.n_mod_total, lab.n_mod_sin_fecha, coalesce(lab.antes_adj, FALSE) antes_adj, date_diff('day', k2.fecha_adj, lab.f_primera) dias_mod FROM k2 JOIN e USING (licitacion_id) JOIN an USING (id_canonical) JOIN lab USING (id_canonical)""") R['etiquetas'] = x("""SELECT T, count(*), sum(y), sum(y_imp), sum(y24), count(*) FILTER (WHERE n_mod_sin_fecha > 0 AND y = 0), count(*) FILTER (WHERE antes_adj) FROM ds GROUP BY 1 ORDER BY 1""").fetchall(); log(R['etiquetas']) x("COPY ds TO '/w/priv/ds_rpc.csv' (HEADER, DELIMITER ',')") json.dump(R, open('/w/salidas/20_ds_rpc.json', 'w'), ensure_ascii=False, indent=1, default=str) log('fin')