"""MK1 · 01 · base de lotes PLACSP (universo NOT es_copia) con nº de ofertas, baja y adjudicatarios descompuestos (UTE -> socios). Solo construye y cuenta; no cruza ninguna medida con la etiqueta. Escribe interno/{lotes,ganadores,lab,gris}.parquet y salidas/01_base.md.""" import duckdb, time T0 = time.time() D = '/d/unified_20261018' c = duckdb.connect('/w/tmp/mk1.duckdb'); 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.0f}s]", *a, flush=True) PROC = "('Abierto','Restringido','Negociado_publicidad','Dialogo_competitivo','Asociacion_innovacion')" 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.tipo_contrato, u.cpv_division, u.num_ofertas, u.imp_lic_eur pbl, u.imp_lic_reparto rep, u.imp_adj_eur adj, u.anio_ref::INTEGER anio, u.fecha_adj, u.fecha_pub, u.url_origen, (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.source='PLACSP' AND u.tabla_origen IN ('resultados_licitaciones','resultados_agregacion') AND u.unidad='adjudicacion' AND u.es_contrato AND u.vigente AND NOT coalesce(u.es_menor, FALSE) AND u.anio_ref BETWEEN 2012 AND 2025 AND u.procedimiento IN {PROC} AND coalesce(u.rol_am,'') NOT IN ('basado','basado_sda') AND coalesce(u.tramitacion,'') <> 'emergencia'""") log('r', x("SELECT count(*), count(*) FILTER (WHERE es_copia), count(*) FILTER (WHERE anulado) FROM r").fetchone()) 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')") assert x("SELECT count(*) FROM (SELECT organo_nif, organo_nombre, dir3 FROM o1 GROUP BY ALL HAVING count(*)>1)").fetchone()[0] == 0 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 adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave, bool_or(coalesce(nombre_ute,FALSE)) es_ute FROM read_parquet('{D}.identidad/adjudicatarios_filas.parquet') GROUP BY ALL""") x("""CREATE OR REPLACE TABLE ri AS SELECT r.*, o.organo_clave, coalesce(ot.tipo,'sin_tipo') tipo_organo, a.empresa_clave, coalesce(a.es_ute,FALSE) es_ute 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""") n_r, n_ri = x("SELECT (SELECT count(*) FROM r), (SELECT count(*) FROM ri)").fetchone(); assert n_r == n_ri, (n_r, n_ri) # universo: no copia, no anulado, con órgano x("""CREATE OR REPLACE TABLE uu AS SELECT *, CASE WHEN tabla_origen='resultados_licitaciones' THEN 'p' ELSE 'a' END feed FROM ri WHERE NOT es_copia AND NOT anulado AND organo_clave IS NOT NULL""") # lotes en los dos feeds: se queda el propio (regla OU1) x("""CREATE OR REPLACE TABLE uu2 AS SELECT * FROM uu WHERE NOT (feed='a' AND (licitacion_id, lote_id) IN (SELECT (licitacion_id, lote_id) FROM uu WHERE feed='p'))""") dup = x("SELECT (SELECT count(*) FROM uu) - (SELECT count(*) FROM uu2)").fetchone()[0]; log('filas quitadas por doble feed', dup) x("CREATE OR REPLACE TABLE uu3 AS SELECT *, feed || '|' || licitacion_id || '|' || lote_id lote_key FROM uu2") x("""CREATE OR REPLACE TABLE nl AS SELECT licitacion_id, count(*) n_adj_lic FROM uu3 GROUP BY 1""") x("""CREATE OR REPLACE TABLE lotes AS SELECT u.lote_key, u.licitacion_id, u.lote_id, u.feed, u.id_canonical id_min, u.organo_clave, u.tipo_organo, coalesce(u.cpv_division,'??') cpv_div, u.tipo_contrato, u.anio, u.fecha_adj, u.fecha_pub, u.url_origen, k.nof, k.nof_min, k.n_filas, CASE WHEN k.n_filas=1 AND u.pbl > 0 AND u.adj > 0 AND (u.rep='por_lote' OR nl.n_adj_lic=1) THEN greatest(least(1-u.adj/u.pbl,1),-0.5) END baja FROM (SELECT *, row_number() OVER (PARTITION BY lote_key ORDER BY id_canonical) rn FROM uu3) u JOIN (SELECT lote_key, max(num_ofertas) nof, min(num_ofertas) nof_min, count(*) n_filas FROM uu3 GROUP BY 1) k USING (lote_key) LEFT JOIN nl USING (licitacion_id) WHERE u.rn = 1""") # ganadores descompuestos: peso 1/filas por fila; UTE con socios conocidos -> 1/socios a cada socio x(f"""CREATE OR REPLACE TABLE us AS SELECT ute_clave, socio_nif FROM read_parquet('{D}.identidad/ute_socios.parquet') WHERE socio_nif IS NOT NULL GROUP BY ALL""") x("""CREATE OR REPLACE TABLE g0 AS SELECT lote_key, id_canonical, coalesce(empresa_clave, 'X:' || nullif(trim(adjudicatario_nif),''), 'Y:' || nullif(upper(trim(adjudicatario_nombre)),'')) empresa_clave, es_ute, 1.0/count(*) OVER (PARTITION BY lote_key) w FROM uu3""") x("""CREATE OR REPLACE TABLE ganadores AS WITH d AS (SELECT g.lote_key, coalesce(us.socio_nif, g.empresa_clave) ent, g.w / count(*) OVER (PARTITION BY g.lote_key, g.id_canonical) w, (us.socio_nif IS NOT NULL) via_ute FROM g0 g LEFT JOIN us ON us.ute_clave = g.empresa_clave) SELECT lote_key, ent, sum(w) w, bool_or(via_ute) via_ute FROM d GROUP BY 1,2""") x("""CREATE OR REPLACE TABLE lotes_sin_ganador AS SELECT DISTINCT lote_key FROM g0 WHERE empresa_clave IS NULL""") # etiquetas (como ML1): ids PLACSP directos; TED y PLACSP-no-cuenta -> original PLACSP por 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, CASE WHEN l.source='PLACSP' AND coalesce(v.dup_de,'') LIKE 'PLACSP:%' THEN v.dup_de WHEN l.source='PLACSP' THEN l.id_canonical WHEN coalesce(v.dup_de,'') LIKE 'PLACSP:%' THEN v.dup_de END id_placsp FROM lab0 l LEFT JOIN vted v ON v.source=l.source AND v.id_canonical=l.id_canonical""") x("CREATE OR REPLACE TABLE lab AS SELECT DISTINCT l.exp, l.var, uu3.lote_key FROM lab1 l JOIN uu3 ON uu3.id_canonical = l.id_placsp") # gris: lotes de empresas que ganaron algún lote etiquetado (y de sus UTE) o de licitaciones con un lote V3; fuera de los controles x("""CREATE OR REPLACE TABLE gemp AS SELECT DISTINCT g.ent FROM ganadores g JOIN lab USING (lote_key) WHERE g.ent 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("""CREATE OR REPLACE TABLE gris AS SELECT DISTINCT lote_key FROM ganadores WHERE ent IN (SELECT ent FROM gemp) UNION SELECT DISTINCT lote_key FROM uu3 WHERE licitacion_id IN (SELECT DISTINCT u.licitacion_id FROM uu3 u JOIN lab USING (lote_key) WHERE lab.var='V3')""") for t in ['lotes', 'ganadores', 'lab', 'gris', 'lotes_sin_ganador']: x(f"COPY {t} TO '/w/interno/{t}.parquet' (FORMAT parquet)") L = ["# MK1 · 01 · base (recuentos; sin cruzar medidas 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 PLACSP (adjudicación no menor, procedimientos U, 2012-2025)", "SELECT count(*) filas, count(*) FILTER (WHERE es_copia) copias, count(*) FILTER (WHERE anulado) anuladas, count(*) FILTER (WHERE organo_clave IS NULL) sin_organo FROM ri") tab("lotes del universo", f"SELECT count(*) lotes, count(*) FILTER (WHERE nof>=1) con_ofertas, count(*) FILTER (WHERE nof_min<>nof) incoherentes, count(baja) con_baja, count(*) FILTER (WHERE n_filas>1) varias_filas, {dup} filas_doble_feed FROM lotes") tab("lotes por año", "SELECT anio, count(*) lotes, count(*) FILTER (WHERE nof>=1) con_ofertas, count(baja) con_baja FROM lotes GROUP BY 1 ORDER BY 1") tab("origen de la clave de adjudicatario (filas)", "SELECT left(empresa_clave,2)='X:' por_nif, left(empresa_clave,2)='Y:' por_nombre, empresa_clave IS NULL sin, count(*) FROM g0 GROUP BY ALL ORDER BY 4 DESC") tab("ganadores", """SELECT count(DISTINCT lote_key) lotes_con_ganador, count(*) filas_ent, count(DISTINCT ent) entidades, count(*) FILTER (WHERE via_ute) filas_socio_ute, (SELECT count(*) FROM lotes_sin_ganador) lotes_con_fila_sin_empresa FROM ganadores WHERE ent IS NOT NULL""") tab("UTE: filas con nombre de UTE y si su clave se descompone", "SELECT es_ute, count(*) filas, count(*) FILTER (WHERE empresa_clave IN (SELECT ute_clave FROM us)) descompuestas FROM uu3 GROUP BY 1") tab("etiquetas: lotes del universo por expediente y variante", "SELECT exp, var, count(DISTINCT lote_key) lotes FROM lab GROUP BY 1,2 ORDER BY 1,2") tab("gris", "SELECT count(*) lotes_gris FROM gris") tab("unidades órgano × división × año (para dimensionar)", "SELECT count(*) celdas, count(*) FILTER (WHERE n>=5) n5 FROM (SELECT organo_clave, cpv_div, anio, count(*) n FROM lotes GROUP BY ALL)") open('/w/salidas/01_base.md', 'w').write("\n".join(L)); log('fin')