"""D5b · Paso 2a (impl1). Base de la capa 0 y recuentos SIN screens (antes del sello de DEFINICIONES_D1C0.md): lotes adjudicados de unified_20261025 (todas las fuentes) con identidad de órgano y adjudicatario, vínculos entre fuentes (fila que cuenta), etiqueta del banco (D4c, D5 y v2), exclusión de controles (D1 §3) y mercado afectado de cada clúster. No calcula bajas, medianas ni percentiles. Se ejecuta en el contenedor (py.sh). Salidas: tmp/c0.duckdb (tablas b, labc, xc, mk), interno/c0_recuentos.json, salidas/30_c0_recuentos.md.""" import duckdb, json, csv, re, sys, collections, unicodedata, time, os sys.path.insert(0, '/d5/py') from config_d5 import CASOS sys.path.insert(0, '/c/py') import config_acco # d5b-c0-acco T0 = time.time() def log(*a): print(*a, round(time.time() - T0), flush=True) con = duckdb.connect('/c/tmp/c0.duckdb') con.execute("SET memory_limit='8GB'; SET threads=6; SET temp_directory='/c/tmp/duck'; SET max_temp_directory_size='60GB'; SET preserve_insertion_order=false;") con.execute("CREATE OR REPLACE TEMP VIEW u AS SELECT * FROM read_parquet('/d/unified_20261025/*/*/*.parquet', hive_partitioning=1, union_by_name=1)") I = '/d/unified_20261025.identidad/' for t in ('adjudicatarios_filas', 'organos_filas', 'organos_tipo', 'ute_socios'): con.execute(f"CREATE OR REPLACE TEMP VIEW idn_{t} AS SELECT * FROM read_parquet('{I}{t}.parquet')") con.execute("CREATE OR REPLACE TEMP VIEW vf AS SELECT * FROM read_parquet('/d/unified_20261025.vinculos/filas.parquet')") def q1(s): return con.execute(s).fetchone() def tabla(n): return q1(f"SELECT count(*) FROM information_schema.tables WHERE table_name='{n}'")[0] > 0 # ---------- 1. base de lotes adjudicados (todas las fuentes) if not tabla('b'): con.execute("""CREATE TABLE b0 AS SELECT source, id_canonical, md5(source || '|' || id_canonical) id_md5, coalesce(licitacion_id, id_canonical) lic, licitacion_id, organo_nombre, organo_nif, dir3, adjudicatario_nombre, adjudicatario_nif, adjudicatario_nif_raw, procedimiento, procedimiento_native, tramitacion, rol_am, tipo_contrato, coalesce(cpv_division, '??') cpv, num_ofertas, imp_lic_eur pbl, imp_lic_reparto rep, imp_adj_eur adj, anio_ref anio, coalesce(fecha_adj, fecha_formalizacion, fecha_pub) fref, coalesce(es_menor, false) es_menor, ccaa, regexp_replace(lower(strip_accents(coalesce(organo_nombre, ''))), '[^0-9a-z]+', ' ', 'g') org_n FROM u WHERE unidad = 'adjudicacion'""") log('b0', q1("SELECT count(*), count(DISTINCT id_canonical) FROM b0")) con.execute("""CREATE TEMP TABLE o1 AS SELECT organo_nif, organo_nombre, dir3, any_value(organo_clave) organo_clave, count(DISTINCT organo_clave) nk FROM idn_organos_filas GROUP BY ALL""") con.execute("CREATE TEMP TABLE ot AS SELECT organo_clave, any_value(tipo) tipo FROM idn_organos_tipo GROUP BY 1") con.execute("""CREATE TEMP TABLE a1 AS SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave, bool_or(coalesce(persona_fisica, false)) persona_fisica, bool_or(coalesce(nombre_ute, false)) nombre_ute, count(DISTINCT empresa_clave) nk FROM idn_adjudicatarios_filas GROUP BY ALL""") con.execute("CREATE TEMP TABLE nl AS SELECT source, lic, count(*) n_adj_lic FROM b0 WHERE NOT es_menor GROUP BY 1, 2") con.execute("CREATE TEMP TABLE v1 AS SELECT source, id_canonical, any_value(cuenta) cuenta, any_value(dup_de) dup_de, any_value(grupo_contrato) grupo FROM vf GROUP BY 1, 2") con.execute("""CREATE TABLE b AS SELECT b.*, o.organo_clave, coalesce(t.tipo, 'sin_tipo') tipo, a.empresa_clave, coalesce(a.persona_fisica, false) persona_fisica, coalesce(a.nombre_ute, false) nombre_ute, coalesce(nl.n_adj_lic, 0) n_adj_lic, CASE WHEN b.procedimiento = 'Acuerdo_marco' OR b.rol_am IN ('basado','basado_sda') OR regexp_matches(coalesce(b.procedimiento_native,''), '(?i)basado|derivado de acuerdo|espec[ií]fico en el marco|adjudicado en el marco|derivado de asociaci') THEN 'basado' WHEN b.tramitacion = 'emergencia' OR regexp_matches(coalesce(b.procedimiento_native,''), '(?i)emergencia') THEN 'emergencia' WHEN b.procedimiento = 'Negociado_sin_publicidad' THEN 'nsp' WHEN b.procedimiento = 'Abierto' AND regexp_matches(coalesce(b.procedimiento_native,''), '(?i)simplificado') THEN 'abierto_simplificado' WHEN b.procedimiento = 'Abierto' THEN 'abierto' WHEN b.procedimiento = 'Restringido' THEN 'restringido' WHEN b.procedimiento = 'Negociado_publicidad' THEN 'negociado_publicidad' WHEN b.procedimiento IN ('Dialogo_competitivo','Asociacion_innovacion') THEN 'dialogo_innovacion' WHEN b.procedimiento IS NULL THEN 'sin_procedimiento' ELSE 'otro' END clase, CASE WHEN b.pbl > 0 AND (b.rep = 'por_lote' OR nl.n_adj_lic = 1) THEN b.pbl END pbl_lote, coalesce(v.cuenta, true) cuenta, v.dup_de, coalesce(v.grupo, b.id_canonical) grupo FROM b0 b LEFT JOIN o1 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 LEFT JOIN ot t ON t.organo_clave = o.organo_clave LEFT JOIN a1 a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM b.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM b.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM b.adjudicatario_nombre LEFT JOIN nl ON nl.source = b.source AND nl.lic = b.lic LEFT JOIN v1 v ON v.source = b.source AND v.id_canonical = b.id_canonical""") log('b', q1("SELECT count(*), count(DISTINCT id_canonical), count(*) FILTER (WHERE organo_clave IS NULL), count(*) FILTER (WHERE tipo='sin_tipo'), count(*) FILTER (WHERE NOT cuenta) FROM b"), 'o1 conflictos', q1("SELECT count(*) FROM o1 WHERE nk > 1"), 'a1 conflictos', q1("SELECT count(*) FROM a1 WHERE nk > 1")) con.execute("DROP TABLE b0") CHK = dict(b=q1("SELECT count(*), count(DISTINCT id_canonical), count(DISTINCT (source, id_canonical)) FROM b")) assert CHK['b'][0] == CHK['b'][2], 'la unión multiplica filas' # ---------- 2. etiqueta del banco y traslado a la fila que cuenta (vínculos) def CL(e): return 'ACREA carreteras 2020' if e in ('ACREA S/07/2023', 'ACREA S/08/2023', 'ACREA S/07/2023+S/08/2023') else e lab = [] for r in csv.DictReader(open('/etq/casado/salidas/lotes_cartel_v2.csv')): if r['var'] in ('V2', 'V3'): lab.append((CL(r['exp']), r['var'], 'previo', r['source'], r['id_md5'])) for r in csv.DictReader(open('/d5/salidas/lotes_cartel_d5.csv')): if r['var'] in ('V2', 'V3') and r['exp'] != 'AVC 303-SAN-2019': lab.append((CL(r['exp']), r['var'], 'd5', r['source'], r['id_md5'])) if os.path.exists('/c/salidas/lotes_cartel_d5b_v2.csv'): for r in csv.DictReader(open('/c/salidas/lotes_cartel_d5b_v2.csv')): lab.append((CL(r['exp']), 'V3', 'v2', r['source'], r['id_md5'])) if os.path.exists('/c/salidas/lotes_cartel_acco.csv'): for r in csv.DictReader(open('/c/salidas/lotes_cartel_acco.csv')): lab.append((config_acco.CLUSTER.get(r['exp'], r['exp']), r['var'], 'acco', r['source'], r['id_md5'])) con.execute("CREATE OR REPLACE TABLE lab0(cl VARCHAR, var VARCHAR, banco VARCHAR, source VARCHAR, id_md5 VARCHAR)") con.executemany("INSERT INTO lab0 VALUES (?,?,?,?,?)", lab) con.execute("""CREATE OR REPLACE TABLE labr AS SELECT l.*, b.id_canonical, b.lic, b.cuenta, b.dup_de FROM lab0 l LEFT JOIN b ON b.source = l.source AND b.id_md5 = l.id_md5""") log('etiqueta', con.execute("SELECT banco, count(*), count(id_canonical), count(*) FILTER (WHERE NOT cuenta) FROM labr GROUP BY 1 ORDER BY 1").fetchall()) # fila que cuenta: la propia si cuenta; si no, la de dup_de (vínculos: sin cadenas) con.execute("""CREATE OR REPLACE TABLE labc AS SELECT DISTINCT l.cl, l.var, l.banco, c.source, c.id_canonical FROM labr l JOIN b c ON c.id_canonical = CASE WHEN l.cuenta THEN l.id_canonical ELSE l.dup_de END WHERE l.id_canonical IS NOT NULL""") log('labc', q1("SELECT count(*), count(DISTINCT id_canonical), count(*) FILTER (WHERE id_canonical IN (SELECT id_canonical FROM b WHERE NOT cuenta)) FROM labc")) # ---------- 3. exclusión de controles (D1 §3, ampliada a todos los fallos del banco) def sa(s): return ''.join(ch for ch in unicodedata.normalize('NFKD', s or '') if not unicodedata.combining(ch)).lower() def nn(s): return re.sub(r'[^0-9a-z]+', ' ', sa(s)).strip() FJ4 = r'( (s a u|s a|s l u|s l|s c a|s coop|sociedad anonima|sociedad limitada|unipersonal|sau|slu|sa|sl|sca))+$' # D4c c2_lotes.py FJ5 = r'( (s a u|s a|s l u|s l p|s l|s a l|s c a|s coop|sociedad anonima|sociedad limitada|sociedad cooperativa andaluza|unipersonal|sau|slu|sa|sl|sca|sal))+$' # D5 fe = [] for e, v in json.load(open('/etq/casado/interno/c1_fallo.json')).items(): for n in v['empresas']: n2 = re.sub(r'^(?:[A-Z]{2,5}\.\s*)?\d+\.\s*', '', n).strip() for cand in {n2, n2.split(', ')[-1]}: k = re.sub(FJ4, '', nn(cand)).strip() if len(k) >= 4: fe.append(('D4c', e, 'nombre4', k)) FALLOS5 = [(e, v, CASOS) for e, v in json.load(open('/d5/interno/fallo_d5.json')).items()] + [(e, v, config_acco.CASOS) for e, v in json.load(open('/c/interno/fallo_acco.json')).items()] for e, v, CAS in FALLOS5: if e not in CAS or CAS[e]['banco'] != 'positivo': continue for n in v['empresas']: k = re.sub(FJ5, '', nn(n)).strip() if len(k) >= 4 and not re.fullmatch(r'(?:empresa|entidad|sociedad|grupo|ute|union)', k): fe.append(('D5', e, 'nombre5', k)) for x in v['nifs']: fe.append(('D5', e, 'nif', x.upper())) p8 = list(csv.DictReader(open('/col/interno/emp.csv'))) for r in p8: fe.append(('P8', r['exp'], 'nif', r['nif'].upper())) con.execute("CREATE OR REPLACE TEMP TABLE fe(banco VARCHAR, exp VARCHAR, via VARCHAR, k VARCHAR)"); con.executemany("INSERT INTO fe VALUES (?,?,?,?)", fe) con.execute(f"""CREATE OR REPLACE TABLE femp AS SELECT DISTINCT fe.exp, a.empresa_clave FROM fe JOIN ( SELECT DISTINCT empresa_clave, upper(adjudicatario_nif) nif, trim(regexp_replace(regexp_replace(lower(strip_accents(adjudicatario_nombre)), '[^0-9a-z]+', ' ', 'g'), '{FJ4}', '')) k4, trim(regexp_replace(regexp_replace(lower(strip_accents(adjudicatario_nombre)), '[^0-9a-z]+', ' ', 'g'), '{FJ5}', '')) k5 FROM idn_adjudicatarios_filas WHERE NOT coalesce(persona_fisica, false) AND empresa_clave IS NOT NULL) a ON (fe.via = 'nombre4' AND a.k4 = fe.k) OR (fe.via = 'nombre5' AND a.k5 = fe.k) OR (fe.via = 'nif' AND (a.nif = fe.k OR a.empresa_clave = fe.k))""") log('empresas del fallo (claves)', q1("SELECT count(DISTINCT empresa_clave) FROM femp")) con.execute("CREATE OR REPLACE TEMP TABLE rx(rx VARCHAR)"); con.executemany("INSERT INTO rx VALUES (?)", [(r['rx'],) for r in p8 if r.get('rx')]) con.execute("""CREATE OR REPLACE TABLE x0 AS SELECT b.id_canonical FROM b WHERE b.empresa_clave IN (SELECT empresa_clave FROM femp) UNION SELECT b.id_canonical FROM b JOIN idn_ute_socios us ON us.ute_clave = b.empresa_clave WHERE us.socio_nif IN (SELECT empresa_clave FROM femp) UNION SELECT b.id_canonical FROM b JOIN (SELECT DISTINCT n.adjudicatario_nombre FROM (SELECT DISTINCT adjudicatario_nombre FROM b WHERE nombre_ute) n JOIN rx ON regexp_matches(lower(strip_accents(coalesce(n.adjudicatario_nombre, ''))), rx.rx)) x ON b.nombre_ute AND b.adjudicatario_nombre = x.adjudicatario_nombre UNION SELECT id_canonical FROM labr WHERE id_canonical IS NOT NULL UNION SELECT id_canonical FROM labc UNION SELECT b.id_canonical FROM b JOIN (SELECT DISTINCT source, lic FROM labr WHERE var = 'V3' AND lic IS NOT NULL UNION SELECT DISTINCT c.source, c.lic FROM labc l JOIN b c USING (source, id_canonical) WHERE l.var = 'V3') v USING (source, lic)""") # traslado por grupo de vínculos: si una fila del grupo está excluida, el grupo entero con.execute("""CREATE OR REPLACE TABLE xc AS SELECT DISTINCT b.id_canonical FROM b WHERE b.grupo IN (SELECT DISTINCT b2.grupo FROM b b2 JOIN x0 USING (id_canonical))""") log('exclusión', q1("SELECT (SELECT count(*) FROM x0), (SELECT count(*) FROM xc)")) # ---------- 4. mercado afectado por clúster (el que usó cada banco para su V2) MERC_P8 = {'S/0016/21': "b.cpv IN ('15','03')", 'S/0013/19': "b.tipo = 'age' AND regexp_matches(lower(strip_accents(coalesce(b.organo_nombre,''))), 'carreteras')", 'S/0008/21': ("b.tipo = 'age' AND regexp_matches(lower(strip_accents(coalesce(b.organo_nombre,''))), " "'defensa|ejercito|jaemale|mando de apoyo logistico|parque y centro|armada|intendencia|" "jefatura de asuntos economicos|direccion general de armamento|mando de ingenieros|acuartelamiento|" "base aerea|regimiento|brigada')")} ALIAS_D4C = {'ACCO 101/2018': [r'ferrocarril metropolita|metro de barcelona|transports de barcelona|\btmb\b'], 'CGC S 8/2021': [r'lugo', r'sergas|servizo galego de saude|area sanitaria|xerencia'], 'S/0011/19': [r'educacion', r'cantabria'], 'S/DC/0512/14': [r'educacion', r'balears|baleares'], 'S/0626/18': [r'meteorolog'], 'SAMUR/01/24': [r'murcia']} # alias de D5 (idéntico a casado_d5.py: órganos de onames que casan todas las regex y, si hay ccaa, esa ccaa o su fuente) con.execute("ATTACH '/c/tmp/d5b.duckdb' AS e (READ_ONLY)") FUENTE_CCAA = {'AN': 'ANDALUCIA', 'GA': 'GALICIA', 'PV': 'EUSKADI', 'CB': None, 'CN': 'CANARIAS'} org_norm, org_ccaa = {}, collections.defaultdict(set) for n, cc, src in con.execute("SELECT organo_nombre, ccaa, source FROM e.onames").fetchall(): org_norm.setdefault(n, nn(n)); org_ccaa[n].add(cc); org_ccaa[n].add('src:' + src) def alias_d5(k): cfg = CASOS[k] if k in CASOS else config_acco.CASOS[k] if not cfg.get('alias'): return None rxs = [re.compile(x) for x in cfg['alias']]; cc = cfg.get('ccaa'); fs = FUENTE_CCAA.get(cc) if cc else None return sorted(n for n, v in org_norm.items() if all(r.search(v) for r in rxs) and (not cc or cc in org_ccaa[n] or (fs and 'src:' + fs in org_ccaa[n]))) clusters = sorted({x[0] for x in lab}) origen = {} for x in lab: origen.setdefault(x[0], set()).add(x[2]) con.execute("CREATE OR REPLACE TABLE mk(cl VARCHAR, id_canonical VARCHAR)") mercado = {} for c in clusters: if c in MERC_P8: con.execute(f"INSERT INTO mk SELECT ?, b.id_canonical FROM b WHERE {MERC_P8[c]}", [c]); mercado[c] = 'P8 c06 MERCADO'; continue if 'previo' in origen[c]: # D4c: órganos normalizados y CPV de sus V3 (origen D4c); si no hay, alias de D4c v3 = con.execute("""SELECT DISTINCT b.org_n, b.cpv FROM labr l JOIN b ON b.id_canonical = l.id_canonical WHERE l.cl = ? AND l.var = 'V3' AND l.banco = 'previo'""", [c]).fetchall() if v3: orgs = sorted({o for o, _ in v3}); cpvs = sorted({p for _, p in v3}) con.execute("INSERT INTO mk SELECT ?, b.id_canonical FROM b WHERE b.org_n IN (SELECT unnest(?::VARCHAR[])) AND b.cpv IN (SELECT unnest(?::VARCHAR[]))", [c, orgs, cpvs]) mercado[c] = f'D4c órganos × CPV de V3 ({len(orgs)} × {len(cpvs)})' elif c in ALIAS_D4C: cond = ' AND '.join(f"regexp_matches(b.org_n, '{x}')" for x in ALIAS_D4C[c]) con.execute(f"INSERT INTO mk SELECT ?, b.id_canonical FROM b WHERE {cond}", [c]); mercado[c] = 'D4c alias' else: mercado[c] = 'sin mercado' continue # D5 / ACCO: órganos (nombre) y CPV de sus V3 (de D5 o de la ACCO; nunca de la v2: DEFINICIONES_PREVIAS §3); si no hay, alias de config_d5 / config_acco bk = 'acco' if 'acco' in origen[c] else 'd5' v3 = con.execute("""SELECT DISTINCT b.organo_nombre, b.cpv FROM labr l JOIN b ON b.id_canonical = l.id_canonical WHERE l.cl = ? AND l.var = 'V3' AND l.banco = ?""", [c, bk]).fetchall() if v3: orgs = sorted({o for o, _ in v3 if o}); cpvs = sorted({p for _, p in v3}) con.execute("INSERT INTO mk SELECT ?, b.id_canonical FROM b WHERE b.organo_nombre IN (SELECT unnest(?::VARCHAR[])) AND b.cpv IN (SELECT unnest(?::VARCHAR[]))", [c, orgs, cpvs]) mercado[c] = f'D5 órganos × CPV de V3 ({len(orgs)} × {len(cpvs)})' else: k = 'ACREA S/07/2023' if c == 'ACREA carreteras 2020' else ('ACCO 100/2018' if c.startswith('ACCO Aerobús') else c) al = alias_d5(k) if (k in CASOS or k in config_acco.CASOS) else None if al: con.execute("INSERT INTO mk SELECT ?, b.id_canonical FROM b WHERE b.organo_nombre IN (SELECT unnest(?::VARCHAR[]))", [c, al]); mercado[c] = f'D5 alias ({len(al)} órganos)' else: mercado[c] = 'sin mercado' log('mercados', mercado) # ---------- 5. población P0 (sin screens) y recuentos por clúster y año con.execute("""CREATE OR REPLACE TABLE p0 AS SELECT b.* FROM b WHERE b.cuenta AND NOT b.es_menor AND b.tipo_contrato IN ('Obras','Servicios','Suministros') AND NOT b.persona_fisica AND coalesce(b.num_ofertas, 0) >= 3 AND NOT (b.source = 'CATALUNYA_PSCP' AND b.procedimiento IS NULL) AND coalesce(b.adj > 0, false) AND b.anio IS NOT NULL""") log('p0', q1("SELECT count(*), count(pbl_lote), count(*) FILTER (WHERE pbl_lote > 0 AND adj / pbl_lote <= 1.25) FROM p0")) # embudo de las positivas (filas que cuentan) emb = con.execute("""SELECT l.cl, count(DISTINCT l.id_canonical) lotes, count(DISTINCT l.id_canonical) FILTER (WHERE b.es_menor) menor, count(DISTINCT l.id_canonical) FILTER (WHERE NOT b.es_menor AND coalesce(b.tipo_contrato,'') NOT IN ('Obras','Servicios','Suministros')) tipo, count(DISTINCT l.id_canonical) FILTER (WHERE b.persona_fisica) pf, count(DISTINCT l.id_canonical) FILTER (WHERE coalesce(b.num_ofertas, 0) < 3) menos3, count(DISTINCT l.id_canonical) FILTER (WHERE p.id_canonical IS NOT NULL) en_p0, count(DISTINCT l.id_canonical) FILTER (WHERE p.pbl_lote > 0 AND p.adj / p.pbl_lote <= 1.25) p0_pbl FROM labc l JOIN b USING (id_canonical) LEFT JOIN p0 p USING (id_canonical) GROUP BY 1 ORDER BY 2 DESC""").fetchall() for banco, filtro in (('previo', "banco = 'previo'"), ('ampliado', "true")): con.execute(f"""CREATE OR REPLACE TEMP TABLE pos_{banco} AS SELECT DISTINCT cl, id_canonical FROM labc WHERE {filtro}""") rec = {} for banco in ('previo', 'ampliado'): rows = con.execute(f""" WITH pos AS (SELECT p.*, x.cl FROM p0 p JOIN pos_{banco} x USING (id_canonical)), an AS (SELECT DISTINCT cl, anio FROM pos), ctl AS (SELECT p.*, mk.cl FROM p0 p JOIN mk USING (id_canonical) JOIN an ON an.cl = mk.cl AND an.anio = p.anio WHERE p.id_canonical NOT IN (SELECT id_canonical FROM xc)) SELECT cl, (SELECT count(*) FROM pos WHERE pos.cl = a.cl) pos_lotes, (SELECT count(DISTINCT source||lic) FROM pos WHERE pos.cl = a.cl) pos_lics, (SELECT count(DISTINCT coalesce(organo_clave, 'n:'||coalesce(organo_nombre,''))) FROM pos WHERE pos.cl = a.cl) pos_org, (SELECT count(*) FROM pos WHERE pos.cl = a.cl AND pbl_lote > 0 AND adj / pbl_lote <= 1.25) pos_pbl, (SELECT count(DISTINCT source||lic) FROM pos WHERE pos.cl = a.cl AND pbl_lote > 0 AND adj / pbl_lote <= 1.25) pos_pbl_lics, (SELECT count(*) FROM ctl WHERE ctl.cl = a.cl) ctl_lotes, (SELECT count(*) FROM ctl WHERE ctl.cl = a.cl AND pbl_lote > 0 AND adj / pbl_lote <= 1.25) ctl_pbl, (SELECT string_agg(DISTINCT anio::VARCHAR, ',' ORDER BY anio::VARCHAR) FROM pos WHERE pos.cl = a.cl) anios, (SELECT string_agg(DISTINCT source, ',') FROM pos WHERE pos.cl = a.cl) fuentes FROM (SELECT DISTINCT cl FROM pos) a ORDER BY pos_pbl_lics DESC""").fetchall() rec[banco] = rows log(banco, len(rows)) AUT = "CASE WHEN l.cl LIKE 'ACREA%' THEN 'ACREA' WHEN l.cl LIKE 'CGC%' THEN 'CGC' WHEN l.cl LIKE 'AVC%' THEN 'AVC' WHEN l.cl LIKE 'ACCO%' THEN 'ACCO' ELSE 'CNMC' END" ad = {} for nombre, filtro in (('antes (D4c + D5)', "banco IN ('previo','d5')"), ('después (+ v2 + ACCO)', 'true')): ad[nombre] = con.execute(f"""WITH x AS (SELECT DISTINCT {AUT} aut, l.cl, b.source, b.id_canonical, b.lic, (b.num_ofertas IS NOT NULL AND NOT (b.source = 'CATALUNYA_PSCP' AND b.procedimiento IS NULL)) con_nof, (b.num_ofertas IS NOT NULL AND NOT (b.source = 'CATALUNYA_PSCP' AND b.procedimiento IS NULL) AND coalesce(b.pbl > 0 AND b.adj > 0, false)) capa0 FROM labr l JOIN b ON b.id_canonical = l.id_canonical WHERE {filtro}), k AS (SELECT aut, cl, count(DISTINCT source || lic) FILTER (WHERE capa0) c0l FROM x GROUP BY 1, 2) SELECT x.aut, count(DISTINCT x.source || x.id_canonical) lotes, count(DISTINCT x.source || x.lic) licit, count(DISTINCT x.source || x.id_canonical) FILTER (WHERE con_nof) con_nof, count(DISTINCT x.source || x.id_canonical) FILTER (WHERE capa0) c0_lotes, count(DISTINCT x.source || x.lic) FILTER (WHERE capa0) c0_licit, count(DISTINCT x.cl) clusters, (SELECT count(*) FROM k WHERE k.aut = x.aut AND k.c0l >= 10) cl_c0_10 FROM x GROUP BY 1 ORDER BY 2 DESC""").fetchall() json.dump(dict(chk=CHK, mercado=mercado, embudo=emb, recuentos=rec, antes_despues=ad), open('/c/interno/c0_recuentos.json', 'w'), ensure_ascii=False, indent=1, default=str) L = ['# D5b · Capa 0: recuentos antes del sello (sin screens, sin empresas)\n', 'P0 = lote adjudicado que cuenta (vínculos), no menor, obras/servicios/suministros, sin persona física, ≥ 3 ofertas (PSCP con procedimiento), adjudicación > 0.', '«con PBL» = PBL del lote fiable (D1) y adjudicación/PBL ≤ 1,25 (condición necesaria de BAJA_COND; la celda ≥ 10 y el mercado ≥ 20 se ven tras el sello).', 'Controles: mercado afectado del clúster, años con positivas, fuera de la exclusión.\n', '## Embudo de las positivas (filas que cuentan)\n', '| Clúster | Lotes | Menor | Tipo | Persona física | < 3 ofertas | En P0 | En P0 con PBL |', '|---|--:|--:|--:|--:|--:|--:|--:|'] for x in emb: L.append('| ' + ' | '.join(map(str, x)) + ' |') for banco in ('previo', 'ampliado'): L += [f'\n## Banco {banco}\n', '| Clúster | Mercado de control | Pos. lotes | Pos. licit. | Pos. órganos | Pos. con PBL | Licit. con PBL | Controles | Controles con PBL | Años | Fuentes |', '|---|---|--:|--:|--:|--:|--:|--:|--:|---|---|'] for x in rec[banco]: L.append(f'| {x[0]} | {mercado.get(x[0])} | ' + ' | '.join('' if v is None else str(v) for v in x[1:]) + ' |') L.append(f"\nClústeres con ≥ 10 licitaciones positivas con PBL: {sum(1 for x in rec[banco] if x[5] >= 10)}") L += ['\n## Antes y después por autoridad (filas originales del banco; capa 0 = nº de ofertas fiable + PBL > 0 + adjudicación > 0, como D5)\n', '| Momento | Autoridad | Lotes | Licitaciones | Con nº ofertas | Capa 0 lotes | Capa 0 licit. | Clústeres | Clústeres con ≥ 10 licit. en capa 0 |', '|---|---|--:|--:|--:|--:|--:|--:|--:|'] for nombre, rows in ad.items(): for x in rows: L.append(f'| {nombre} | ' + ' | '.join(map(str, x)) + ' |') open('/c/salidas/30_c0_recuentos.md', 'w').write('\n'.join(L) + '\n') log('fin')