"""UTE1 · 02 · PASO 0 (sin etiquetas): base de lotes (universo NOT es_copia, no menores, 2012-2025), provincia, importe, marca UTE, miembros; descriptivos por año, mercado y fuente. Escribe tabla `lot` y `lot_emp` en /w/tmp/ute1.duckdb y /w/salidas/02_paso0.txt (solo agregados).""" import sys, time, duckdb sys.path.insert(0, '/w/py') from comun import NUTS_INE, NOMBRE_INE T0 = time.time() con = duckdb.connect('/w/tmp/ute1.duckdb'); x = con.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") I = '/d/unified_20261018.identidad/' OUT = open('/w/salidas/02_paso0.txt', 'w') def log(*a): s = f"[{time.time()-T0:6.1f}s] " + ' '.join(str(v) for v in a); print(s, flush=True); OUT.write(s + '\n') def tabla(titulo, q): r = x(q); cols = [d[0] for d in r.description]; rows = r.fetchall() OUT.write(f"\n## {titulo}\n" + ' | '.join(cols) + '\n'); print('\n##', titulo, cols) for row in rows: OUT.write(' | '.join(str(v) for v in row) + '\n'); print(row) x("CREATE OR REPLACE TEMP TABLE nuts_ine(n VARCHAR, p VARCHAR)"); con.executemany("INSERT INTO nuts_ine VALUES (?,?)", list(NUTS_INE.items())) x("CREATE OR REPLACE TEMP TABLE nom_ine(n VARCHAR, p VARCHAR)"); con.executemany("INSERT INTO nom_ine VALUES (?,?)", list(NOMBRE_INE.items())) # órgano x(f"""CREATE OR REPLACE TEMP TABLE b3 AS SELECT b.*, o.organo_clave FROM b2 b LEFT JOIN (SELECT organo_nif, organo_nombre, dir3, any_value(organo_clave) organo_clave FROM read_parquet('{I}organos_filas.parquet') GROUP BY ALL) o ON o.organo_nif IS NOT DISTINCT FROM b.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM b.organo_nombre AND o.dir3 IS NOT DISTINCT FROM b.dir3 WHERE NOT b.es_copia AND NOT b.es_menor AND NOT b.es_menor_dudoso""") x("""CREATE OR REPLACE TEMP TABLE b4 AS SELECT b.*, coalesce(ni.p, nm.p) prov_fila FROM b3 b LEFT JOIN nuts_ine ni ON ni.n = left(b.nuts, 5) LEFT JOIN nom_ine nm ON nm.n = trim(lower(strip_accents(b.provincia)))""") # provincia modal del órgano (>= 5 filas con provincia y cuota >= 0,8) x("""CREATE OR REPLACE TEMP TABLE op AS WITH a AS (SELECT organo_clave, prov_fila p, count(*) n FROM b4 WHERE organo_clave IS NOT NULL AND prov_fila IS NOT NULL GROUP BY 1, 2), b AS (SELECT *, sum(n) OVER (PARTITION BY organo_clave) t, row_number() OVER (PARTITION BY organo_clave ORDER BY n DESC, p) r FROM a) SELECT organo_clave, p FROM b WHERE r = 1 AND t >= 5 AND n >= 0.8 * t""") x("""CREATE OR REPLACE TABLE lot AS SELECT b.source, b.id_canonical, b.source || '|' || b.lic0 lic, b.source || '|' || b.lic0 || '|' || coalesce(nullif(split_part(b.id_canonical, '|', 2), ''), '-') lote_k, b.organo_clave, coalesce(b.cpv2, '??') cpv2, b.tipo_contrato, b.anio, coalesce(b.prov_fila, op.p, CASE b.source WHEN 'MADRID_AYTO' THEN '28' END) prov, CASE WHEN b.prov_fila IS NOT NULL THEN 'fila' WHEN op.p IS NOT NULL THEN 'organo' WHEN b.source='MADRID_AYTO' THEN 'fuente' END prov_via, b.rol_am, b.num_ofertas, CASE WHEN b.rol_am IN ('acuerdo','sda') OR abs(b.imp_web) > 1e9 THEN NULL ELSE b.imp_web END imp, CASE WHEN b.rol_am IN ('acuerdo','sda') OR abs(b.imp_sin_iva) > 1e9 THEN NULL ELSE b.imp_sin_iva END imp_sin_iva, b.adjudicatario_nombre, b.empresa_clave, b.empresa_tipo, coalesce(b.persona_fisica, FALSE) pf, (d.ute_clave IS NOT NULL) es_ute FROM b4 b LEFT JOIN op ON op.organo_clave = b.organo_clave LEFT JOIN (SELECT DISTINCT ute_clave FROM ute_def) d ON d.ute_clave = b.empresa_clave""") log('lot', x("SELECT count(*), count(DISTINCT lote_k), sum(es_ute::INT), count(DISTINCT lic) FROM lot").fetchone()) # miembros válidos de cada UTE: <= 6 miembros (más son listas de adjudicatarias) x("""CREATE OR REPLACE TABLE ute_m AS SELECT m.* FROM ute_miembros m JOIN (SELECT ute_clave, count(*) n FROM ute_miembros GROUP BY 1) c USING (ute_clave) WHERE c.n <= 6""") x("""CREATE OR REPLACE TABLE ute_n AS SELECT d.ute_clave, coalesce(c.n, 0) n_miembros, coalesce(c.n_pub, 0) n_pub FROM (SELECT DISTINCT ute_clave FROM ute_def) d LEFT JOIN (SELECT ute_clave, count(*) n, sum(pub::INT) n_pub FROM ute_miembros GROUP BY 1) c USING (ute_clave)""") # ---- descriptivos Q = """count(*) lotes, sum(es_ute::INT) lotes_ute, round(100*avg(es_ute::INT),2) pct_lotes_ute, round(sum(imp)/1e6) imp_meur, round(sum(imp) FILTER (WHERE es_ute)/1e6) imp_ute_meur, round(100*sum(imp) FILTER (WHERE es_ute)/sum(imp),1) pct_imp_ute, round(100*avg((n.n_miembros>=2)::INT) FILTER (WHERE es_ute),1) pct_ute_2miembros, round(100*avg((n.n_pub>=1)::INT) FILTER (WHERE es_ute),1) pct_ute_con_pub""" J = "FROM lot l LEFT JOIN ute_n n ON n.ute_clave = l.empresa_clave" tabla('Por año', f"SELECT anio, {Q} {J} GROUP BY 1 ORDER BY 1") tabla('Por fuente', f"SELECT source, {Q} {J} GROUP BY 1 ORDER BY 2 DESC") tabla('Por tipo de contrato', f"SELECT tipo_contrato, {Q} {J} GROUP BY 1 ORDER BY 2 DESC") tabla('Por mercado (cpv2), 25 de más importe en UTE', f"SELECT cpv2, {Q} {J} GROUP BY 1 ORDER BY imp_ute_meur DESC NULLS LAST LIMIT 25") tabla('Total', f"SELECT 'total', {Q} {J}") tabla('Provincia: vía', "SELECT prov_via, count(*), round(100*count(*)/sum(count(*)) OVER (),1) pct FROM lot GROUP BY 1 ORDER BY 2 DESC") tabla('UTE: miembros resueltos (claves UTE con algún lote en el universo)', """SELECT least(n.n_miembros, 7) miembros, count(DISTINCT n.ute_clave) claves, count(*) lotes, round(sum(l.imp)/1e6) imp_meur FROM lot l JOIN ute_n n ON n.ute_clave = l.empresa_clave GROUP BY 1 ORDER BY 1""") tabla('UTE: vía de la marca (lotes)', """SELECT d.empresa_via, count(*) FROM lot l JOIN (SELECT DISTINCT ute_clave, first(empresa_via) empresa_via FROM ute_def GROUP BY 1) d ON d.ute_clave = l.empresa_clave GROUP BY 1 ORDER BY 2 DESC""") # lotes con una UTE y una fila de uno de sus miembros en el mismo lote (la PLACSP publica la adjudicación al socio y la formalización a la UTE) x("""CREATE OR REPLACE TABLE dup_socio AS SELECT DISTINCT a.lote_k, a.id_canonical id_socio FROM lot a JOIN lot b ON a.lote_k = b.lote_k AND b.es_ute AND NOT a.es_ute JOIN ute_m m ON m.ute_clave = b.empresa_clave AND m.emp = a.empresa_clave""") tabla('Fila de socio en el mismo lote que su UTE (doble conteo de origen)', """SELECT l.source, count(*) filas_socio, round(sum(l.imp)/1e6) imp_meur FROM lot l JOIN dup_socio d ON d.id_socio = l.id_canonical AND d.lote_k = l.lote_k GROUP BY 1 ORDER BY 2 DESC""") # varias_empresas sin palabra UTE (posibles UTE no marcadas) tabla('Nombre con varias empresas sin marca UTE (posible UTE no marcada)', """SELECT count(*), round(sum(imp)/1e6) FROM lot l JOIN read_parquet('/d/unified_20261018.identidad/adjudicatarios_filas.parquet') a ON a.adjudicatario_nombre = l.adjudicatario_nombre AND a.empresa_clave = l.empresa_clave WHERE NOT l.es_ute AND a.varias_empresas AND NOT coalesce(a.nombre_ute,FALSE)""") log('fin'); OUT.close()