"""ML1 · 01 · base de lotes PLACSP no menores 2012-2025 con variables de proceso/contexto y marcas de etiqueta. No cruza variables con la etiqueta: solo construye. Escribe interno/base.parquet (700) y salidas/01_base.md (recuentos).""" import duckdb, time T0 = time.time() D = '/d/unified_20261018' c = duckdb.connect('/w/tmp/ml1.duckdb'); x = c.execute x("SET threads=4; SET memory_limit='7GB'; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") def log(*a): print(f"[{time.time()-T0:6.0f}s]", *a, flush=True) # 1. filas PLACSP adjudicadas no menores (incluye copias y anuladas: se marcan) x(f"""CREATE OR REPLACE TABLE r AS SELECT u.id_canonical, u.tabla_origen, u.licitacion_id, coalesce(u.lote_id,'') lote_id, u.organo_nif, u.organo_nombre, u.dir3, u.adjudicatario_nif_raw, u.adjudicatario_nif, u.adjudicatario_nombre, u.procedimiento, u.procedimiento_native, u.tramitacion, u.rol_am, u.tipo_contrato, u.cpv_division, CASE WHEN length(u.nuts)>=5 THEN left(u.nuts,5) ELSE nullif(u.nuts,'') END provincia, u.num_ofertas, u.oferta_min_eur mn, u.oferta_max_eur mx, u.ofertas_anormales an, u.imp_lic_eur pbl, u.imp_lic_reparto rep, u.imp_adj_eur adj, u.valor_estimado_eur_expediente vee, u.anio_ref anio, u.fecha_pub, u.fecha_adj, u.fecha_fin_ofertas, u.sara, u.financiacion_ue, coalesce(u.duracion_meses, u.duracion_meses_periodo) dur, u.es_pyme, (u.copia_de IS NOT NULL OR NOT coalesce(v.cuenta, TRUE) OR u.correccion_imp_adj IS NOT DISTINCT FROM 'copia_otra_fuente') es_copia, coalesce(u.anulado_en_origen, FALSE) anulado, v.dup_de FROM read_parquet('{D}/source=PLACSP/**/*.parquet', hive_partitioning=1, union_by_name=1) u LEFT JOIN (SELECT * FROM read_parquet('{D}.vinculos/filas.parquet') WHERE source='PLACSP') v ON v.id_canonical = u.id_canonical WHERE u.unidad='adjudicacion' AND u.es_contrato AND u.vigente AND NOT coalesce(u.es_menor, FALSE) AND u.anio_ref BETWEEN 2011 AND 2025""") log('r', x("SELECT count(*), count(*) FILTER (WHERE es_copia), count(*) FILTER (WHERE anulado) FROM r").fetchone()) # 2. identidad x(f"""CREATE OR REPLACE TABLE o1 AS SELECT DISTINCT organo_nif, organo_nombre, dir3, organo_clave FROM read_parquet('{D}.identidad/organos_filas.parquet')""") x(f"""CREATE OR REPLACE TABLE ot AS SELECT organo_clave, any_value(tipo) tipo FROM read_parquet('{D}.identidad/organos_tipo.parquet') GROUP BY 1""") x(f"""CREATE OR REPLACE TABLE a1 AS SELECT DISTINCT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, empresa_clave, persona_fisica, nombre_ute FROM read_parquet('{D}.identidad/adjudicatarios_filas.parquet')""") x(f"""CREATE OR REPLACE TABLE em AS SELECT empresa_clave, min(anio_primero) anio_primero FROM read_parquet('{D}.identidad/empresas.parquet') GROUP BY 1""") x("""CREATE OR REPLACE TABLE ri AS SELECT r.*, o.organo_clave, coalesce(ot.tipo,'sin_tipo') tipo_organo, a.empresa_clave, coalesce(a.persona_fisica,FALSE) persona_fisica, coalesce(a.nombre_ute,FALSE) es_ute, em.anio_primero FROM r LEFT JOIN o1 o ON o.organo_nif IS NOT DISTINCT FROM r.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM r.organo_nombre AND o.dir3 IS NOT DISTINCT FROM r.dir3 LEFT JOIN ot USING (organo_clave) LEFT JOIN a1 a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM r.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM r.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM r.adjudicatario_nombre LEFT JOIN em USING (empresa_clave)""") log('ri', x("SELECT count(*), (SELECT count(*) FROM r), count(organo_clave), count(empresa_clave) FROM ri").fetchone()) # 3. etiquetas: ids PLACSP directos; TED y PLACSP-no-cuenta -> su original PLACSP (dup_de) x("CREATE OR REPLACE TABLE lab0 AS SELECT DISTINCT exp, var, source, id_canonical FROM read_csv('/et/colusion/etiquetas/casado/interno/lotes_cartel_v2_ids.csv', header=true, all_varchar=true) WHERE var IN ('V2','V3')") x(f"""CREATE OR REPLACE TABLE vted AS SELECT v.source, v.id_canonical, v.dup_de FROM read_parquet('{D}.vinculos/filas.parquet') v SEMI JOIN (SELECT DISTINCT source, id_canonical FROM lab0) l ON l.source=v.source AND l.id_canonical=v.id_canonical""") x("""CREATE OR REPLACE TABLE lab1 AS SELECT l.exp, l.var, l.source src_etiqueta, CASE WHEN l.source='PLACSP' AND r.id_canonical IS NOT NULL AND NOT r.es_copia THEN l.id_canonical WHEN coalesce(v.dup_de,'') LIKE 'PLACSP:%' THEN v.dup_de WHEN l.source='PLACSP' THEN l.id_canonical END id_placsp FROM lab0 l LEFT JOIN vted v ON v.source=l.source AND v.id_canonical=l.id_canonical LEFT JOIN r ON l.source='PLACSP' AND r.id_canonical=l.id_canonical""") # 4. universo U: no copia, no anulado, 2012-2025, con órgano, sin persona física x("""CREATE OR REPLACE TABLE uu AS SELECT *, CASE WHEN tabla_origen='resultados_licitaciones' THEN 'p' ELSE 'a' END || '|' || licitacion_id || '|' || lote_id lote_key FROM ri WHERE NOT es_copia AND NOT anulado AND organo_clave IS NOT NULL AND NOT persona_fisica""") x("""CREATE OR REPLACE TABLE lab AS SELECT DISTINCT l.exp, l.var, l.src_etiqueta, uu.lote_key FROM lab1 l JOIN uu ON uu.id_canonical = l.id_placsp""") log('lab', x("SELECT exp, count(*), count(DISTINCT lote_key) FROM lab GROUP BY 1 ORDER BY 2 DESC").fetchall()) # 5. lote: fila representante = menor id_canonical; nº adjudicatarios; PBL del lote fiable (regla P8) x("""CREATE OR REPLACE TABLE nl AS SELECT licitacion_id, count(*) n_adj_lic, count(DISTINCT lote_id) n_lotes_lic FROM uu GROUP BY 1""") x("""CREATE OR REPLACE TABLE lt AS SELECT u.*, nl.n_adj_lic, nl.n_lotes_lic, k.n_adj_lote, CASE WHEN u.pbl > 0 AND (u.rep='por_lote' OR nl.n_adj_lic=1) THEN u.pbl END pbl_lote FROM (SELECT *, row_number() OVER (PARTITION BY lote_key ORDER BY id_canonical) rn FROM uu) u JOIN (SELECT lote_key, count(*) n_adj_lote FROM uu GROUP BY 1) k USING (lote_key) LEFT JOIN nl USING (licitacion_id) WHERE u.rn = 1""") # 6. contexto del año anterior (desde las filas de r con órgano, mismas reglas salvo anulado, que se mide) x("""CREATE OR REPLACE TABLE oy AS SELECT organo_clave, anio, count(*) n, avg((num_ofertas=1)::INT) FILTER (WHERE num_ofertas>0) ou, avg(num_ofertas) FILTER (WHERE num_ofertas>0) nof_med, count(DISTINCT empresa_clave) nadj, avg(CASE WHEN pbl_lote>0 AND adj>0 THEN greatest(least(1-adj/pbl_lote,1),-0.5) END) baja_med, avg((procedimiento='Negociado_sin_publicidad')::INT) nsp FROM lt GROUP BY 1,2""") x("""CREATE OR REPLACE TABLE oyan AS SELECT o.organo_clave, r.anio, avg(r.anulado::INT) anul FROM r JOIN o1 o ON o.organo_nif IS NOT DISTINCT FROM r.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM r.organo_nombre AND o.dir3 IS NOT DISTINCT FROM r.dir3 WHERE NOT r.es_copia GROUP BY 1,2""") x("""CREATE OR REPLACE TABLE hh AS WITH s AS (SELECT cpv_division, provincia, anio, empresa_clave, sum(adj) v FROM lt WHERE adj>0 AND empresa_clave IS NOT NULL AND cpv_division IS NOT NULL AND provincia IS NOT NULL GROUP BY ALL) SELECT cpv_division, provincia, anio, sum(power(v/t,2)) hhi, count(*) nemp FROM (SELECT *, sum(v) OVER (PARTITION BY cpv_division, provincia, anio) t FROM s) GROUP BY ALL""") x("""CREATE OR REPLACE TABLE fy AS SELECT empresa_clave, anio, count(*) n FROM lt WHERE empresa_clave IS NOT NULL GROUP BY 1,2""") # 7. variables x("""CREATE OR REPLACE TABLE base AS SELECT lt.lote_key, lt.licitacion_id, lt.organo_clave, lt.empresa_clave, lt.anio, lt.cpv_division, lt.provincia, lt.num_ofertas, CASE WHEN lt.pbl_lote>0 AND lt.adj>0 THEN greatest(least(1-lt.adj/lt.pbl_lote,1),-0.5) END baja, CASE WHEN lt.pbl_lote>0 AND lt.mn>0 THEN least(lt.mn/lt.pbl_lote, 2) END min_rel, CASE WHEN lt.pbl_lote>0 AND lt.mx>0 THEN least(lt.mx/lt.pbl_lote, 2) END max_rel, CASE WHEN lt.mn>0 AND lt.mx>0 AND lt.mn<=lt.mx THEN (lt.mx-lt.mn)/lt.mx END disp, lt.an::INT ofertas_anormales, CASE WHEN lt.procedimiento='Acuerdo_marco' OR lt.rol_am IN ('basado','basado_sda') OR regexp_matches(coalesce(lt.procedimiento_native,''), '(?i)basado|derivado de acuerdo|espec[ií]fico en el marco|adjudicado en el marco|derivado de asociaci') THEN 'basado' WHEN lt.tramitacion='emergencia' OR regexp_matches(coalesce(lt.procedimiento_native,''), '(?i)emergencia') THEN 'emergencia' WHEN lt.procedimiento='Negociado_sin_publicidad' THEN 'nsp' WHEN lt.procedimiento='Abierto' AND regexp_matches(coalesce(lt.procedimiento_native,''), '(?i)simplificado') THEN 'abierto_simplificado' WHEN lt.procedimiento='Abierto' THEN 'abierto' WHEN lt.procedimiento='Restringido' THEN 'restringido' WHEN lt.procedimiento='Negociado_publicidad' THEN 'negociado_publicidad' WHEN lt.procedimiento IN ('Dialogo_competitivo','Asociacion_innovacion') THEN 'dialogo_innovacion' WHEN lt.procedimiento IS NULL THEN 'sin_procedimiento' ELSE 'otro' END procedimiento, coalesce(lt.tramitacion,'sin_dato') tramitacion, coalesce(lt.tipo_contrato,'sin_dato') tipo_contrato, CASE WHEN lt.pbl>0 THEN log10(lt.pbl) END log_pbl, CASE WHEN lt.vee>0 THEN log10(lt.vee) END log_vee, CASE WHEN lt.dur>0 AND lt.dur<=240 THEN lt.dur END duracion, CASE WHEN date_diff('day', lt.fecha_pub, lt.fecha_fin_ofertas) BETWEEN 0 AND 365 THEN date_diff('day', lt.fecha_pub, lt.fecha_fin_ofertas) END plazo_ofertas, CASE WHEN date_diff('day', lt.fecha_fin_ofertas, lt.fecha_adj) BETWEEN 0 AND 730 THEN date_diff('day', lt.fecha_fin_ofertas, lt.fecha_adj) END dias_adjudicar, lt.sara::INT sara, lt.financiacion_ue::INT financiacion_ue, lt.n_lotes_lic n_lotes, lt.n_adj_lote, lt.tipo_organo, o.n org_n_prev, o.ou org_ou_prev, o.nof_med org_nof_prev, o.nadj org_nadj_prev, o.nadj/o.n org_nadj_ratio_prev, o.baja_med org_baja_prev, o.nsp org_nsp_prev, oa.anul org_anul_prev, h.hhi hhi_prev, h.nemp merc_nemp_prev, CASE WHEN lt.anio_primero IS NOT NULL THEN greatest(lt.anio - lt.anio_primero, 0) END edad_adj, lt.es_ute::INT es_ute, lt.es_pyme::INT es_pyme, f.n adj_n_prev FROM lt LEFT JOIN oy o ON o.organo_clave=lt.organo_clave AND o.anio=lt.anio-1 LEFT JOIN oyan oa ON oa.organo_clave=lt.organo_clave AND oa.anio=lt.anio-1 LEFT JOIN hh h ON h.cpv_division=lt.cpv_division AND h.provincia=lt.provincia AND h.anio=lt.anio-1 LEFT JOIN fy f ON f.empresa_clave=lt.empresa_clave AND f.anio=lt.anio-1 WHERE lt.anio BETWEEN 2012 AND 2025""") # marcas de etiqueta por lote (todas las filas del lote) y licitaciones con V3 x("""CREATE OR REPLACE TABLE labk AS SELECT lote_key, string_agg(DISTINCT exp, ';' ORDER BY exp) exps, string_agg(DISTINCT var, ';' ORDER BY var) vars FROM lab GROUP BY 1""") x("""CREATE OR REPLACE TABLE gris_emp AS SELECT DISTINCT u.empresa_clave FROM uu u JOIN lab USING (lote_key) WHERE u.empresa_clave IS NOT NULL UNION SELECT DISTINCT nif FROM read_csv('/et/colusion/interno/emp.csv', header=true, all_varchar=true) WHERE exp IN (SELECT DISTINCT exp FROM lab)""") x(f"""CREATE OR REPLACE TABLE gris_emp2 AS SELECT empresa_clave FROM gris_emp UNION SELECT DISTINCT us.ute_clave FROM read_parquet('{D}.identidad/ute_socios.parquet') us WHERE us.socio_nif IN (SELECT empresa_clave FROM gris_emp)""") x("""CREATE OR REPLACE TABLE gris AS SELECT DISTINCT lote_key FROM uu WHERE empresa_clave IN (SELECT empresa_clave FROM gris_emp2) UNION SELECT DISTINCT lote_key FROM uu WHERE licitacion_id IN (SELECT DISTINCT u.licitacion_id FROM uu u JOIN lab USING (lote_key) WHERE lab.var='V3')""") x("""COPY (SELECT b.*, k.exps, k.vars, (g.lote_key IS NOT NULL AND k.lote_key IS NULL) gris FROM base b LEFT JOIN labk k USING (lote_key) LEFT JOIN gris g USING (lote_key)) TO '/w/interno/base.parquet' (FORMAT parquet)""") x("COPY (SELECT * FROM lab) TO '/w/interno/lab.parquet' (FORMAT parquet)") # recuentos (sin variables) L = ["# ML1 · 01 · base (recuentos, sin cruzar variables con la etiqueta)", "", f"DuckDB {duckdb.__version__}", ""] def tab(t, q): rows = x(q).fetchall(); cols = [d[0] for d in x(q).description] L.extend([f"## {t}", "", "| " + " | ".join(cols) + " |", "|" + "---|" * len(cols)]); L.extend("| " + " | ".join(str(v) for v in r) + " |" for r in rows); L.append("") tab("filas r (PLACSP adjudicación no menor 2011-2025)", "SELECT count(*) filas, count(*) FILTER (WHERE es_copia) copias, count(*) FILTER (WHERE anulado) anuladas FROM r") tab("etiquetas: lotes de U por expediente", "SELECT exp, count(DISTINCT lote_key) lotes_U FROM lab GROUP BY 1 ORDER BY 2 DESC") tab("lotes etiquetados en U por expediente y fuente de la etiqueta", "SELECT exp, src_etiqueta, count(DISTINCT lote_key) FROM lab GROUP BY 1,2 ORDER BY 1,2") tab("base", "SELECT count(*) lotes, count(*) FILTER (WHERE exps IS NOT NULL) etiquetados, count(*) FILTER (WHERE gris) gris FROM '/w/interno/base.parquet'") tab("cobertura de variables (todos los lotes de la base, sin etiqueta)", """SELECT count(num_ofertas) num_ofertas, count(baja) baja, count(min_rel) min_rel, count(disp) disp, count(ofertas_anormales) anormales, count(log_pbl) log_pbl, count(duracion) duracion, count(plazo_ofertas) plazo, count(dias_adjudicar) dias_adj, count(sara) sara, count(financiacion_ue) fue, count(org_n_prev) org_prev, count(org_ou_prev) org_ou, count(org_anul_prev) anul, count(hhi_prev) hhi, count(edad_adj) edad, count(adj_n_prev) adj_prev, count(*) n FROM '/w/interno/base.parquet'""") open('/w/salidas/01_base.md','w').write("\n".join(L)) log('fin')