"""REC3-OU: segunda implementación, sin el módulo del ETL: todo en SQL, y el anuncio DOC_CN leído partiendo el fichero por entradas, sin regex. Compara por órgano con el CSV.""" import sys, csv, gzip, json, os, zipfile, time, collections; sys.path.insert(0, "/lect") from comun import * t0 = time.time() c = con(); x = c.execute x("SET memory_limit='3500MB'") D = "/data/unified_20261103"; ATOM = "/n/salida/licitaciones_completo_2012_2026.parquet" x(f"""CREATE TABLE l AS SELECT row_number() OVER () rid, p.feed, p.licitacion_id lid, p.organo_clave oc, p.unica, u.tipo_contrato tc, u.objeto ob, u.expediente ex, u.organo_nif nif, u.organo_nombre onom, regexp_replace(strip_accents(lower(coalesce(u.objeto, ''))), '[^\\x00-\\x7F]', '', 'g') t, regexp_replace(strip_accents(lower(coalesce(u.expediente, ''))), '[^\\x00-\\x7F]', '', 'g') te, upper(coalesce(u.objeto, '')) obu, upper(trim(coalesce(u.expediente, ''))) exu FROM read_parquet('/ou/lotes_p.parquet') p LEFT JOIN read_parquet('{D}/source=PLACSP/**/*.parquet', hive_partitioning=1, union_by_name=1) u ON u.id_canonical = p.id_min WHERE p.anio = 2024""") x("""ALTER TABLE l ADD COLUMN ft VARCHAR""") x(r"""UPDATE l SET ft = CASE WHEN tc = 'Patrimonial' OR regexp_matches(t, '(^|[^a-z])(venta|ventas|enajenacion)([^a-z]|$)|adquisicion de (un |una |el |la |los |las )?(inmueble|finca|solar|parcela|vivienda|local)') THEN 'F1' WHEN contains(t || ' ' || te, 'prorrog') THEN 'F2' WHEN contains(t, 'patrocin') THEN 'F3' WHEN contains(t, 'exclusiv') THEN 'F4' WHEN regexp_matches(t, 'emergenc|covid|coronavirus|\bdana\b|paiporta|riada|inundaci|nevada|filomena|temporal de') THEN 'F5' END""") print("texto", x("SELECT ft, count(*) FROM l WHERE ft IS NOT NULL GROUP BY 1 ORDER BY 1").fetchall(), flush=True) x("CREATE TABLE tod AS SELECT lid, bool_and(unica) todas FROM l GROUP BY 1") x(f"""CREATE TABLE ted AS SELECT organo_nif n, upper(trim(expediente)) e, num_ofertas o FROM read_parquet('{D}/source=TED/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE expediente IS NOT NULL AND num_ofertas IS NOT NULL AND year(coalesce(fecha_pub, fecha_adj)) BETWEEN 2023 AND 2025 AND upper(trim(expediente)) <> ''""") x(r"""CREATE TABLE tcod AS SELECT e, max(o) mx FROM ted WHERE regexp_matches(e, '^[A-Z0-9][A-Z0-9/\-. ]{6,}$') AND regexp_matches(e, '[0-9]') AND regexp_matches(e, '[-/]') GROUP BY 1""") x("CREATE TABLE cand AS SELECT l.rid, l.lid, l.oc, l.nif, l.obu, l.exu, l.feed, l.onom FROM l JOIN tod USING (lid) WHERE l.ft IS NULL AND l.unica AND tod.todas") x("""CREATE TABLE cn1 AS SELECT c.rid, max(t.o) mx FROM cand c JOIN ted t ON t.n = c.nif WHERE c.nif IS NOT NULL AND (t.e = c.exu OR (length(t.e) >= 6 AND (contains(c.obu, t.e) OR contains(c.exu, t.e)))) GROUP BY 1""") print("cruce con NIF", x("SELECT count(*), sum((mx>1)::INT) FROM cn1").fetchone(), round(time.time() - t0), flush=True) x(r"""CREATE TABLE cn2 AS WITH tok AS (SELECT DISTINCT rid, unnest(regexp_extract_all(obu || ' ' || exu, '[A-Z0-9][A-Z0-9/\-.]{6,}')) t FROM cand WHERE nif IS NULL), e AS (SELECT rid, t e FROM tok UNION SELECT rid, trim(t, '.-/') e FROM tok) SELECT e.rid, max(m.mx) mx FROM e JOIN tcod m USING (e) GROUP BY 1""") print("cruce sin NIF", x("SELECT count(*), sum((mx>1)::INT) FROM cn2").fetchone(), flush=True) x("CREATE TABLE cr AS SELECT * FROM cn1 UNION ALL SELECT * FROM cn2") x("""CREATE TABLE org_ko AS WITH q AS (SELECT c.oc, count(DISTINCT c.lid) casa, count(DISTINCT c.lid) FILTER (WHERE r.mx > 1) contra FROM cand c JOIN cr r USING (rid) GROUP BY 1) SELECT oc, contra, casa FROM q WHERE casa >= 10 AND contra >= 0.2 * casa""") print("órganos fuera", x("SELECT count(*) FROM org_ko").fetchone(), flush=True) x(f"""CREATE TABLE psc AS SELECT upper(trim(expediente)) e, trim(regexp_replace(regexp_replace(strip_accents(lower(coalesce(organo_nombre,''))), '[^\\x00-\\x7F]', '', 'g'), '[^a-z0-9]+', ' ', 'g')) o, max(num_ofertas) mx FROM read_parquet('{D}/source=CATALUNYA_PSCP/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE num_ofertas IS NOT NULL AND anio_ref BETWEEN 2023 AND 2025 GROUP BY 1, 2""") x("""CREATE TABLE f7 AS SELECT c.rid FROM cand c JOIN psc p ON p.e = c.exu AND p.o = trim(regexp_replace(regexp_replace(strip_accents(lower(coalesce(c.onom,''))), '[^\\x00-\\x7F]', '', 'g'), '[^a-z0-9]+', ' ', 'g')) WHERE c.feed = 'agregacion' AND p.mx > 1 AND c.rid NOT IN (SELECT rid FROM cr WHERE mx > 1)""") print("F6, F7", x("SELECT (SELECT count(*) FROM cr WHERE mx>1), (SELECT count(*) FROM f7)").fetchone(), flush=True) x("""CREATE TABLE q AS SELECT l.* FROM l WHERE l.ft IS NULL AND l.rid NOT IN (SELECT rid FROM cr WHERE mx > 1) AND l.rid NOT IN (SELECT rid FROM f7) AND coalesce(l.oc,'') NOT IN (SELECT oc FROM org_ko)""") # DOC_CN: por entradas, sin regex claves = {r[0] for r in x("SELECT DISTINCT lid FROM q").fetchall()} corta = {"/".join(k.split(":", 1)[1].split("/")[-2:]) for k in claves} arch = x(f"""SELECT DISTINCT archivo_origen, conjunto FROM read_parquet('{ATOM}') WHERE conjunto IN ('licitaciones','agregacion') AND array_to_string(string_split(id, '/')[-2:], '/') IN (SELECT unnest(?))""", [sorted(corta)]).fetchall() vis, cn = set(), set() for z, conj in arch: p = next((q for q in (f"/n/zips/{conj}/{z}", f"/n/zips/{conj}/_historico/{z}") if os.path.exists(q)), None) if not p: continue with zipfile.ZipFile(p) as zf: for n in zf.namelist(): if not n.endswith(".atom"): continue for ent in zf.read(n).split(b"", a) if a < 0 or b < 0: continue k = ent[a + 13:b].decode("utf-8", "replace"); k = "/".join(k.split("/")[-2:]) if k in corta: vis.add(k) if b">DOC_CN<" in ent: cn.add(k) print(z[:44], len(vis), len(cn), round(time.time() - t0), flush=True) x("CREATE TABLE sin_cn AS SELECT unnest(?) k", [sorted(vis - cn)]) print("sin DOC_CN entre las que quedan", x("SELECT count(*) FROM sin_cn").fetchone(), flush=True) x("""CREATE TABLE q8 AS SELECT * FROM q WHERE array_to_string(string_split(split_part(lid, ':', 2), '/')[-2:], '/') NOT IN (SELECT k FROM sin_cn)""") x(f"""CREATE TABLE varios AS SELECT licitacion_id lid FROM read_parquet('{D}/source=PLACSP/**/*.parquet', hive_partitioning=1, union_by_name=1) WHERE licitacion_id IN (SELECT DISTINCT lid FROM l) AND unidad='adjudicacion' AND vigente AND es_contrato GROUP BY 1 HAVING count(lote_id) = 0 AND count(DISTINCT coalesce(adjudicatario_nif, adjudicatario_nombre)) > 1""") x("""CREATE TABLE fin AS SELECT oc, count(*) n, sum(u::INT) k FROM (SELECT oc, lid, bool_and(unica) u FROM q8 WHERE lid NOT IN (SELECT lid FROM varios) AND NOT starts_with(coalesce(oc,''), 'N:') GROUP BY 1, 2) GROUP BY 1""") print("final", x("SELECT count(*), sum(n), sum(k), count(*) FILTER (WHERE n>=30), count(*) FILTER (WHERE n>=30 AND k>0) FROM fin").fetchone(), flush=True) pub = {r["organo_clave"]: (int(r["licitaciones"]), int(r["con_una_oferta"])) for r in csv.DictReader(gzip.open("/rec/v3_ou/salida/oferta_unica_filtrada.csv.gz", "rt", encoding="utf-8"))} aqui = {r[0]: (int(r[1]), int(r[2])) for r in x("SELECT oc, n, k FROM fin WHERE n >= 30").fetchall()} dif = [(k[:30], pub.get(k), aqui.get(k)) for k in set(pub) | set(aqui) if pub.get(k) != aqui.get(k)] print("DIFERENCIAS", len(dif), "de", len(set(pub) | set(aqui)), dif[:8], round(time.time() - t0), flush=True)